Free Microsoft Excel 2013 Quick Reference

Compare lists using VLOOKUP and highlight matching cells

What I am trying to do is to compare two lists and highlight the cells on one sheet if the values are missing on the other one but it is not doing what I needed it to do.


	VB:
	
 
Do While Not IsEmpty(Cells(srow, scol).value) 
     
    result = Application.VLookup(Cells(srow, scol).value, LUrange, 1, False) 
    If Not Err.Number = 0 Then Cells(srow, scol).Interior.Color = 65535 
    srow = srow + 1 
    Err.Clear 
     
Loop 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Post your answer or comment

comments powered by Disqus
Hi,

I have two lists. One is a list of 700 people who have been sent some training to complete. The second list is of 350 who have completed that training. I need to highlight the people who have completed the training in my list of the 700 people who were sent it.

Ideally I would match the surname and highlight the cell/row somehow if they have completed the training. Either with a word or a colour

Any ideas?

thanks,
Mike

I have two named ranges, PO1(sheet1 E2:I65536) and PO2 (sheet 2 A3:A65536). I want to compare these two ranges and have the matches highlighted in the PO1 range. Essentially, if any of the cells in the PO1 range are the same as a cell in PO2, highlight that cell in the PO1 range.

I prefer a macro that will do this, not conditional formatting

One more thing. I don't want blank cells highlighted.

Thanks,

JBMERREL

Hello All:

I can't seem to get the right code to deliver cells from either my Vlookup or Index/Match functions on matching dates & times.

The point of the code is to use a date/time from one list (thousands long), look for a match in another list (thousands long), and pull in data from the column next to it in the same row. I have used vlookup many times for looking up data before, but the problem is that if the Data table has multiple entries on the same date/time (this is certainly will happen for my data) the Vlookup and Index/Match functions return only the first entry, at least how I have it coded right now.

I attached a spreadsheet illustrating what I tried with Index/Match and Vlookup, and also have it reprinted in text below.

Any coding tips to bring in data from multiple rows with identical matching lookup criteria (like date & time) would be greatly appreciated!

Index/Match trial:
DATA TABLE
Timestamp_1 Key1 Motor1 Amount1
7/20/09 1:34:54 AM 105 1 730
7/20/09 1:35:04 AM 103 1 260
7/20/09 2:19:34 AM 105 1 255
7/20/09 2:19:34 AM 103 2 705
7/20/09 2:19:34 AM 105 1 -515
7/20/09 2:19:36 AM 105 1 -220
7/20/09 2:19:41 AM 103 1 -835

CHECK TABLE
Timestamp_2 Key1 (code is in attached spreadsheet & below)
7/20/09 1:35:04 AM is 103
7/20/09 1:35:05 AM #N/A
7/20/09 1:35:06 AM #N/A
7/20/09 1:35:07 AM #N/A
7/20/09 2:19:33 AM #N/A
7/20/09 2:19:34 AM is 105
7/20/09 2:19:34 AM is 105
7/20/09 2:19:34 AM is 105
7/20/09 2:19:41 AM is 103

Key1 code: =IF(INDEX($A$3:$D$9,MATCH(A13,$A$3:$A$9,0),2)=103,"is 103",IF(INDEX($A$3:$D$9,MATCH(A13,$A$3:$A$9,0),2)=105,"is 105","not 103 or 105"))

Vlookup trial:
DATA TABLE
Timestamp_1 Key1 Motor1 Amount1
7/20/09 1:34:54 AM 105 1 730
7/20/09 1:35:04 AM 103 1 260
7/20/09 2:19:34 AM 105 1 255
7/20/09 2:19:34 AM 103 2 705
7/20/09 2:19:34 AM 105 1 -515
7/20/09 2:19:36 AM 105 1 -220
7/20/09 2:19:41 AM 103 1 -835

CHECK TABLE
Timestamp_2 Key1 (code is in attached spreadsheet)
7/20/09 1:35:04 AM is 103
7/20/09 1:35:05 AM is 103
7/20/09 1:35:06 AM is 103
7/20/09 1:35:07 AM is 103
7/20/09 2:19:33 AM is 103
7/20/09 2:19:34 AM is 105
7/20/09 2:19:34 AM is 105
7/20/09 2:19:34 AM is 105
7/20/09 2:19:41 AM is 103

