• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Highlight duplicate items in different columns:

A

Anubhav Dhyani

New Member
I have two columns with exactly same items but in different positions, is there a way to highlight the two similar items and subsequently highlight all items in different colors which are same in same columns. I'm not able to upload my excel file but I'll give you an example here and what I'd like to do:
A B
1 5
2 4
3 1
4 3
5 2
6 6

I'd like to highlight all '1' in green, all, '4' in blue and so on for every number so that at first glance I know where they're placed against each column. now if the rows are few I can achieve this by selecting conditional formatting to "equal to' but when there are 50-60 rows its very tedious. Is there a way where we can highlight all duplicate items in two different columns using a conditional formatting?

p.s - The columns could contain Words or Numbers
 
I'm not able to upload the file probably my office servers are not letting me to upload, my request is simple, lets take this example:

Column A Column B

34786 34786
34787 32828
32835 34761
34761 32835
32818 32818
32819 32806
32806 32819
32839 32839
32828 34787

and this is the result I want:

Column A Column B

34786 34786
3478732828
3283534761
3476132835
3281832818
3281932806
3280632819
3283932839
32828 34787

this way I can determine that 34787 was second in sales in 'Column A' but was last in 'Column B'

I hope it helps.
 
Hi ,

Formatting duplicates is a matter of creating / defining a Conditional Formatting rule. If one rule is created , it can use only one colour to format duplicates.

If two rules are formulated , two colours can be used , and so on.

Thus , what you want cannot be done for the general case , where there may be a dozen or even more duplicate items.

Using VBA , one can cycle through a list of predefined colours , but even then , there may be multiple numbers which will be coloured using the same colour , depending on how many colours are used.

Narayan
 
You must log in or register to reply here.
Back
Top