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 LoopIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

- Compare two lists and highlight matching values
- Compare two named ranges and highlight matches
- Vlookup and Index/Match problem with multiple rows
- Comparing 2 Excel files and highlighting differences
- Conditional Formatting using VLookup and text
- Comparing lists-use ctrl-shift-enter?
- Multiple specific conditions on vlookup or index/match
- Using Vlookup and Offset?
- Using Vlookup (and perhaps MATCH)
- Using VLookup and/or Advanced Lookup to find multiple values and then sum
- I am using Vlookup and want it to stay blank if no input
- Please tell me how to use vlookup and hlookup in excel
- Extracting Data using Vlookup and using Sum on the result
- Compare 2 workbooks and highlight missing cells on original
- Compare lists and highlight matches
- Compare cells/columns and highlight matching text strings
- Comparing data in two columns and highlighting the data
- Comparing data in two columns and highlighting the data
- VLOOKUP and Match formula while doing copy and paste
- Look up value in list of duplicates and return matching value
- Macro to compare two excel sheets and highlighting the additional stuff of sheet2
- Compare a list of amounts with a macro and highlight amounts
- Comparing 2 dates and highlight a cell?
- Compare two Cells and highlight third cell

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 prefer a macro that will do this, not conditional formatting

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

Thanks,

JBMERREL

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"))

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 ModuleIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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 #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

>

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.

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?

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

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.

Do I need some sort of other formula? Thanks for any help!!

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

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

-- 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?

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.

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

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

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

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.

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

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

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

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!!!

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.