Key1 code: =IF(VLOOKUP(A13,$A$3:$D$9,2)=103,"is 103",IF(VLOOKUP(A13,$A$3:$D$9,2)=105,"is 105","not 103 or 105"))

Hello,
I am trying to create a VB program that will compare 2 excel files and highlight the differences. I do not know if there is a excel command that I can use instead that would be more simple. I looked in the forums, but did not see anything. Please redirect me if there is a relevant post. I do not have any experience in VB. I have coded in Java before but it has been a while so there might be some fundamental problems with the code. I am getting a error message 'COMException unhandled' "Member not found. (Exception from HRESULT: 0x80020003 (DISP_E_MEMBERNOTFOUND))". I dont really know enough about VB to know why the program is failing. Thank you very much.

Here is my code


	VB:
	
Module Module1 
 
 
Sub Main() 
    Dim objExcel 
    Dim objWorkbook1 
    Dim objWorkbook2 
    Dim objWorksheet1 As Object 
    Dim objWorksheet2 As Object 
     'instantiated objects
     
    objExcel = CreateObject("Excel.Application") 
    objExcel.Visible = True 
    objWorkbook1 = objExcel.Workbooks.Open("C:UsersarmstrDocumentsWorkiyrul12original.xls") 
    objWorkbook2 = objExcel.Workbooks.Open("C:UsersarmstrDocumentsWorkiyrul12.xls") 
     
    objWorksheet1 = objWorkbook1.Sheets.Item(1) 
     
    objWorksheet2 = objWorkbook2.Sheets.Item(1) 
     'defined objects
     
     'runs for loop to compare worksheets
    For Each cell In objWorksheet1.UsedRange 
        If Not cell.Value = objWorksheet2.Range(cell.Address).Value Then 
            cell.Interior.ColorIndex = 3 
             'Highlights in red color if any changes in cells
        Else 
            cell.Interior.ColorIndex = 0 
        End If 
    Next 
    objWorkbook1.Workbooks.Save() 
    objWorkbook1.Workbooks.Close() 
    objWorkbook2.Workbooks.Save() 
    objWorkbook2.Workbooks.Close() 
End Sub 
 
Private Sub objExcel() 
    Throw New NotImplementedException 
End Sub 
 
End Module 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Any help on finding out why conditional formatting using vlookup and text doesn't work for me is greatly appreciated ...

Cells A2:A20 of my spreadsheet contains a data validation dropdown list from a named range of text values, i.e., when the user clicks A2, a dropdown appears and the user can select "Bob", "Bill", "Jim", "Sally" and "Pete".

I wish to use conditional formatting and vlookup to format the cells A2:A20 grey if "Bob" or "Jim" are selected, but leave the cell uncolored if either of the other three names are chosen. (This example is very simplistic - my actual spreadsheet has hundreds of unique dropdown text values, and vlookup would seem to make a lot of sense because a subset of these dropdown values should be colored grey if the user selects any one of them). When I try to do this using vlookup and conditional formatting, conditional formatting does not work for me.

The puzzling thing is that when I use numbers instead of names (say the numbers 1 and 2 of a list 1 to 5), the vlookup formula in the conditional formatting box works fine, i.e, something like =VLOOKUP(A2,$B$1:$B$5,1,FALSE) has no problems coloring cells, where A2 is the cell with the data validation dropdown list, and Cells B1 to B5 contain the listing of numbers that drive the lookup.

Thanks for any insights you can offer.

Thought #1) Did you remember to use ctrl-shift-enter?
Thought #2) Try using vlookup and capture the 'not founds' with...
if(Isna(vlookup(...)),"No in other list","")

HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".

"sofiane b via OfficeKB.com" wrote:

> i have tried the cpearson methods and conditional formatting wth colors but i
> cant seem to have it working nothig happens..
>
> i need to compare column A that has 1008 rows with column B that has 1031
> rows and gives me the difference between the them or something in that area..
> ...
>
> can someone help? thank you alot
>
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...tions/200507/1
>

Hi,

