Unique value based on multiple column

V

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
Co CodeCountry

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]Formula 1[/TD]
[TD="align: right"]Formula 2[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]PXAB[/TD]
[TD="align: right"]IN[/TD]
[TD="align: right"]PXAB[/TD]
[TD="align: right"]IN[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]PXAB[/TD]
[TD="align: right"]CN[/TD]
[TD="align: right"]PXBB[/TD]
[TD="align: right"]CN[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]PXBB[/TD]
[TD="align: right"]CN[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]PXBB[/TD]
[TD="align: right"]IN[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]PXBB[/TD]
[TD="align: right"]IN[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Intended Result


ABCD
Co CodeCountry

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]Formula 1[/TD]
[TD="align: right"]Formula 2[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]PXAB[/TD]
[TD="align: right"]IN[/TD]
[TD="align: right"]PXAB[/TD]
[TD="align: right"]IN[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]PXAB[/TD]
[TD="align: right"]CN[/TD]
[TD="align: right"]PXBB[/TD]
[TD="align: right"]IN[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]PXBB[/TD]
[TD="align: right"]CN[/TD]
[TD="align: right"]PXAB[/TD]
[TD="align: right"]CN[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]PXBB[/TD]
[TD="align: right"]IN[/TD]
[TD="align: right"]PXBB[/TD]
[TD="align: right"]CN[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]PXBB[/TD]
[TD="align: right"]IN[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>


OR

ABCD
Co CodeCountry

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"]Formula 1[/TD]
[TD="align: right"]Formula 2[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]PXAB[/TD]
[TD="align: right"]IN[/TD]
[TD="align: right"]PXAB[/TD]
[TD="align: right"]IN[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]PXAB[/TD]
[TD="align: right"]IN[/TD]
[TD="align: right"]PXBB[/TD]
[TD="align: right"]IN[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]PXBB[/TD]
[TD="align: right"]CN[/TD]
[TD="align: right"]PXBB[/TD]
[TD="align: right"]CN[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]PXBB[/TD]
[TD="align: right"]IN[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]PXBB[/TD]
[TD="align: right"]IN[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]PXAB[/TD]
[TD="align: right"]IN[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
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
Sanjeev1976 said:
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)))
Click to expand...

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


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
Co Code​
[/td][td]
Country​
[/td][td]
Formula 1​
[/td][td]
Formula 2​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
PXAB​
[/td][td]
IN​
[/td][td]
PXAB​
[/td][td]
IN​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
PXAB​
[/td][td]
CN​
[/td][td]
PXAB​
[/td][td]
CN​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
PXBB​
[/td][td]
CN​
[/td][td]
PXBB​
[/td][td]
CN​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
PXBB​
[/td][td]
IN​
[/td][td]
PXBB​
[/td][td]
IN​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
PXBB​
[/td][td]
IN​
[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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
You must log in or register to reply here.

Similar threads

S
  • Solved
Fine tuning of single cell formula
Replies
5
Views
221
Fluff
Fluff
M
  • Solved
Optimizing my rows to send e-mail to different address
Replies
2
Views
169
MasterBash
M
G
  • Solved
If Else Loop macro running slow, anyway to speed it up?
Replies
9
Views
291
Alex Blakenburg
A
BrettOlbrys1
  • Solved
Count Unique Values Based on Multiple Criteria
Replies
6
Views
463
BrettOlbrys1
BrettOlbrys1
S
  • Solved
Adding extra step to Index Match Formula
Replies
4
Views
192
Sundance_Kid
S

Forum statistics

Threads
1,223,895
Messages
6,175,257
Members
452,625
Latest member
saadat28

Share this page

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