# Unique value based on multiple column

#### vostroxe

##### New Member
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>

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### Sanjeev1976

##### Board Regular
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
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
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.

#### vostroxe

##### New Member
great man. it works. really appreciate the help.