Help needed on columns comparison... thanks!!

shamnath

New Member
Joined
Oct 5, 2006
Messages
21
I have two identically formatted spreadsheets with the same record labels on the first row. I need to find which entires from one s/s are missing from the other s/s. I current use this in a third new worksheet:

=IF(ISERROR(VLOOKUP(GMI!D2,PROD!$D$2:$D$133,1,FALSE)),"","Match")

where GMI and PROD are the two s/s I am comparing. The problem with this is that I am only using ONE record label to compare. How can I add additional labels in the comparison... like I was to also compre E2 (GMI) to $E$2:$E133 (PROD) and F2 (GMI) to $F$2:$F133 (PROD) before it can say "Match".

If this is too complicated because I have failed to explain it well, here is something else:!!

If I am comparing 2 columns (A and B)... and I have the above function to compare each row of A to the entire B. So for every record in A, I have a corresponding column that says "Match" if that record exists in B... how can I get excel to display WHERE in B it found the entry?

I hope someone can help me with this.... thanks soo much. Cheers.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

sunnyland

Well-known Member
Joined
Jan 27, 2006
Messages
912
Hello,
================
To compare the 2 columns
=================
You can use match that will return the position in the array that will work if you only have 1 instance of each date if not it will only return the first one found.
ie:
=MATCH(B5,Sheet1!A2:A6,0) will return 1 for example is the search value is in cell A2

to return the actual row number try:

=MATCH(B5,Sheet1!A2:A6,0)+(ROW(Sheet1!A2)-1)
............................
meaning you find the position in the array then add the row number of the first cell in the array -1 to get the number of offset form first row.
...........................

If you have more than one instance you can try this approach, it will work if you have a maximum of 255 occurences of one date.

1) select your columns of date and filter somewhere else using special filters and unique records only

2) this single list of date will be the base for the formula shown as sample below:
Book1
ABCDEFGHIJ
1Date123456789
223/09/2006200000000
327/09/2006430000000
41/10/2006500000000
56/10/2006860000000
61/01/2005000000000
Sheet1


the top row 1 , 2, 3 ,.....255 is the number of time a date as appeared.
The formula in B2 is the master formula and has to be entered as an array formula to return the correct result
meaning [if you don't know]
typing the formula as:
=LARGE(((mydates=Sheet1!$A2)*ROW(mydates)),B$1)

....mydates is just a named range.
I add source and formula on 2 sheets but you can have them on the same sheet :
=LARGE((($C$14:$C$18=Sheet1!$A2)*ROW($C$14:$C$18)),B$1)
.................................................
Think to put $ to your references array if not when you drag left and down later it will be incorrect.
.........................................
MOST IMPORTANT
Still in the formula bar press:
Ctrl+Shift+Enter
This will give the {} as in the sample from there you can drag to the left and then down without modification to the formula.

Have a go if this suits you
 

Forum statistics

Threads
1,136,309
Messages
5,674,996
Members
419,541
Latest member
freddyboots

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top