# Excel help - comparing data

Hoping someone on here knows their excel fairly well..

Although, i dont think this is that difficult, i just cannot get it to work.

Basically - i have 2 lists of numbers. I want to compare the numbers in column A with the numbers in column B - to see which are not there.

Ive tried a couple of Vlookups and some =IF sums.. but nothings working..

example ofnumbers are as below. theres about 100 in total.

2559321 2565804

2559323 2559339

2559334 2568418

2559342 2568801

2559344 2568795

Is there a formula i can use to do this? - ideally to show either the number that is not present in column C or a 'false' label...

Id rather not use Macros

thanks

Although, i dont think this is that difficult, i just cannot get it to work.

Basically - i have 2 lists of numbers. I want to compare the numbers in column A with the numbers in column B - to see which are not there.

Ive tried a couple of Vlookups and some =IF sums.. but nothings working..

example ofnumbers are as below. theres about 100 in total.

2559321 2565804

2559323 2559339

2559334 2568418

2559342 2568801

2559344 2568795

Is there a formula i can use to do this? - ideally to show either the number that is not present in column C or a 'false' label...

Id rather not use Macros

thanks

At least that's a reply and a relatively quick fix?

I presume that there are a lot of numbers that do match?

Original Poster

If i can just figure out the formula it would take minutes

I'll see if I can work something out for you...:thumbsup:

=IF(A1=B1,"TRUE")

It will say FALSE for any numbers that dont match up.

(this of course assumes that the two columns match up exactly.

ie

1 1

2 2

3 3

etc

Place this formula in Cell C1:

=IF(COUNTIF(B:B,A1),"There", "Not There")

Then copy cell C1 to your clipboard ([CTRL]+[C]), highlight all the cells in column C that match all the entries in column A, & paste ([CTRL]+[V]).

Hence, column C2 should read:

=IF(COUNTIF(B:B,A2),"There", "Not There")

C3:

=IF(COUNTIF(B:B,A3),"There", "Not There")

and so on...

BFN,

fp.

[EDIT]: "There" in column C means the number in the same row in Column A is somewhere in Column B. "Not There" means the number in the same row in Column A is not in Column B at all.

Original Poster

The lists dont match up exactly - thats why i need to search for the numbers that arent in there.

I really need a formula that will search for the value of one specific cell, in the whole B column - and let me know if its not in there

Can you insert a column B and try the above?

(The $C$100 would need to change to how long your data goes down to - if you know what I mean)

As above link ..support.microsoft.com/kb/…367

that works... you could also add [SIZE="4"]

,"")[/SIZE] to the end of TRUE" to give you a blank cell if it does not matchOriginal Poster

The op has already posted the columns do not match up, they need to check any cell in col B against any cell in col A.

Original Poster

but none of the numbers will be in correlating order... ie, A1 will never equal B1 - theyre all jumbled up. If they were in order, i wouldnt need the search to work.

dont know what you mean? when you copy the cell ref from C1 down to say, C435, then the A and B cells should still reference that particular row?

did you try the suggestion in post 6?

Including my suggestion?

Do your columns of numbers not start in row 1?

Can you make your MS-Excel workbook file available for download?

Without knowing the issue you have (rather than you just giving up) I cannot assist any further.

BFN,

fp.

how will that fromula compare A1 to B200?

Original Poster

tried that, came back as not there for all of them. but i know some should be there.. weird. its probably me doing something wrong

VLOOKUPS and IF in that case? :thumbsup:

In Cell C1 (assuming you haven't got any headers), type in the formula:-

=VLOOKUP(A1,B:B,1,FALSE)

Copy the formula down the column

The formula will return the number as shown in column A, if it finds it in column B. If it's not found in column B then it will return #N/A.

Just use the match function.

Presuming that your two rows of data are in A + B with the master list in A.

In cell C1 you would type - =MATCH(B1,A:A,0) and fill down.

which is the same as [COLOR="Black"][SIZE="5"]=IF(A1=B1,"TRUE","")[/SIZE][/COLOR]

surely?

The data is not in order though, that would only return true if they figures were next to each other. They need to check the entire dataset.

will return a number 1 if the number in cell A1 is in column B. It will return a 0 if it is absent. Just copy the formula down for all rows.

Original Poster

comes back as #N/A in every field...

will maybe vlookup functions then ;-) :oops::thumbsup:

Assuming that the data to be compared is in columns A & D, then in Column B use this code:

=ISNA(MATCH(A5,$D$5:$D$870,FALSE))

and in Column E use this code:

=ISNA(MATCH(D5,$A$5:$A$686,FALSE))

Explanations:

For the first code I was comparing column A to Column D, starting in row 5 for each of the columns & comparing through to row D870

For the second code I was comparing Column D to Column A, starting in row 5 for each of the columns & comparing through to row A686

I also jazzed up the results columns (B & E) with conditional formatting, creating a simple rule that said if FALSE then fill the cell with red.

Original Poster

puts a 0 for all of them, but i know some of them match

There is something wrong with your data. I am a MVP and that should work.

Original Poster

using - =ISNA(MATCH(A5,$D$5:$D$870,FALSE)) - they all come back FALSE

using =ISNA(MATCH(D5,$A$5:$A$686,FALSE)) - they all come back TRUE

Precisely.

With excel there is usually more than one way to get to the same result.

In this example I will always use a VLOOKUP, personal preference that's all.

Original Poster

ZOMG! that was it, as soon as i changed it, all my figures altered into the correct answers.

thank you! - i knew it would be some stupid little button somewhere

thanks to everyone for their help - really appreciated

:thumbsup:

That's probably because I forgot to say that the formulae need to be entered into the first row of each colum (i.e. A5 & D5), then expanded to all rows, otherwise you'll always be comparing A5 to D5-D870 & D5 to A5-A686, so in A6 you should have: =ISNA(MATCH(A6,$D$5:$D$870,FALSE)) and in A7 you should have: =ISNA(MATCH(A7,$D$5:$D$870,FALSE)) etc etc

Sorry!

But glad you got it working, whichever way:thumbsup: