Convert Unique Formula to Row number

[pto160]

I have this unique formula that is very simple to use and it is widely seen on some forums and excel websites. It is =INDEX($C$2:$C$6,MATCH(0,COUNTIFS($E$1:$E1,$C$2:$C$6),0))
The index and match work together to look at the prior row to see if is already listed to generate the unique list. I would like to wrap this formula around another index to get the result from another column. I think I need to convert this formula to a row number. Here is the spreadsheet.

Book1
	A	B	C	D	E
1	Type	Brand	Combo		Unique List
2	Computer	A	Computer-A		Computer-A
3	Phone	B	Phone-B		Phone-B
4	Computer	A	Computer-A		Car-C
5	Car	C	Car-C		Computer-B
6	Computer	B	Computer-B
7
8	Result I want
9	Type	Brand
10	Computer	A
11	Phone	B
12	Car	C
13	Computer	B
Sheet2

Cell Formulas
Range	Formula
E2:E5	E2	=INDEX($C$2:$C$6,MATCH(0,COUNTIFS($E$1:$E1,$C$2:$C$6),0))
C2:C6	C2	=A2&"-"&B2
Press CTRL+SHIFT+ENTER to enter array formulas.

I tried
=INDEX($A$2:$A$6,INDEX($C$2:$C$6,MATCH(0,COUNTIFS($E$1:$E1,$C$2:$C$6),0)))

Is this possible with this type of formula?

 

[AhoyNC]

If your version of Excel 365 has the UNIQUE function then try:

Book1
	A	B	C	D	E
1	Type	Brand	Combo		Unique List
2	Computer	A	Computer-A		Computer-A
3	Phone	B	Phone-B		Phone-B
4	Computer	A	Computer-A		Car-C
5	Car	C	Car-C		Computer-B
6	Computer	B	Computer-B
7
8	Result I want
9	Type	Brand
10	Computer	A
11	Phone	B
12	Car	C
13	Computer	B
Sheet1

Cell Formulas
Range	Formula
E2	E2	=UNIQUE(C2:C6)
C2:C6	C2	=A2&"-"&B2
A10	A10	=UNIQUE(A2:B6)

 

[pto160]

Thanks so much. I have Excel 365 on my home computer. The unique formula works great.
I use Excel 2016 on my work computer. (hopefully getting upgraded soon to Office 365).
Is it possible to get the row number with the Countifs formula to wrap it in another index?

 

[AhoyNC]

See if this works for Excel 2016. You may have to enter it as an array with CTRL-SHIFT-ENTER.
Drag formula down column as needed.

Book1
	A	B	C	D	E
1	Type	Brand	Combo		Unique List
2	Computer	A	Computer-A		Computer-A
3	Phone	B	Phone-B		Phone-B
4	Computer	A	Computer-A		Car-C
5	Car	C	Car-C		Computer-B
6	Computer	B	Computer-B
7
8	Result I want
9	Type	Brand
10	Computer	A
11	Phone	B
12	Car	C
13	Computer	B
Sheet1

Cell Formulas
Range	Formula
E2	E2	=UNIQUE(C2:C6)
C2:C6	C2	=A2&"-"&B2
A10:A13	A10	=IFERROR(INDEX($A$2:$B$6,AGGREGATE(15,6,IF(FREQUENCY(MATCH($A$2:$A$6&"/"&$B$2:$B$6,$A$2:$A$6&"/"&$B$2:$B$6,0),ROW($A$2:$A$6)-ROW($A$2)+1),ROW($A$2:$A$6)-ROW($A$2)+1),ROWS($A$10:A10)),0),"")

 

[pto160]

Thanks so much for that formula. It works great.

Is the formula =INDEX($C$2:$C$6,MATCH(0,COUNTIFS($E$1:$E1,$C$2:$C$6),0)) not actually getting the row number like your Aggregate formula above?

 

[Peter_SSs]

For 2016 what about this array-entered version of your original formula, copied across and down, for use with 2016 (or 365)?

pto160_1.xlsm
	A	B	C
1	Type	Brand
2	Computer	A
3	Phone	B
4	Computer	A
5	Car	C
6	Computer	B
7
8	Result I want
9	Type	Brand
10	Computer	A
11	Phone	B
12	Car	C
13	Computer	B
14
15
Sheet1

Cell Formulas
Range	Formula
A10:B15	A10	=IFERROR(INDEX(A$2:A$6,MATCH(0,COUNTIFS($A$9:$A9,$A$2:$A$6,$B$9:$B9,$B$2:$B$6),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[pto160]

That's fantastic. No need for a helper column. It also answers the next question I was going to ask about having non-adjacent columns for Type and Brand. It also works with that.

 

[Peter_SSs]

You're welcome.