I'm relatively new to Excel but have gotten pretty good over the past two weeks at using VLOOKUP and INDEX+MATCH to perform lookups. Here's something I can't figure out, though. I'll lay out an example of what I want to do. The formula in cells G1:G3 is what I'm tearing my hair out over.

________A________B_______C_______D_______F________G____

_______Date_____Name____Crime____Name___Date_____Date on
_______of_______of________________of______Crime____which most
_______Crime____Criminal___________Victim___Investi-__recent
_________________________________________gated___previous crime
_________________________________________________of same
_________________________________________________type,
_________________________________________________committed
_________________________________________________by same
_________________________________________________criminal,
_________________________________________________was
_________________________________________________investigated
1______04/10____Lee______Shot____Edwin____04/11______
2______06/02____Lee______Robbed__Harriet___06/04______
3______11/22____Lee______Shot____Jack_____11/23___04/11

The values in A1:F3 are all given. What I can't figure out is the formula for G1:G3.

Here's what the formula at G1:G3 is supposed to do - I'll use the example of G3.

G3 = The value at the intersection of Column F and some Row [x] which meets ALL of the following three criteria:
(i) The value at (the intersection of Row [x] and Column B) = The value at (the intersection of the row in which G3 is found and Column B)
(ii) The value at (the intersection of Row [x] and Column C) = The value at (the intersection of the row in which G3 is found and Column C)
(iii) The value at (the intersection of Column F and Row [x]) = The value which, among all the values present in the cells in Column F whose rows meet criteria (i) and (ii), is next-lowest after the value at (the intersection of the row in which G3 is found and Column F)

In this case, since G3 is in Row 3, and since B1=B3, and since C1=C3, and since the value at F1, among all the values present in the cells in Column F whose rows meet criteria (i) and (ii), is the next-lowest value after the value at F3, G3=F1.

But how do I code this in a formula? I will have tens of thousands of rows, dozens of which may satisfy criteria (i) and (ii) relative to G[x], but of course only one row will satisfy (iii) as well - that is, unless we're talking about cases where many rows satisfy criteria (i) and (ii) but none of them has a value in Column F, which cases will be encountered. In those cases the value returned at G[x] should be "" or 0.

Thanks! I will be genuinely grateful for assistance.

I'm trying to use Vlookup to report a cell, the problem is that the cell is one row down from the reference, example:

Name Number
John
7
Jane
5

So how do I get it to report the seven when I reference the John cell, if the number is one row down and one column over?

Hello,

I have a 3 Columns A, C and E.
In cell B1, I want to check if the values in A exist in C. If they do, i would like to return the value of E.

For example let's say Bob is in A3. I searched column C for Bob and found it in in C8. In B3, I would like to return the value of E8 (basically I want to keep the row value the same and return the value in E).

So far i have
=IF(VLOOKUP(column A,column B,1,0)= TRUE,1,return value in Column E)

I just dont know how to return the value in column E.

Thanks,
Excel_Fan

Okay, I have been struggling with this for a few hours, so any help would be appreciated:

I am trying to use VLookup and/or Advanced Filtering (if you could offer help with both so I can learn that would be excellent) to search through a sheet of data for a particular salesman's name (which occurs multiple times in the leftmost column) take the values of each individual sale(which is in the column directly to the right of the salesman), add them together, and print the final value in a cell or textbox on the worksheet.

The search would be activated with a button, that retrieves the name from a listbox. I already have it setup so you can select the name in the listbox, press the button, and it presents a messagebox with the name.

I apologize for any misnomers etc as I am very very new at this.

Thanks again for your time and help.

I am using Vlookup and want it to stay blank if no value is put in. I want it to return "Invalid Shipping Option" if some value is put in but not one of the choices.
Do I need some sort of other formula? Thanks for any help!!

=IFERROR(VLOOKUP(B15,shipping,2,FALSE),"Invalid Shipping Option")

please tell me how to use vlookup and hlookup

Dear All,

I am facing issue while using vlookup and at the same time adding the results of that.

First, I am using vlookup for total count for every individual and then adding them up by using Sum for every individual hours, it is taking hell lot of time. Kindly advice me for any formula which I can use once and my work get done. Uploading file for better understanding.
Like: A is having 4 hours of training in his account in jan and feDummy E-Learning.xlsxb

