Formula for listing unique values

[siddharthnk]

Hi All,

I have two columns with data. Column A has names of sectors and column B has names of accounts within those sectors. The accounts will be repeated over several rows. What I need is a formula which will stack up the unique accounts per sector in different columns. It would be great if you could help me with a formula which would help me do this.

Sector</SPAN>	Accounts</SPAN>
Consumer Products</SPAN>	Companhia de Bebidas Das Americas</SPAN>
Automotive</SPAN>	Toyota</SPAN>
Automotive</SPAN>	Ford</SPAN>
Consumer Products</SPAN>	Clorox Corporation</SPAN>
Consumer Products</SPAN>	Companhia de Bebidas Das Americas</SPAN>
Automotive</SPAN>	Toyota</SPAN>
Consumer Products</SPAN>	Diageo plc</SPAN>
Consumer Products</SPAN>	Companhia de Bebidas Das Americas</SPAN>
Automotive</SPAN>	Ford</SPAN>
Pharmaceuticals</SPAN>	Cardinal Health</SPAN>
Consumer Products</SPAN>	Hansen Natural Corporation (HANS)</SPAN>
Consumer Products</SPAN>	Clorox Corporation</SPAN>
Consumer Products</SPAN>	Companhia de Bebidas Das Americas</SPAN>
Automotive</SPAN>	Toyota</SPAN>
Consumer Products</SPAN>	Kimberly-Clark Corporation</SPAN>
Consumer Products</SPAN>	Diageo plc</SPAN>
Electronics</SPAN>	Hewlett-Packard</SPAN>
Pharmaceuticals</SPAN>	Cardinal Health</SPAN>
Steel</SPAN>	ArcelorMittal</SPAN>
Chemicals</SPAN>	Sinochem</SPAN>
Personal care products</SPAN>	Johnson & Johnson</SPAN>

<TBODY>
</TBODY>

 

[Trevor G]

Use a Pivot Table for this.

 

[Weazel]

something like...

D1=SUM(IF(FREQUENCY(IF($A$2:$A$22<>"",IF($A$2:$A$22=D$2,MATCH($B$2:$B$22,$B$2:$B$22,0))),ROW($B$2:$B$22)-ROW($B$2)+1),1)) control shift enter

D2=IF(ROWS($D$2:D2)>D$1,"",INDEX($B$2:$B$22,SMALL(IF(FREQUENCY(IF($B$2:$B$22<>"",IF($A$2:$A$22=D$2,MATCH($B$2:$B$22,$B$2:$B$22,0))),ROW($A$2:$A$22)-ROW($A$2)+1),ROW($A$2:$A$22)-ROW($A$2)+1),ROWS(D$2:D2)))) Control shift enter

the formula in D1 will give you the unique count of accounts in the sector
the formula in D2 would give you the unique list of accounts in the sector

You would still need to create a unique list of Sectors which can be done with remove duplicates and paste special transpose unless you want to write another formula for that.

I'm not entirely sure how you would do it all in a pivot table though.

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	Sector	Accounts		2​	1​	5​	1​	1​	1​	1​
2​	Consumer Products	Companhia de Bebidas Das Americas		Automotive	Chemicals	Consumer Products	Electronics	Personal care products	Pharmaceuticals	Steel
3​	Automotive	Toyota		Toyota	Sinochem	Companhia de Bebidas Das Americas	Hewlett-Packard	Johnson & Johnson	Cardinal Health	ArcelorMittal
4​	Automotive	Ford		Ford		Clorox Corporation
5​	Consumer Products	Clorox Corporation				Diageo plc
6​	Consumer Products	Companhia de Bebidas Das Americas				Hansen Natural Corporation (HANS)
7​	Automotive	Toyota				Kimberly-Clark Corporation
8​	Consumer Products	Diageo plc
9​	Consumer Products	Companhia de Bebidas Das Americas
10​	Automotive	Ford
11​	Pharmaceuticals	Cardinal Health
12​	Consumer Products	Hansen Natural Corporation (HANS)
13​	Consumer Products	Clorox Corporation
14​	Consumer Products	Companhia de Bebidas Das Americas
15​	Automotive	Toyota
16​	Consumer Products	Kimberly-Clark Corporation
17​	Consumer Products	Diageo plc
18​	Electronics	Hewlett-Packard
19​	Pharmaceuticals	Cardinal Health
20​	Steel	ArcelorMittal
21​	Chemicals	Sinochem
22​	Personal care products	Johnson & Johnson

<tbody>
</tbody>