Listing and Counting Unique Row Combinations found across two columns

thumper300zx

New Member
Joined
Jan 10, 2011
Messages
10
I am trying to automate these results instead of using a more manual/procedural approach. In the screenshot provided, you have two columns Model and Color.

What I need is a list of UNIQUE results for column combinations from the entered rows. I don't want made up combinations, only those that exist together in a row (hopefully that makes sense -- see data to clarify). Examples of valid results would be:

Type 1 Green
Type 1 Yellow
....
Type 3 Blue

An 'invalid' result (for the given data) would be:
Type 3 Purple

I want to automate the results in Columns C and D. They should match my more manual results in Columns K and L.

Columns F, G, H, and were used to verify totals and as reference for column L formulas.

Thanks in advance for any help, and thanks to follow :)
 

Attachments

  • model_color.png
    model_color.png
    50.3 KB · Views: 12

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
ABCDEF
1Excel 365
2Type 1GreenType 1 Green3Type 1 Green
3Type 1GreenType 1 Yellow1Type 1 Yellow
4Type 1GreenType 1 Purple1Type 1 Purple
5Type 1YellowType 1 Red6Type 1 Red
6Type 1PurpleType 1 Blue1Type 1 Blue
7Type 1RedType 2 Green2Type 2 Green
8Type 1RedType 2 Blue1Type 2 Blue
9Type 1RedType 3 Green1Type 3 Green
10Type 1RedType 3 Red2Type 3 Red
11Type 1RedType 3 Blue2Type 3 Blue
12Type 1RedType 3 Yellow1Type 3 Yellow
13Type 1Blue
14Type 2Green
15Type 2Green
16Type 2Blue
17Type 3Green
18Type 3Red
19Type 3Red
20Type 3Blue
21Type 3Blue
22Type 3Yellow
Sheet1
Cell Formulas
RangeFormula
C2:C12C2=IFERROR(INDEX(A$2:A$22&" "&B$2:B$22,SMALL(IF(MATCH(A$2:A$22&B$2:B$22,A$2:A$22&B$2:B$22,)=ROW(A$2:A$22)-ROW(A$2)+1,ROW(A$2:A$22)-ROW(A$2)+1),ROWS(C$2:C2))),"")
D2:D12D2=SUMPRODUCT(--(A$2:A$22&" "&B$2:B$22=C2))
F2:F12F2=UNIQUE(A2:A22&" "&B2:B22)
Dynamic array formulas.
 
Solution

thumper300zx

New Member
Joined
Jan 10, 2011
Messages
10
I do have one quick follow up for anyone that has found this and is interested.

I combined FILTER function to ignore blanks (allows me to extend the range for future entries).

=UNIQUE(FILTER(A2:A22,A2:A22<>"")&" "&FILTER(B2:B22,B2:B22<>""))

This seems to work for this small data set, but in another larger set I have, it is populating duplicates beyond the unique set. Gotta figure that out.
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
Filtering columns A and B separately might cause problems.

How about:

ABCD
1
2Type 1GreenType 1 Green
3Type 1GreenType 1 Yellow
4Type 1GreenType 1 Purple
5Type 1YellowType 1 Red
6Type 1PurpleType 1 Blue
7Type 1RedType 2 Green
8Type 1RedType 2 Blue
9Type 1RedType 3 Green
10Type 1RedType 3 Red
11Type 1RedType 3 Blue
12Type 1RedType 3 Yellow
13Type 1Blue
14Type 2Green
15Type 2Green
16Type 2Blue
17Type 3Green
18Type 3Red
19Type 3Red
20Type 3Blue
21Type 3Blue
22Type 3Yellow
23
24Blah
25Blah
Sheet1
Cell Formulas
RangeFormula
C2:C12C2=UNIQUE(FILTER(A2:A100&" "&B2:B100,LEN(A2:A100)*LEN(B2:B100)))
Dynamic array formulas.

You can wrap this in a SORT() if you like.
 

thumper300zx

New Member
Joined
Jan 10, 2011
Messages
10
Good stuff. I also played with formulas to allow more criteria (additional columns) and working great. Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,128,140
Messages
5,628,937
Members
416,354
Latest member
JojoMaque

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
Top