Simultaneously, I want to see the result once I select any employee, I can see their attended training.

Regards,
Kunal Kumar

Hello everyone, here's my problem:

-- I have STORE.xls, which lists products available at my store.

-- my purveyor provides me with UPDATE.xls, which contains updated prices etc.

-- items in both spreadsheets can be matched by their unique SKU numbers.

Problem is that my purveyor may discontinue a product and not tell me. Then I would need to remove it from my inventory.

What I'm trying to achieve:

-- compare column named SKU in STORE.xls to SKU in UPDATE.xls

-- IF an SKU cell in STORE.xls is not found in UPDATE.xls, THEN highlight that cell in red

Currently I have a half-baked solution using this formula

which I use to indicate OK on a column adjacent to SKU, but I would like a VBA solution with color.

Any ideas?

Hi,

I'm looking for a way to compare two lists (they're quite extensive) and
highlight the matches. Is there a formula for this?

thanx

S.

Hello guys,

As the thread title reads, I need to compare text strings between two
cells/columns, and highlight those that match between them.

Example:
--------------------------------
A1:
VW, GM, CHRYSLER, VOLVO, FORD

B1:
FORD, GM, MITSUBISHI, NISSAN
--------------------------------

The idea is that FORD and GM be highlighted in A1, since the
conditional formatting would find those text strings in B1. Note that
the strings in A1 and B1 will always be different, varying by row.

Thanks for reading and hope you can help me with this!

--
luxbelle

I have 2 columns of data that I am trying to compare. 1 column is a long
list cells (d2:d1950) the 2nd column (f2:f95) contains values that may or may
not be in column d.

I've read enough posts to find the formula for if there is a match
(=IF(COUNTIF($D$2:$D$1936,F2)>0,"match","")), but rather really what I am
looking to do is highlight the cell in column D when there is a match and if
there is no match I'd like to hightlight those in column f.

Any help is really really appreciated.

Also I may have several columns f, g, h all the same charecteristics. Would
there be a way to highlight the matches in Column D when they match up with
any cell in f, g, or h and highlight the cell in f, g or h when there is no
match to the range in column D?

Thanks so much
David

I have 2 columns of data that I am trying to compare. 1 column is a long
list cells (d2:d1950) the 2nd column (f2:f95) contains values that may or may
not be in column d.

I've read enough posts to find the formula for if there is a match
(=IF(COUNTIF($D$2:$D$1936,F2)>0,"match","")), but rather really what I am
looking to do is highlight the cell in column D when there is a match and if
there is no match I'd like to hightlight those in column f.

Any help is really really appreciated.

Also I may have several columns f, g, h all the same charecteristics. Would
there be a way to highlight the matches in Column D when they match up with
any cell in f, g, or h and highlight the cell in f, g or h when there is no
match to the range in column D?

Thanks so much
David

Hello Friends

Want to lookup vaue from a data base list, there are 2 unique id in the database 1. date and 2. account number. below is the screen shot of data base.

Acct.No Value Date 01234 12546 1-Apr-12 012345 6589 1-Apr-12 01254 74589 1-Apr-12 02589 35894 1-Apr-12 09854 12587 1-Apr-12 0978 48963 1-Apr-12 01234 12548 2-Apr-12 012345 6580 2-Apr-12 01254 4589 2-Apr-12 02589 3589 2-Apr-12 09854 12578 2-Apr-12 0978 38963 2-Apr-12 01234 22548 3-Apr-12 012345 65852 3-Apr-12 01254 45891 3-Apr-12 02589 58985 3-Apr-12 09854 22578 3-Apr-12 0978 48963 3-Apr-12
I get the raw data in the above manner, i have to arrange the data in the below manner for reporting. on a daily i have to copy paste, to get rid of this i tried using vlookup and match formula, however it worked only for the 1-april-12, formula did not worked when i copied and pasted for other dates. my formula is

=VLOOKUP(I15,$C$14:$E$19,MATCH($G$15,$D$14:$D$19,1))

