Help needed on columns comparison... thanks!!

[shamnath]

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.

 

[sunnyland]

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
	A	B	C	D	E	F	G	H	I	J
1	Date	1	2	3	4	5	6	7	8	9
2	23/09/2006	2	0	0	0	0	0	0	0	0
3	27/09/2006	4	3	0	0	0	0	0	0	0
4	1/10/2006	5	0	0	0	0	0	0	0	0
5	6/10/2006	8	6	0	0	0	0	0	0	0
6	1/01/2005	0	0	0	0	0	0	0	0	0
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