Compare 2 columns and output in column C

[hiranuk]

Hi

I am trying to compare column A to B and then output the match in column C. So i want to check that everything that is in column B is in column A. The problem is that the data is not all aligned up between column A and B

I want to match only the numbers not characters.

thanks for any help

here is column A and B

emisnet-12923 12923
emisnet-12926 12926
emisnet-12946 12964
emisnet-12964 13047
emisnet-12992 13130
emisnet-13037 13142
emisnet-13047 13193
emisnet-13065 13323
emisnet-13121 14350
emisnet-13130 14573
emisnet-13136 14573
emisnet-13142 14807
emisnet-13154 14825
emisnet-13163 14851
emisnet-13184 14975
emisnet-13193 15041
emisnet-13275 15221
emisnet-13307 15231
emisnet-13319 15328
emisnet-13323 15470
emisnet-13331 15533
emisnet-13357 15715
emisnet-13366 15815
emisnet-13383 15854
emisnet-13472 15855

[etaf]

does this work
=IF(COUNTIF(A:A,"*"&B2)>0,B2,"")

If not, then please attach a sample with expected results shown

[Pepe Le Mokko]

Administrative note

Perhaps take a minute to acknowledge the help you have received in earlier thread(s) before continuing here?

[hiranuk]

Thanks yes your attachment is what i need. but there will be entries in colum B that will not exist in column A so does that mean in column C for that entry the box will be left blank?

[hiranuk]

Actually is not working quite correctly. if you look at the spreadsheet attached. the entry 52982 exists in column B bot not in A but yet in column C it is there. I would
want it so that in column C it indicates that 52982 is not in column A, it an be either highlighted in color or something like "FALSE". Thanks

see attachment

[etaf]

so does that mean in column C for that entry the box will be left blank?

yes it does

[Pepe Le Mokko]

Have you read post #3??

[hiranuk]

Actually is not working quite correctly. if you look at the spreadsheet attached. the entry 52982 exists in column B bot not in A but yet in column C it is there. I would
want it so that in column C it indicates that 52982 is not in column A, it an be either highlighted in color or something like "FALSE". Thanks

see attachment

[hiranuk]

yes it does

Actually is not working quite correctly. if you look at the spreadsheet attached. the entry 52982 exists in column B bot not in A but yet in column C it is there. I would
want it so that in column C it indicates that 52982 is not in column A, it an be either highlighted in color or something like "FALSE". Thanks

see attachment

[etaf]

it does work your formula is incorrect

you have
=IF(COUNTIF(A:A,"*"&B2)>0,B1,"")
so it looking at the row below the one tested
change to
=IF(COUNTIF(A:A,"*"&B1)>0,B1,"")

you can use
=COUNTIF(A:A,"*"&B1)>0
in a conditional format formula to colour the cell

OR change
=IF(COUNTIF(A:A,"*"&B1)>0,B1,"")

to
=IF(COUNTIF(A:A,"*"&B1)>0,B1,FALSE)

so instead of a blank you get the FALSE in the cell

[etaf]

@Pepe Le Mokko
yes, did you want all answers to now stop , until you allow ? not sure what or who the reference was for

Perhaps take a minute to acknowledge the help you have received in earlier thread(s) before continuing here?

I have search and seen another duplicate here
https://www.excelforum.com/excel-gen...ml#post5109125

[hiranuk]

it does work your formula is incorrect

you have
=IF(COUNTIF(A:A,"*"&B2)>0,B1,"")
so it looking at the row below the one tested
change to
=IF(COUNTIF(A:A,"*"&B1)>0,B1,"")

you can use
=COUNTIF(A:A,"*"&B1)>0
in a conditional format formula to colour the cell

OR change
=IF(COUNTIF(A:A,"*"&B1)>0,B1,"")

to
=IF(COUNTIF(A:A,"*"&B1)>0,B1,FALSE)

so instead of a blank you get the FALSE in the cell

Thanks but if you look at 18758 this exists in column A and B but it is blank in C, it should appear in C well. i want to make sure all that appear in column B also appear in column A, that ones that dont should be highlighted in column C. so i dont think the formula is correct

[etaf]

have you changed the formula

[hiranuk]

thanks i tried it again its working

[Pepe Le Mokko]

@Pepe Le Mokko
yes, did you want all answers to now stop , until you allow ?

Hi etaf,
no problems, there is no rule about lack of courtesy. I am just trying to make the OP understand, but he does not seem to care.
I'll close the post you mentioned.
Thanls