R8VI
asked on
excel compare to columns
Hi,
I have 2 columns in excel they both data in it but i need to find the differences and row numbers because one colums has 200 more records, they are not ordered in any particualr way either
Please help
Thanks,
R8VI
I have 2 columns in excel they both data in it but i need to find the differences and row numbers because one colums has 200 more records, they are not ordered in any particualr way either
Please help
Thanks,
R8VI
Please send the sample i/p and o/p?
assuming that the 2 columns are column A and column B, easiest way would be to sort both columns (one at a time) and to type the following formula in cell C1 :
you can then multiply the formula by dragging it downwards and apply a filter to hide all empty cells in the C column
=IF(A1<>B1, "difference", "")
you can then multiply the formula by dragging it downwards and apply a filter to hide all empty cells in the C column
pay attention though that this approach will only give you the answers you need if entries in the rows are related to each other. When both columns are identical but one cell is missing, you will get a lot of differences !
if you could post an example sheet, we could come up with an answer suitable to your situation.
if you could post an example sheet, we could come up with an answer suitable to your situation.
ASKER
Hi,
Sorting is really not an option because there are about 3000 plus records and example data
1974635 3438418
3438418 3592898
3592898 7138356
7138356 7310161
7141481 7771217
7310161 1974635
on this data there is only 1 records that match both coloums I need to find all the differences
Thanks,
R8VI
Sorting is really not an option because there are about 3000 plus records and example data
1974635 3438418
3438418 3592898
3592898 7138356
7138356 7310161
7141481 7771217
7310161 1974635
on this data there is only 1 records that match both coloums I need to find all the differences
Thanks,
R8VI
Hello R8VI,
Take a look at the example workbook.
It uses the following formula
The range MyRange is the shorter list. The formula will return "No Match" is the item is not found in both lists. Otherwise it will return the row number of the first match in the second list.
Kyle
Q-27379213-RevA.xlsx
Take a look at the example workbook.
It uses the following formula
=IF(ISERROR(MATCH(D5,MyRange,0)),"No Match",ROW(OFFSET(MyRange,0,0,1,1))+MATCH(D5,MyRange,0)-1)
The range MyRange is the shorter list. The formula will return "No Match" is the item is not found in both lists. Otherwise it will return the row number of the first match in the second list.
Kyle
Q-27379213-RevA.xlsx
So for given example you would like to get all records on the left except for the 3438418 entry ?
then use kgerb's function and filter all entries to show only those containing "No Match"
ASKER
Hi kgerb's
I have tried your example but still i dont think its right this is how I have changed it to
=IF(ISERROR(MATCH(C2,B2:B3 908,0)),"N o Match",ROW(OFFSET(B2:B3908 ,0,0,1,1)) +MATCH(C2, B2:B3908,0 )-1)
but I am getting more than 1000 records that dont match
when the big list has 3908 records and small list has 3642 records so there should only be about 260 odd that dont match or not there
and need to identfiy which row or which ones they are
Thanks,
R8VI
I have tried your example but still i dont think its right this is how I have changed it to
=IF(ISERROR(MATCH(C2,B2:B3
but I am getting more than 1000 records that dont match
when the big list has 3908 records and small list has 3642 records so there should only be about 260 odd that dont match or not there
and need to identfiy which row or which ones they are
Thanks,
R8VI
ASKER
Hi,
I looked at the first record in C and it says no match
the record is 345444 I did a control find in B and its the 6 one down
Please help
Thanks,
R8VI
I looked at the first record in C and it says no match
the record is 345444 I did a control find in B and its the 6 one down
Please help
Thanks,
R8VI
I think the problem might be that you need to reference your short list absolutely. Change B2:B3908 to $B$2:$B$3908. If that doesn't work I'll need to see some data. It's hard to know why the match function is not working correctly without seeing the actual data. Can you paste the two columns of data into a new workbook and upload it?
Also, my formula returns the row of the first match found in the short list. Is that not what you want? I attached a new workbook with the data you provided.
Kyle
Q-27379213-RevB.xlsx
Also, my formula returns the row of the first match found in the short list. Is that not what you want? I attached a new workbook with the data you provided.
Kyle
Q-27379213-RevB.xlsx
ASKER
Hi Kyle,
I am not sure if this is what your solution does.
But what I want it that take everything from A and match To B and bring back row number or records that do not appear in B which is the smaller one
Thanks,
R8VI
I am not sure if this is what your solution does.
But what I want it that take everything from A and match To B and bring back row number or records that do not appear in B which is the smaller one
Thanks,
R8VI
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.