Formula for listing unique values

siddharthnk

New Member
Joined
Jun 20, 2013
Messages
44
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>
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Weazel

Well-known Member
Joined
Dec 24, 2011
Messages
3,155
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​
SectorAccounts
2​
1​
5​
1​
1​
1​
1​
2​
Consumer ProductsCompanhia de Bebidas Das AmericasAutomotiveChemicalsConsumer ProductsElectronicsPersonal care productsPharmaceuticalsSteel
3​
AutomotiveToyotaToyotaSinochemCompanhia de Bebidas Das AmericasHewlett-PackardJohnson & JohnsonCardinal HealthArcelorMittal
4​
AutomotiveFordFordClorox Corporation
5​
Consumer ProductsClorox CorporationDiageo plc
6​
Consumer ProductsCompanhia de Bebidas Das AmericasHansen Natural Corporation (HANS)
7​
AutomotiveToyotaKimberly-Clark Corporation
8​
Consumer ProductsDiageo plc
9​
Consumer ProductsCompanhia de Bebidas Das Americas
10​
AutomotiveFord
11​
PharmaceuticalsCardinal Health
12​
Consumer ProductsHansen Natural Corporation (HANS)
13​
Consumer ProductsClorox Corporation
14​
Consumer ProductsCompanhia de Bebidas Das Americas
15​
AutomotiveToyota
16​
Consumer ProductsKimberly-Clark Corporation
17​
Consumer ProductsDiageo plc
18​
ElectronicsHewlett-Packard
19​
PharmaceuticalsCardinal Health
20​
SteelArcelorMittal
21​
ChemicalsSinochem
22​
Personal care productsJohnson & Johnson

<tbody>
</tbody>
 

Watch MrExcel Video

Forum statistics

Threads
1,099,386
Messages
5,468,298
Members
406,579
Latest member
lollypop1389

This Week's Hot Topics

Top