Forum Discussion

Copper Contributor

Jan 08, 2024

Listing rows based on unique column data.

For simplicity sake, I have a 4 column sheet. Brand, Product, Salesman, Sales period. I want to list all row data for: Unique brands sold AND unique brand & product sold AND (if multiple br...

excel

SergeiBaklan

Diamond Contributor

Aug 01, 2024

ulahh

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] & "|" & Sales[Product] & "|" &  Sales[period],
       uBrand & "|" &  uProduct & "|" &  minPeriod ),
 VSTACK(
   Sales[#Headers],
   FILTER(Sales, IFNA(flag, 0) ) )
)

Listing rows based on unique column data.xlsx16 KB