Listing rows based on unique column data.

Question

For simplicity sake,&nbsp;I have a 4 column sheet.Brand, Product, Salesman, Sales period.&nbsp;I want to list all row data for:Unique brands sold AND unique brand &amp; product sold AND (if multiple brand &amp; product sold) for the first period its sold in.&nbsp;So, from this:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;to this:BrandProductSalesmanperiod&nbsp;BrandProductSalesmanperiodAlpharopeJoe23&nbsp;AlpharopeJoe23AlphastringJoe12&nbsp;AlphastringJoe12AlpharopeBob25&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AlphacordNikki24&nbsp;AlphacordNikki24BravoshieldJoe11&nbsp;BravoshieldJoe11BravoknifeBrandon24&nbsp;BravoknifeBrandon24CharlievestBrandon33&nbsp;CharlievestBrandon33DeltavestSheila2&nbsp;DeltavestSheila2EchoeardrumRodney2&nbsp;EchoeardrumRodney2EchoeardrumJoe2&nbsp;EchoeardrumJoe2FoxtrottailBob4&nbsp;FoxtrottailBob4GolfballBob6&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;GolfballJack5&nbsp;Golf&nbsp;ballJack5

sergeibaklan · Answer

peiyezhu&nbsp;
first sold&nbsp;Golf ball Jack 5? not Bob 6?
First Jack 5 since 5 is less than 6

peiyezhu · Answer

ulahh&nbsp;Golf ball Bob 6&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;Golf ball Jack 5&nbsp;&nbsp;&nbsp;first sold&nbsp;Golf ball Jack? not Bob 6?

sergeibaklan · Answer

ulahh&nbsp;
As variant with Power Query

Please see in attached file

sergeibaklan · Answer

ulahh&nbsp;
With formula, assuming you are on Excel 365
=LET(
 uBrands,   UNIQUE(Sales[[Brand]:[Product]]),
 uBrand,    CHOOSECOLS(uBrands,1),
 uProduct,  CHOOSECOLS(uBrands,2),
 minPeriod, MINIFS(
       Sales[period],
       Sales[Brand], uBrand,
       Sales[Product], uProduct),
 flag, XMATCH(
       Sales[Brand] &amp; "|" &amp; Sales[Product] &amp; "|" &amp;  Sales[period],
       uBrand &amp; "|" &amp;  uProduct &amp; "|" &amp;  minPeriod ),
 VSTACK(
   Sales[#Headers],
   FILTER(Sales, IFNA(flag, 0) ) )
)

Forum Discussion

Listing rows based on unique column data.

Share

Resources

Brand	Product	Salesman	period	Brand	Product	Salesman	period
Alpha	rope	Joe	23	Alpha	rope	Joe	23
Alpha	string	Joe	12	Alpha	string	Joe	12
Alpha	rope	Bob	25
Alpha	cord	Nikki	24	Alpha	cord	Nikki	24
Bravo	shield	Joe	11	Bravo	shield	Joe	11
Bravo	knife	Brandon	24	Bravo	knife	Brandon	24
Charlie	vest	Brandon	33	Charlie	vest	Brandon	33
Delta	vest	Sheila	2	Delta	vest	Sheila	2
Echo	eardrum	Rodney	2	Echo	eardrum	Rodney	2
Echo	eardrum	Joe	2	Echo	eardrum	Joe	2
Foxtrot	tail	Bob	4	Foxtrot	tail	Bob	4
Golf	ball	Bob	6
Golf	ball	Jack	5	Golf	ball	Jack	5