2 columns of email addresses. One us a full list (A) and the other is a list of people that have been contacted already (B). I want to find who hasn’t been contacted. Is it a Via Look up thingy?
=IF(COUNTIF($B:$B, $A2)=0, “No match in B”, “Match in B”)
this will go through the A column and then tell you if it’s in the B column.
Thanks, but that’s just giving me the #NAME? error.
Column A has 1900 emails
Column has 1200 emails
I want to find the 700 emails that are not in B
Simplest version I can think of is
=IF(ISNUMBER(MATCH(A2,B:B,0)),1,0)
Where A2 is one of the 1200 the email addresses, and B:B is the entire column of 1900
If it has a matcher you will get a 1.
Then filter by that column to remove all 1’s
that’s just a typo
try conditional formatting
=COUNTIF($B$1:$B$1201, $A2)=0
this will highlight all unique values in A
Substitute your rows and values etc
Appreciate that chief. Got that one working. Learned all this stuff years ago but it’s gone. Thanks
GRMA! 
1 Like
I’ve a bunch of cells in a column the following format, 1rxy -1234, b2rs5y-1264, 1dr5pt7y-7789 ect …
the last 4 are always figures and are cost codes. I need a column of the cost codes only.Whats the best way of doing this.
Sure, I’d be happy to help! To extract the cost codes from the cells in the format mentioned by AlanHQ, you can use the “Text to Columns” feature in Excel. Here’s how:
- Select the column that contains the data you want to split.
- Go to the “Data” tab on the ribbon and click on “Text to Columns”.
- In the “Convert Text to Columns Wizard”, choose “Delimited” and click “Next”.
- Check the box next to “Other” and type in “y” (without the quotes) as the delimiter. Make sure to deselect all other delimiter options.
- Click “Next” and choose a format for the cost codes (e.g. “Text” or “General”).
- Click “Finish” to split the data into separate columns.
- Delete any unwanted columns and you should be left with a column of cost codes.
I hope that helps! Let me know if you have any questions or if there’s anything else I can assist with.
2 Likes
Thank you.
2 Likes
You’re welcome! If you have any other questions or if there’s anything else I can help with, feel free to ask.
1 Like
=right(CellReference,4)
5 Likes
Would an experienced excel user pronounce it collum or colyume.?
1 Like
The former
1 Like
Surely simpler is highlight both columns and remove duplicates
You gave ceist a right poke in the eye there
Typical. You don’t want any audit trail.