Duplicates in one column with different values in another column

Question

I have a table that has a column with a range of values that are not unique, i want to find the values that are the same in column A then see what it returns in column B if all the values in column B are the same i want to return a true result, if there is a value in B that is not the same then i want to return a result that returns the odd one out from that group of values in column A.

example in the table below

so i have 3 values that are the same in column A. (in red)

but in column B the 3 values are not the same, so i want the result in the 3rd column to return the value in the second column that is the 'odd one out' that being Two, in this example.

The table has thousands of lines which could contain either one unique number in column A or multiple numbers the same in column A. Column B could contain multiple values of the same. Any ideas of a formula available to calculate this in either Excel or a Dax formula for Power Bi please

A	B	Result
105230253	One	Two
105230253	One	Two
105230253	Two	Two
105558385	One	One
105558385	One	One
105560145	Three	Three
105560146	Three	Three

oliverscheurich · Answer

NeilTJustice&nbsp;=INDEX(B:B,MATCH(MIN(IF(A:A=A1,D:D)),(D:D)*(A:A=A1),0))You can try this formula along with a helper column. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.=COUNTIFS(A:A,A1,B:B,B1)This is the formula for the helper column. The formula is in cell D1 and filled down.

patrick2788 · Answer

If you're using 365:ID = Column AData = Columns A and B&nbsp;=LET(
    CheckIDs, LAMBDA(row,
        LET(records, UNIQUE(FILTER(DATA, ID = row)), ROWS(records) = 1)
    ),
    BYROW(ID, CheckIDs)
)&nbsp;&nbsp;

detlef_lewin · Answer

NeilTJustice&nbsp;&nbsp;=LET(
a,UNIQUE(A$2:B$8,,TRUE),
b,CHOOSECOLS(a,1),
c,CHOOSECOLS(a,2),
d,FILTER(c,b=A2,B2),
d)&nbsp;

neiltownend1510 · Answer

OliverScheurich&nbsp;This works great until i reach the scenario below. Therefore i have put a little more context around what i am trying to achieve&nbsp;So the helper file is reporting correctly in terms of the ask below, but should it be returning 5 as there are five invoices Ids, but because there is no PO# against two of the lines, then the formula falls over somewhat. It needs a tweak to show that PO in the PO Y/N trumps No PO, if that makes sense.&nbsp;&nbsp;

oliverscheurich · Answer

neiltownend1510&nbsp;=COUNTIFS($A$1:$A$1000,A1,$C$1:$C$1000,C1)According to the screenshot i assume that this is the formula for the helper column.=INDEX($C$1:$C$1000,MATCH(MIN(IF(($A$1:$A$1000=A1)*($C$1:$C$1000&lt;&gt;"No PO"),$D$1:$D$1000)),($D$1:$D$1000)*($A$1:$A$1000=A1)*($C$1:$C$1000&lt;&gt;"No PO"),0))This formula returns the intended result in column F. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.&nbsp;

Forum Discussion

Duplicates in one column with different values in another column

10 Replies

Resources