SOLVED

How to get the unique data from 1 column using the duplicates from another column

Occasional Contributor

Hi All,

I've been trying to highlight somehow the data in 1 row using the duplicates from another. 

The example is I have duplicate numbers in column B but in Column A I have 2 different names for the same duplicate number in Column B. 

I have attached the test excel where the number 123456 appears 4 times in Column B and in Column A I have 2 different names for that number - Alfa and Beta. Is there a way to highlight Alfa and Beta or to get a True or False to these results?

 

Thank you in advance!

7 Replies

@Steiny88 

That could be conditional formatting rule with formula

=COUNTIFS($B$2:$B$10,$B2,$A$2:$A$10,$A2) <> 1

@Sergei Baklan I don't think it works as I want to and now I see that I didn't explained it well.
Basically I need to know which names in column A appear with the same number in column B and to somehow extract them or highlight them.
Like for example the first 4 names - Alfa and Beta have the same number in column B but Alfa500 is only one name with the same number in column B.


I need to know only if there is more than one name with the same number in Column B.

Does that make sense?

Thank you!

@Steiny88 

It could be

=COUNTIFS($B$2:$B$10,$B2) <> COUNTIFS($A$2:$A$10,$A2)

which gives

image.png

1234567 also has more than one name.

@Sergei Baklan 
That works perfectly but I only pasted an example as I have 11k values below and didn't realize that the same name can appear with a different number (as in my file they are sorted with the numbers and not the names) where the formula should also check the other number if it has more than one name (didn't see that one coming) I've attached another example using the formula which you gave me and the results are True and should be False for A12:A15 and B12:B15 as the number 234 is appearing only for Alfa and 456 only for Beta. 

 

Thank you!

 

 

best response confirmed by Steiny88 (Occasional Contributor)
Solution

@Steiny88 

Perhaps

=COUNTIFS($B$2:$B$15,$B2, $A$2:$A$15, "<>" & $A2  )

@Sergei Baklan

Thank you so much! It works great. I suppose the numbers that it's giving me as result is how many times the criteria is met. I simply filtered without the zeros and I have the data I need to work with  

Thanks again!

 

 

 

@Steiny88 

This is larger and somewhat more rambling approach to the problem!

 

image.png

What I set our to do was, first get a list of distinct numbers.  From those return a list of distinct names corresponding to each number.  Return any for which there exists more than one match.  My assessment is that it is a computationally expensive process so I am not convinced it would extend to tens of thousands of rows.  The worksheet formula is

 

= LET(
      distinct, UNIQUE(Number),
      matches, MAP(distinct,  MultipleMatchesλ),
      FILTER(HSTACK(distinct, matches), matches<>"")
   )

 

with a Lambda function MultipleMatchesλ

 

= LAMBDA(num,
     LET(
         matchedNames, UNIQUE(FILTER(Name, Number=num)),
         multiple?, COUNTA(matchedNames)>1,
         IF(multiple?, TEXTJOIN(", ",,matchedNames),"")
     )
  )