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

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that 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

F
  • Solved
Web search using Excel VBA error and script corrections
2
Replies
10
Views
237
Edgar_
Edgar_
S
  • Solved
Fine tuning of single cell formula
Replies
5
Views
224
Fluff
Fluff
R
  • Solved
Count Unique Values excluding blanks and one value
Replies
6
Views
156
Domenic
D
K
  • Solved
Index with Multiple Conditions
Replies
2
Views
110
kivikatz
K
C
  • Question Question
Only Brings Unique Value Across
Replies
3
Views
107
kvsrinivasamurthy
K

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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