Listing and Counting Unique Row Combinations found across two columns

[thumper300zx]

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

 

[StephenCrump]

	A	B	C	D	E	F
1						Excel 365
2	Type 1	Green	Type 1 Green	3		Type 1 Green
3	Type 1	Green	Type 1 Yellow	1		Type 1 Yellow
4	Type 1	Green	Type 1 Purple	1		Type 1 Purple
5	Type 1	Yellow	Type 1 Red	6		Type 1 Red
6	Type 1	Purple	Type 1 Blue	1		Type 1 Blue
7	Type 1	Red	Type 2 Green	2		Type 2 Green
8	Type 1	Red	Type 2 Blue	1		Type 2 Blue
9	Type 1	Red	Type 3 Green	1		Type 3 Green
10	Type 1	Red	Type 3 Red	2		Type 3 Red
11	Type 1	Red	Type 3 Blue	2		Type 3 Blue
12	Type 1	Red	Type 3 Yellow	1		Type 3 Yellow
13	Type 1	Blue
14	Type 2	Green
15	Type 2	Green
16	Type 2	Blue
17	Type 3	Green
18	Type 3	Red
19	Type 3	Red
20	Type 3	Blue
21	Type 3	Blue
22	Type 3	Yellow
Sheet1

Cell Formulas
Range	Formula
C2:C12	C2	=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:D12	D2	=SUMPRODUCT(--(A$2:A$22&" "&B$2:B$22=C2))
F2:F12	F2	=UNIQUE(A2:A22&" "&B2:B22)
Dynamic array formulas.

 

[thumper300zx]

That looks perfect! I will study these functions and use them heavily. I tried to find an answer to this for hours and nothing provided a real answer. This is precise and efficient! Thanks so much.

 

[thumper300zx]

<POST/>

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]

Filtering columns A and B separately might cause problems.

How about:

	A	B	C	D
1
2	Type 1	Green	Type 1 Green
3	Type 1	Green	Type 1 Yellow
4	Type 1	Green	Type 1 Purple
5	Type 1	Yellow	Type 1 Red
6	Type 1	Purple	Type 1 Blue
7	Type 1	Red	Type 2 Green
8	Type 1	Red	Type 2 Blue
9	Type 1	Red	Type 3 Green
10	Type 1	Red	Type 3 Red
11	Type 1	Red	Type 3 Blue
12	Type 1	Red	Type 3 Yellow
13	Type 1	Blue
14	Type 2	Green
15	Type 2	Green
16	Type 2	Blue
17	Type 3	Green
18	Type 3	Red
19	Type 3	Red
20	Type 3	Blue
21	Type 3	Blue
22	Type 3	Yellow
23
24	Blah
25		Blah
Sheet1

Cell Formulas
Range	Formula
C2:C12	C2	=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]

Good stuff. I also played with formulas to allow more criteria (additional columns) and working great. Thanks!