Excel help - comparing data - HotUKDeals
Get the HUKD app free at Google Play

#### Search Error

An error occurred when searching, please try again!

# Excel help - comparing data

6y, 11m agoPosted 6 years, 11 months ago
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
6y, 11m agoPosted 6 years, 11 months ago
Options

(36)
1 Like #1
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?
#2
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
#3
Narfette
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:
#4
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
#6
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]).

=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.
#7
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
#8
=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)

#9
Babbabooey
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

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
#10
none of the above works im afraid. ive tried everything you could possibly find on google, which is why i asked on here. nevermind!
#11
Disco
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.
#12
Disco
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.
#13
Narfette
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?
#14
Narfette
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?
1 Like #15
Narfette
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?

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

BFN,

fp.
#16
Disco
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?
#17
greg_68
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
1 Like #18
sorry, I may have misread it wrong..

VLOOKUPS and IF in that case? :thumbsup:
#19
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.
1 Like #20
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.
#21
Fanpages & chester123 way should deffo work, perhaps your formatting is to blame for you not returning the correct values.
#22
thefilbertfox
=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?
#23
Disco
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.
#24
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.
#25
chester123
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...
#26
chester123
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:
1 Like #27
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.
#28
5757alan
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
#29
Narfette
comes back as #N/A in every field...

There is something wrong with your data. I am a MVP and that should work.
1 Like #30
Narfette
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
1 Like #31
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)
#32
cis_groupie
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.

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
1 Like #33
chester123
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.
#34
5757alan
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
#35
:thumbsup:
Narfette
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
#36
Narfette
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

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: