Forum Discussion
Duplicates in one column with different values in another column
=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.
This works great until i reach the scenario below. Therefore i have put a little more context around what i am trying to achieve
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.
=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<>"No PO"),$D$1:$D$1000)),($D$1:$D$1000)*($A$1:$A$1000=A1)*($C$1:$C$1000<>"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.
OliverScheurich I dont get the expected result from the formula above. I just get PO for all lines, i want to see this in column F, its only when there are multiple InvoiceIds with differing Values in column (C) that PO takes precedence of all other values related to that InvoiceID number. Cheers
=IF(COUNTIF($A$1:$A$1000,A1)=COUNTIFS($A$1:$A$1000,A1,$C$1:$C$1000,C1),C1,"PO")Does this return the intended result?