Oct 17 2022 09:20 PM
Oct 17 2022 09:20 PM
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 enter "Grocer" in A1, and put the items I need there in B2, B3, B4 and so on.
I enter "Wal Mart" in A5, and put the items I need there in B6, B7 B8.
I enter "Pharmacy in A9, and put the items I need there in B10, B11, andso on.
Now I want to sort my shopping list alphabetically, but I want the items from each store to "follow" the store in the store's place in the list. (In my real problem, I have 144 items for column A, and 10-12 items for each Column A entry in Column B) For reasons of printing, I have to list the items in Column B vertically, and NOT horizontally.
HOW CAN I DO THIS? Thanks!!!!
Oct 17 2022 11:24 PM
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 formated as Table (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
Oct 18 2022 06:46 AM
L z. is right about challenges when you store data that way, but I think there is still an easy solution...
The shop names are not in alphabetical order (and you presumably want them to remain in that order). If you are willing to prefix them with a number (1-Grocer, 2-Wal Mart, 3-Pharmacy) that helps a lot. Then, you just need a new column (which can be hidden, or at least not included in the print area) with a formula that combines cell values for sorting purposes (shown here for row 1 only):
=LOOKUP(2,1/(COUNTIF(A$1:A1,">"&A$1:A1&CHAR(7))=0),A$1:A1) & B1
Copy (drag) that formula down to the remaining data cells in that column.
So then you'll have...
|oh, lolli…||1-Groceroh, lolli…|
|2-Wal Mart||2-Wal Mart|
|lime powder||2-Wal Martlime powder|
|coconut oil||2-Wal Martcoconut oil|
|gravy boat||2-Wal Martgravy boat|
|Love Potion No. 9||3-PharmacyLove Potion No. 9|
[hoping this comes out OK; the preview function complains about an error in the HTML it generated]
Sort all the rows on (ascending) values in the new column, but only select columns A & B for printing.
Oct 18 2022 10:51 AM
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