Date Acct.no Value Acct.no Value Acct.no Value Acct.no Value Acct.no Value Acct.no Value 1-Apr-12 01234 12546 012345 6589 01254 74589 02589 35894 09854 12587 0978 35894 2-Apr-12 01234 #REF! 012345 #REF! 01254 02589 09854 0978 3-Apr-12 01234 012345 01254 02589 09854 0978 4-Apr-12 01234 012345 01254 02589 09854 0978 5-Apr-12 01234 012345 01254 02589 09854 0978 6-Apr-12 01234 012345 01254 02589 09854 0978
Have attached the excel sheet for reference. can anyone suggest correct formula or correct way to do this.

Hi,
Am a research student. basically i have to sort some range in col. A
in descending order (as col. C) and then in col. D, i would like to
have the corresponding row value present in Col. B
I tried to use VLOOKUP. but it gives the first matching value only.
A B C D
5000 10 7000.0 70
2000 20 5000.0 10
4000 30 4000.0 30
3000 40 3900.0 90
1500 50 3000.0 40
2000 60 2000.0 20
7000 70 2000.0 20
1000 80 2000.0 20
3900 90 1500.0 50
2000 100 1000.0 80
I want in cells D7 and D8 the value of 60 and 100 respectively instead
of 20 and 20.
Please help me with suitable formula in column D. excl file is attached for better clarity on the problem
It is urgently required for me. advance thanks for the help
- kalaiarasan

Hi, I am searching for the macro code which should compare two excel sheets (Sheet1 and sheet2) and highlight the unmatched data in Sheet2. Here, cell to cell comparision is not suitable because the data in the other sheet might exist but not in the same cell. Hence using vaule of one cell of sheet1, we have to search the sheet2 (in all the cells) and then if it doesnot exist, highlight that particular cell of sheet1 with some color.
Please help me. I am attaching the sheets to be compared and the sample data.
Sheet1 data will be available in sheet2 but sheet2 will have more data.

Thanks..

sheet2.xlssheet1.xls

Compare a list of amounts with a macro and highlight amounts
I need to create a macro to compare a list of amounts and highlight amounts that total a certain quantity.
See list of amounts below.
I need to see if I can extract any amounts from list that will equal totaling 14000.
If there is no way to come up with that amount of 14000 then the macro would come back with response "Can not be accomplish".

Also maybe when you first click the macro it will ask you what amount you are trying to total to. In this case it would be 14000.

Quantities
2038
2038
815
815
713
407
407
407
407
407
407
407
366
346
305
305
305
305
305
265
244
203
203
203
203
203
203
163
142
122
122
122
101
101
101
101
101
101
101
101
101
101
101
101
101
101
101
81
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
61
40
40
40
40
40
------------------------------------
I manual figured out which quantites would equal 14000.

See below.

Is there a macro that could have figured out which quantities if possibly would total 14000 and highlight just those quantities in col A Sheet1.

The macro would just highlight the quantities below.

2038
2038
815
815
713
407
407
407
407
407
407
407
366
346
305
305
305
305
305
265
244
203
203
203
203
163
142
101
101
101
101
61
61
61
61
61
40
40
40
40
------------------------------------
Thank you for your anticipated response.
Yaneckc@aol.com

Hi all I am new to here. I am having a problem right now and wish someone can help. I have a lot of different documents and need to be reviewed at a certain frequency (monthly, weekly, daily). Once I've reviewed, I will put in the latest reviewd date in column C.
Column A: Document number (1, 2, 3, 4......)
Column B: Frequency (Monthly, Weekly, Daily)
Column C: Last reviewed date

I am hoping everytime when I open the file, it will tells me which document hasn't been updated. For example, if "Document 1" need to be updated on a monthly frequency, and the lastest reviewed date was 11/1/2004 (today is 1/19/2005), then Highlight cell "A2" (Document 1) so I will notice the issue.

Please look at the attachment, if everything goes well, cell A2 and A4 should be highlighted since they haven't been updated based on their own frequency.

I am not sure can this be done without using macro, and i am not good at coding at all....any help will be appreciated!!!

I have a worksheet with cell A1 with a certain number and cell A2 with
another number in it. I would like to compare cells A1 vs A2.

If the value is less than A2, then the cell A3 should be highlighted in yellow
If the value is equal to A2, then highlight the cell A3 in green
If the value is higher than A2, highlight the cell A3 in red

Thanks in advance.


No luck finding an answer? You could always try Google.