Forum Discussion

Retiredtrialjudge's avatar
Retiredtrialjudge
Copper Contributor
Oct 18, 2022

Sorting with following items

There is probably an easy way to do this, but I can't find/figure it out.   I want to go shopping to the Grocers, Wal Mart, and the Pharmacy.   I have several items to get at each place.   So I e...
excel
Lorenzo's avatar
Lorenzo
Silver Contributor
Oct 18, 2022

Retiredtrialjudge 

There is probably an easy way to do this... Not really. Recording data as you do causes challenges when it comes to sorting, grouping, suming...

 

Attached is a Power Query option with your data in columns A-B https://www.bing.com/ck/a?!&&p=9829d1d9061623b1JmltdHM9MTY2NTk2NDgwMCZpZ3VpZD0yYzAzNDE2Yy0xMDEwLTY4NWUtM2RhYy01MzUxMTE2MTY5MWEmaW5zaWQ9NTE3Mg&ptn=3&hsh=3&fclid=2c03416c-1010-685e-3dac-53511161691a&psq=excel+format+as+table&u=a1aHR0cHM6Ly9zdXBwb3J0Lm1pY3Jvc29mdC5jb20vZW4tdXMvb2ZmaWNlL2Zvcm1hdC1hbi1leGNlbC10YWJsZS02Nzg5NjE5Zi1jODg5LTQ5NWMtOTljMi0yZjk3MWMwZTIzNzA&ntb=1 (Header Row isn't displayed). On sheet SORTED, the same as above, Sorted by [Shop]

 

When you add data to your Shopping list > Switch to SORTED > Right-click somewhere in Column A-B > Refresh

    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Oct 18, 2022

      Retiredtrialjudge 

       

      Another option if you run Excel 365. With your data in Table named ShoppingList (as previously shared):

       

      =LET(
  FillShop, SCAN(,ShoppingList[Shop],
    LAMBDA(seed,shop, IF(shop <> "", shop, seed))
  ),
  StackData, HSTACK(FillShop, IF(ShoppingList <> "", ShoppingList, "")),
  DROP(SORT(StackData, 1),,1)
)

       

      In attached file next to the Power Query option in sheet SORTED

Resources