Unique value based on multiple column

vostroxe

New Member
Joined
Jul 13, 2018
Messages
29
Hi,

I am trying to find on how to find unique value based on two column. Column C must be repeated based on how much unique value available in column D

Current formula used in column C is

={IFNA(INDEX($A$1:$A$6,MATCH(0,COUNTIF($C$1:C1,$A$1:$A$6),0)),"")}

Current situation

ABCD
1Co CodeCountryFormula 1Formula 2
2PXABINPXABIN
3PXABCNPXBBCN
4PXBBCN
5PXBBIN
6PXBBIN
11
12
13

<tbody>
</tbody>

Intended Result


ABCD
1Co CodeCountryFormula 1Formula 2
2PXABINPXABIN
3PXABCNPXBBIN
4PXBBCNPXABCN
5PXBBINPXBBCN
6PXBBIN
11
12
13

<tbody>
</tbody>


OR

ABCD
1Co CodeCountryFormula 1Formula 2
2PXABINPXABIN
3PXABINPXBBIN
4PXBBCNPXBBCN
5PXBBIN
6PXBBIN
11PXABIN
12
13

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Sanjeev1976

Board Regular
Joined
Dec 25, 2008
Messages
247
Add a helper column say in "F" and use an array formula in F2 and then pulled down
=SUM(((MATCH(A2&B2,$A$2:$A$6&$B$2:$B$6,0))=COUNTA($A$2:A2)),C1)

Formula in C2 and pulled down
= IF(COUNTA($A$2:A2)>MAX($F$2:$F$6),"",INDEX($A$2:$A$6,MATCH(COUNTA($A$2:A2),$F$2:$F$6,0)))

Formula in D2 and pulled down
=IF(COUNTA($A$2:A2)>MAX($F$2:$F$6),"",INDEX($B$2:$B$6,MATCH(COUNTA($A$2:A2),$F$2:$F$6,0)))
 

vostroxe

New Member
Joined
Jul 13, 2018
Messages
29
Add a helper column say in "F" and use an array formula in F2 and then pulled down
=SUM(((MATCH(A2&B2,$A$2:$A$6&$B$2:$B$6,0))=COUNTA($A$2:A2)),C1)

Formula in C2 and pulled down
= IF(COUNTA($A$2:A2)>MAX($F$2:$F$6),"",INDEX($A$2:$A$6,MATCH(COUNTA($A$2:A2),$F$2:$F$6,0)))

Formula in D2 and pulled down
=IF(COUNTA($A$2:A2)>MAX($F$2:$F$6),"",INDEX($B$2:$B$6,MATCH(COUNTA($A$2:A2),$F$2:$F$6,0)))
Thank you for your reply. I have tried and it does not work. It seems to create circular reference in the array formula upon drag to the bottom.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,371
Try


A
B
C
D
1
Co Code​
Country​
Formula 1​
Formula 2​
2
PXAB​
IN​
PXAB​
IN​
3
PXAB​
CN​
PXAB​
CN​
4
PXBB​
CN​
PXBB​
CN​
5
PXBB​
IN​
PXBB​
IN​
6
PXBB​
IN​
7

Array Formula in C2 copied down
=IFERROR(INDEX(A$2:A$100,SMALL(IF(FREQUENCY(IF(A$2:A$100<>"",MATCH(A$2:A$100&"|"&B$2:B$100,A$2:A$100&"|"&B$2:B$100,0)),ROW(A$2:A$100)-ROW(A$2)+1),ROW(A$2:A$100)-ROW(A$2)+1),ROWS(C$2:C2))),"")
Ctrl+Shift+Enter

Array formula in D2 copied down
=IF(C2="","",INDEX(B$2:B$100,SMALL(IF(A$2:A$100=C2,ROW(B$2:B$100)-ROW(B$2)+1),COUNTIF(C$2:C2,C2))))
Ctrl+Shift+Enter

M.
 

Forum statistics

Threads
1,089,283
Messages
5,407,366
Members
403,137
Latest member
ExcelGzh

This Week's Hot Topics

Top