Hi All
I am looking for a way to isolate values that appear across each and every column in a spreadsheet. The value shouldn't be highlighted unless it appears in each and every column. I am aware of how to identify duplicates using conditional formatting -- but I don't think that works for my purpose.
Any thoughts on how to accomplish this?
I have attached my spreadsheet for reference.
Thanks for your help in advance!
Are there any rows that have all the columns with the same data? I didn't see any.
Try this conditional formatting formula:
Apply it to:
Please Login or Register to view this content.
Please Login or Register to view this content.
If someone helped achieve your solution, consider clicking "* Add Reputation" on their post.
If your question has been answered, mark the thread as [SOLVED] using the Thread Tools menu at the top of the thread.
To clarify 'eBao-TWL-IN-3-V3.0' only appears in columns B-E and not A so it should not be highlighted.
But 'NonAH-LA_AR_B-22-OUT_v3.0' appears in columns A-E so it should be highlighted since it appears in EACH and EVERY column.
Hope this helps!
@Melvosh
The values may not be contained in the same row in each column -- I only need to know if it is present in each column, regardless of the row number.
deleted, asked and answered.
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Here's a somewhat clumsy conditional formatting formula to highlight values in column A that appears in columns B through E:
I'm not sure how a formula could be created that would accurately highlight those values in all columns. It may require several formulas. Does that at least do what you're looking for on column A?Please Login or Register to view this content.
so, what are you looking for, CF for column A if anything in A appears anywhere in B through G?
if so I used this CF =SUM(COUNTIF(A2,$B$2:$G$700))>1 and used bold italic red font with yellow fill, used format painter to highlight down col A to row 566.
EDIT: and this will only highlight col A if it is anywhere in B through G. Doesn't apply to B through G except for reference.
@Melvosh
This seems to meet the requirement. I am getting the return TRUE or #NA (I assume that means that specific value is not present in EACH column).
@Sambo kid -- I am looking for any values that appear across each and all of the columns, not just anywhere between B and G.
ok, not really sure what exactly you want to see, but I'm going with this.
what I gave you for CF for column A, then this for CF in col B =SUM(COUNTIF(B2,$A$2:$A$700),COUNTIF(B2,$C$2:$G$700))>1
this for CF in col C =SUM(COUNTIF(C2,$A$2:$B$700),COUNTIF(C2,$D$2:$G$700))>1
this for CF in col D =SUM(COUNTIF(D2,$A$2:$C$700),COUNTIF(D2,$E$2:$G$700))>1
this for the CF in col E =SUM(COUNTIF(E2,$A$2:$D$700),COUNTIF(E2,$F$2:$G$700))>1
you see the pattern, just keep making changes for each column.
so whatever you pick as the format for dups you will see those in each column that are in the other columns.
If that isn't what you are looking for, maybe create a tab with All the values in the source data tab then a lookup to return their frequency and which column or cell address they are in.
@Jnehra, correct. If you use the formula in conditional formatting, you can set it to highlight the column A values that meet the conditions.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks