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 all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,217
Messages
5,413,130
Members
403,464
Latest member
TheRepairGuy

This Week's Hot Topics

Top