Excel - Conditional Formatting or CountIf?
May 6, 2022 8:51 AM Subscribe
I have a large column of customer ID numbers (list A) to compare with another column of customer ID numbers (list B) in another worksheet of the same file. I can't seem to Google Fu an answer or find the right formula - please help.
List A is in one Worksheet, and List B is in another. Some of the list A numbers repeat, but the numbers in List B have no duplicates.
If the List A customer ID numbers are in List B, I want those List A customer ID numbers to format as Green to show they appear in List B. Or, I would like to return a "Yes" in the next column for easy comparison.
IF formula seems not to be working, Google is failing me, conditional formatting seems right but it isn't working. Is there something I'm missing with conditional formatting or IF formula, or is there another formula I could use?
List A is in one Worksheet, and List B is in another. Some of the list A numbers repeat, but the numbers in List B have no duplicates.
If the List A customer ID numbers are in List B, I want those List A customer ID numbers to format as Green to show they appear in List B. Or, I would like to return a "Yes" in the next column for easy comparison.
IF formula seems not to be working, Google is failing me, conditional formatting seems right but it isn't working. Is there something I'm missing with conditional formatting or IF formula, or is there another formula I could use?
Response by poster: Unfortunately, those aren't working.
The =COUNTIF formula is returning a zero for all numbers in Table A, when I know many of them are in Table B.
The =IF(COUNTIF...) formula is returning a "No" for all.
Is there a setting or a way of pasting the data that might help? Is it because I'm comparing tables between two different worksheets?
posted by glaucon at 9:14 AM on May 6, 2022
The =COUNTIF formula is returning a zero for all numbers in Table A, when I know many of them are in Table B.
The =IF(COUNTIF...) formula is returning a "No" for all.
Is there a setting or a way of pasting the data that might help? Is it because I'm comparing tables between two different worksheets?
posted by glaucon at 9:14 AM on May 6, 2022
Are the customer ID numbers entirely numeric or alphanumeric? I find Excel is sometimes finicky with this exact task due to finicky cell type issues, even when both are set to be Text or Numbers.
If your ID numbers are entirely numeric, and the customer ID number format permits it, then you could try multiplying both the List A numbers and List B numbers by 1 and compare the resulting columns instead and see if that works any better.
Comparing between two worksheets in the same workbook should not create a problem or act any differently than comparing across the same sheet.
posted by andrewesque at 9:26 AM on May 6, 2022 [2 favorites]
If your ID numbers are entirely numeric, and the customer ID number format permits it, then you could try multiplying both the List A numbers and List B numbers by 1 and compare the resulting columns instead and see if that works any better.
Comparing between two worksheets in the same workbook should not create a problem or act any differently than comparing across the same sheet.
posted by andrewesque at 9:26 AM on May 6, 2022 [2 favorites]
You could use the Fuzzy Lookup plugin to match the two sheets (you'd have to convert them both to tables). I use this all the time, it's so helpful for matching data between spreadsheets.
posted by phlox at 9:40 AM on May 6, 2022 [1 favorite]
posted by phlox at 9:40 AM on May 6, 2022 [1 favorite]
You could also use VLOOKUP to return a value from one sheet onto another, if there is a cell that matches in each sheet.
posted by nkknkk at 9:49 AM on May 6, 2022 [2 favorites]
posted by nkknkk at 9:49 AM on May 6, 2022 [2 favorites]
This will color the cell if it appears in the second list.
https://www.automateexcel.com/how-to/highlight-cell-if-value-exists-in-another-column/
posted by marplot at 9:54 AM on May 6, 2022
https://www.automateexcel.com/how-to/highlight-cell-if-value-exists-in-another-column/
posted by marplot at 9:54 AM on May 6, 2022
If the COUNTIF formula is not working that means you have some kind of data problem between the two sheets. To prove this, add a line to both sheets and type in "abc" in the customer id field for both and see that the formula does work. I often end up with leading or trailing spaces or an apostrophe that marks a cell as text. You want them both to show the customer id with no extra characters and have the same hyphenation, for example.
If COUNTIF is not working, vlookup and conditional formatting will not work either. The values in both cells must match exactly - and value is different than format in Excel. You can have two cells with different formatting but the same value (like 89.00 and 90-1) and Excel will see them as equal. So playing around with the formatting is not likely to solve this. You will need to do a find replace to remove spaces, for example, or use a TRIM formula. As you play around with this, it is best to work on a copy of your data so you don't compromise the original. In this case, you can copy both columns to a new sheet and add a new COUNTIF formula to it. Then work on fixing one of the columns until the formulas work.
posted by soelo at 10:12 AM on May 6, 2022 [4 favorites]
If COUNTIF is not working, vlookup and conditional formatting will not work either. The values in both cells must match exactly - and value is different than format in Excel. You can have two cells with different formatting but the same value (like 89.00 and 90-1) and Excel will see them as equal. So playing around with the formatting is not likely to solve this. You will need to do a find replace to remove spaces, for example, or use a TRIM formula. As you play around with this, it is best to work on a copy of your data so you don't compromise the original. In this case, you can copy both columns to a new sheet and add a new COUNTIF formula to it. Then work on fixing one of the columns until the formulas work.
posted by soelo at 10:12 AM on May 6, 2022 [4 favorites]
Seconding VLOOKUP recommendation.
posted by Grok Lobster at 10:43 AM on May 6, 2022 [1 favorite]
posted by Grok Lobster at 10:43 AM on May 6, 2022 [1 favorite]
I usually do an IFERROR(VLOOKUP) if I'm looking for something missing, with the Error message being "Not in (whatever)".
So =iferror(vlookup(etc. etc., FALSE),"Not in List A")
posted by littlemisslaika at 11:31 AM on May 6, 2022
So =iferror(vlookup(etc. etc., FALSE),"Not in List A")
posted by littlemisslaika at 11:31 AM on May 6, 2022
Along with what others have suggested, I have used MATCH as well as XLOOKUP (depending on your version of Excel) to do variations of what you're describing.
Based on your followup, is it possible your columns are formatted as Text instead of General?
posted by wondermouse at 11:53 AM on May 6, 2022
Based on your followup, is it possible your columns are formatted as Text instead of General?
posted by wondermouse at 11:53 AM on May 6, 2022
Response by poster: Finally got it. There was a space in List B that needed to be removed in order for the comparison to work. I ended up using Conditional Formatting, but the Columns still needed to be in the same worksheet and then I pasted the resulting color coded column over to the main worksheet I needed it to appear in.
Thank you all for your help and suggestions!
posted by glaucon at 12:18 PM on May 6, 2022
Thank you all for your help and suggestions!
posted by glaucon at 12:18 PM on May 6, 2022
« Older Home maintenance workbook / checklist / calendar... | Songs that feel like going to the dentist... Newer »
This thread is closed to new comments.
If you're looking for a "Yes", you can make it:
=IF(COUNTIF({range},{value})=1,"Yes","No")
posted by LSK at 8:56 AM on May 6, 2022 [2 favorites]