Solved how do I compare data in 2 sheets

April 24, 2019 at 11:46:28
Specs: Windows 7
Hi

I have a workbook of which has data in sheets 1 & 2 and im trying to compare the two to look for missing data.

Data will be in columns A-D in both sheets . What im trying to do is look in sheet 1 and try and match up with what in sheet 2.
Where its a match in sheets 1&2 I want to put in column E in sheet 1 as OK and if there is no match I want it put as MISSING in column E.

Issues I have are that although the data may all be there in sheets 1 & 2 they will not be in the same row on both sheets. I.E data may be in row 14 in sheet 1 and the same in row 99 on sheet 2 so I need it to look all the way down sheet 2 to verify.
Other issues are that in in column C on both sheets there may be times when the date is different. I.E in sheet 1 may be shown as 24/04/2019 and in sheet 2 may be 24 Apr 19. Is there anyway that I can overcome this.


Can this this be done via macro or some other way as I have over 1000 entries on both sheets.

Thanks in advance


See More: how do I compare data in 2 sheets

Report •

#1
April 24, 2019 at 14:27:51
Please clarify this statement:

Data will be in columns A-D in both sheets

Does the "search" have to deal with all 4 columns when looking for a match or does a match in column A mean that a complete match has been found?

Please keep in mind that we can't see your workbook from where we're sitting so the more details you can provide the better.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#2
April 24, 2019 at 23:08:39
Hi yes it does have to look in all 4 columns and be an exact match on both sheets

Report •

#3
April 25, 2019 at 06:49:40
✔ Best Answer
This suggestion uses what is commonly referred to as a "Helper Column". A Helper Column is a column that is used to "help" a formula perform a task on data that it can't (easily) work on directly.

Your data is in Columns A:D. I'll use Column E as the Helper Column, but you could use any column you want, other than A:D of course. You can even hide the Helper Column after you've created it. Both of the following formulas are based on using Column E.

On Sheet2, put this formula in E1 and drag it down as far as you need:

=CONCATENATE(A1,B1,C1,D1)
or
=A1&B1&C1&D1

They both do the same thing. The formula will create a text string contain all 4 values and will also convert the Date to a numerical number. (This assumes that all of your Dates are actually Dates as recognized by Excel and not Text strings that just look like dates.)

Sheet 2:

      A        B         C         D               E
1    Bill     Car       Blue    4/25/2019     BillCarBlue43580
2    Sue      House     Red     24-Apr-19     SuehouseRed43579

Back In Sheet1, enter this formula in Column E and drag it down:

=IF(ISNA(VLOOKUP(A1&B1&C1&D1,Sheet2!E:E,1,0)),"Missing","OK")

What this will do is Concatenate Sheet1!A1:D1, then use VLOOKUP to search for that string in Sheet2 Column E. If the string is not found, the VLOOKUP will return a #N/A error and the ISNA function will be TRUE. That will cause the IF to be TRUE and it will return MISSING. If VLOOKUP finds the string in Sheet2 Column E, the ISNA will be FALSE and the IF will return OK.

The formula is referencing the Helper Column, not the actual data that you are trying to analyze. Let me know how this works for you.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

Related Solutions

#4
April 25, 2019 at 11:56:55
Spot on many thanks

Report •

Ask Question