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

36 Comments

How about sorting both columns lowest to highest then just looking? (You could also use the find command but you would need to key in each number separately)

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

i could, but once ive looked at these 100 or so, theres loads more to do.
If i can just figure out the formula it would take minutes

Narfette;6904993

i could, but once ive looked at these 100 or so, theres loads more to … i could, but once ive looked at these 100 or so, theres loads more to do.If i can just figure out the formula it would take minutes



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

Sort both columns as per above, then in colum C have code
=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

Assuming both lists of numbers start in row 1 & go down each respective column...

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

dont think that'll work.
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

=IF(ISERROR(MATCH(A1,$C$1:$C$100,0)),"",A1)

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

Babbabooey;6905027

Sort both columns as per above, then in colum C have … Sort both columns as per above, then in colum C have code=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 12 23 3etc



that works... you could also add [SIZE="4"],"")[/SIZE] to the end of TRUE" to give you a blank cell if it does not match

Original Poster

none of the above works im afraid. ive tried everything you could possibly find on google, which is why i asked on here. nevermind!

Disco;6905271

that works... you could also add [SIZE="4"],"")[/SIZE] to the end of … that works... you could also add [SIZE="4"],"")[/SIZE] to the end of TRUE" to give you a blank cell if it does not match



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

Disco;6905271

that works... you could also add [SIZE="4"],"")[/SIZE] to the end of … that works... you could also add [SIZE="4"],"")[/SIZE] to the end of TRUE" to give you a blank cell if it does not match



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.

Narfette;6905300

but none of the numbers will be in correlating order... ie, A1 will never … 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?

Narfette;6905300

but none of the numbers will be in correlating order... ie, A1 will never … 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.




did you try the suggestion in post 6?

Narfette;6905284

none of the above works im afraid. ive tried everything you could … none of the above works im afraid. ive tried everything you could possibly find on google, which is why i asked on here. nevermind!



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.

Disco;6905314

dont know what you mean? when you copy the cell ref from C1 down to say, … 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?




how will that fromula compare A1 to B200?

Original Poster

greg_68;6905315

did you try the suggestion in post 6?



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

sorry, I may have misread it wrong..

VLOOKUPS and IF in that case? :thumbsup:

Try this....

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.

Some very convoluted suggestions here.

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.

Fanpages & chester123 way should deffo work, perhaps your formatting is to blame for you not returning the correct values.

thefilbertfox;6905393

=VLOOKUP(A1,B:B,1,FALSE)Copy the formula down the column



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

surely?

Disco;6905438

which is the same as … 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.

A simple =COUNTIF(B:B,A1)

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

chester123;6905426

Some very convoluted suggestions here.Just use the match … Some very convoluted suggestions here.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.



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

chester123;6905453

The data is not in order though, that would only return true if they … 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 maybe vlookup functions then ;-) :oops::thumbsup:

I've done this before, here's how I did it

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

5757alan;6905465

A simple =COUNTIF(B:B,A1)will return a number 1 if the number in cell A1 … A simple =COUNTIF(B:B,A1)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.



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

Narfette;6905467

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



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

Narfette;6905509

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

Are you set to calculate automatically, or manually? Have a look in Tools>Options

I think you must be doing something wrong or it is formatted differently as there are at least 3 different answers that work on a normal set of data (I have tried them all)

Original Poster

cis_groupie;6905488

I've done this before, here's how I did itAssuming that the data to be … I've done this before, here's how I did itAssuming 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 D870For the second code I was comparing Column D to Column A, starting in row 5 for each of the columns & comparing through to row A686I 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.



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

chester123;6905453

The data is not in order though, that would only return true if they … 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.



Precisely.

Some very convoluted suggestions here



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

5757alan;6905526

Are you set to calculate automatically, or manually? Have a look in … Are you set to calculate automatically, or manually? Have a look in Tools>Options



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:

Narfette;6905581

ZOMG! that was it, as soon as i changed it, all my figures altered into … 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 somewherethanks to everyone for their help - really appreciated

Narfette;6905551

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



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:
Post a comment
Avatar
@
    Text
    Top Discussions
    1. Just heard this...2 ★★★★★★★★★★★★★★ congrats to all on 392k ★★★★★★★★★★★★★★7764010
    2. What do you feel like whinging about today?1917251
    3. Meerkat Movies (2 for 1 Cinema Codes) | Official Trading Thread415314087
    4. 75% off Sky TV for existing customers. Only works when you call the specifi…17386134

    See more discussions