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>
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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)))
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,037
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top