Excel help - checking data against each other?

Question

I have very little Excel knowledge so I hope I can explain this in a way which makes some sense!

What I have is two lists of data. I have 10 columns each containing a number from 1-50, there are about 800 rows for these on the first sheet of data and 200 on the second list of data.

I want to use the second lot of data to check each row to the first lot of numbers and see if there are any duplications so I can remove them. The formula must use each row of the second sheet separately to try and find an exact match in the rows on the first sheet.

A simplified explanation would be appreciated as I’m not very excel savvy, Thanks in advance!

Answer 1

I don't have enought knowledge about excel to give you an exact answer to your question, but I do know that you have to use what's known as a V look up.

If you can find someone near you that understands that, then you'll have your answer.

Answer 2

Here's one way to do it:

1) Create a helper column on Sheet1.  This will be the lookup key.  It will combine all ten columns into one cell.  Say your first row is in A1:J1.  In an empty column (say, in column K), put:

=TEXT(A1, "00") &TEXT(B1, "00") &TEXT(C1, "00") &TEXT(D1, "00") &TEXT(E1, "00") &TEXT(F1, "00") &TEXT(G1, "00") &TEXT(H1, "00") &TEXT(I1, "00") &TEXT(J1, "00")

Copy and paste this formula down about 800 rows as required.  You should have a list consisting of 20-digit numbers.

2) Put the same kind of helper column on Sheet2.  Say in column L.

3) On the second sheet, use this formula to find out how many times each number (starting with L1 in this example) on Sheet2 are also on Sheet1.  Copy it down as far as it needs to go.

=COUNTIF(Sheet1!$K$1:$K$1000, L1)

Answer 3

http://www.mrexcel.com/tip096.shtml