Convert Unique Formula to Row number

pto160

Active Member
Joined
Feb 1, 2009
Messages
365
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
ABCDE
1TypeBrandComboUnique List
2ComputerAComputer-AComputer-A
3PhoneBPhone-BPhone-B
4ComputerAComputer-ACar-C
5CarCCar-CComputer-B
6ComputerBComputer-B
7
8Result I want
9TypeBrand
10ComputerA
11PhoneB
12CarC
13ComputerB
Sheet2
Cell Formulas
RangeFormula
E2:E5E2=INDEX($C$2:$C$6,MATCH(0,COUNTIFS($E$1:$E1,$C$2:$C$6),0))
C2:C6C2=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?
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
If your version of Excel 365 has the UNIQUE function then try:

Book1
ABCDE
1TypeBrandComboUnique List
2ComputerAComputer-AComputer-A
3PhoneBPhone-BPhone-B
4ComputerAComputer-ACar-C
5CarCCar-CComputer-B
6ComputerBComputer-B
7
8Result I want
9TypeBrand
10ComputerA
11PhoneB
12CarC
13ComputerB
Sheet1
Cell Formulas
RangeFormula
E2E2=UNIQUE(C2:C6)
C2:C6C2=A2&"-"&B2
A10A10=UNIQUE(A2:B6)
 

pto160

Active Member
Joined
Feb 1, 2009
Messages
365
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks so much. I have Excel 365 on my home computer. The unique formula works great. (y)
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

Well-known Member
Joined
Oct 10, 2011
Messages
4,719
Office Version
  1. 365
Platform
  1. Windows
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
ABCDE
1TypeBrandComboUnique List
2ComputerAComputer-AComputer-A
3PhoneBPhone-BPhone-B
4ComputerAComputer-ACar-C
5CarCCar-CComputer-B
6ComputerBComputer-B
7
8Result I want
9TypeBrand
10ComputerA
11PhoneB
12CarC
13ComputerB
Sheet1
Cell Formulas
RangeFormula
E2E2=UNIQUE(C2:C6)
C2:C6C2=A2&"-"&B2
A10:A13A10=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

Active Member
Joined
Feb 1, 2009
Messages
365
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
ABC
1TypeBrand
2ComputerA
3PhoneB
4ComputerA
5CarC
6ComputerB
7
8Result I want
9TypeBrand
10ComputerA
11PhoneB
12CarC
13ComputerB
14  
15  
Sheet1
Cell Formulas
RangeFormula
A10:B15A10=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

Active Member
Joined
Feb 1, 2009
Messages
365
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
That's fantastic. (y):) 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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
You're welcome. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,850
Messages
5,598,446
Members
414,240
Latest member
xnanx

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