Split data out over columns, under unique headings

J

JSSR

New Member
Joined
Aug 23, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello Team

I'm having trouble working out the best approach to my problem. I have a raw data CSV output from our online ordering software, but it bunches all the customer order information onto one field. It's possible to use text to column to split out the items, but there are two problems to get the data automatically into the format in the attached "results" image.

1) The orders don't sit under a relevant heading
2) Orders also have a multiple next to them for how many of each item they have ordered - again this can be split out separately using text to columns, splitting on the 2x. Alternatively I thought about an approach where I am able to move the item with the multiplier under the correct column, then use the find and replace function to remove the item name leaving only the number,

You'll not in the results picture, some items are aggregated under one column, like wine and the cake. This isn't really required, it would be OK if these formed under their own single column each like the other items.

Warm regards
James

RunSheet 26 Aug 2021 (generated 8.23.21 6_44 PM).xlsx
ABCDEFGHIJKLMNOPQRSTUVWX
1DateService or EventTimeNumber of PeopleBooked AtCustomerCompanyTable(s)SectionStatusPhoneEmailTagsBooking OptionsPayment AmountPayment TypeTransaction IdBooking referenceStaff NameBooking MethodMultiple VenuesNotesCustomer NotesLinks
2#########Dine at Home3:00 PM1#########Customer 11Dine at HomeConfirmedRoast Spatchcock (2 ppl) $95 x 2, Shepherd's Pie (2 ppl) $95 x 137539657online
3#########Dine at Home3:00 PM1#########Customer 21Dine at HomeConfirmedRoast Spatchcock (2 ppl) $95 x 1, Nick Spencer Grüner $38 x 1, Creamy Tiramisu Cake (Single) $15 x 1, Strawberry Cheesecake (Single) $15 x 125768036online
4#########Dine at Home3:00 PM1#########Customer 31Dine at HomeConfirmedRoast Spatchcock (2 ppl) $95 x 1, Strawberry Cheesecake (Single) $15 x 219223131online
5#########Dine at Home3:00 PM1#########Customer 41Dine at HomeConfirmedRoast Spatchcock (2 ppl) $95 x 1, Blackberry Chocolate Cake (Single) $15 x 1, Creamy Tiramisu Cake (Single) $15 x 125902088online
6#########Dine at Home3:30 PM1#########Customer 51Dine at HomeConfirmedRoast Spatchcock (2 ppl) $95 x 1, Shepherd's Pie (2 ppl) $95 x 1, Creamy Tiramisu Cake (Single) $15 x 2, Strawberry Cheesecake (Single) $15 x 229015825online
7#########Dine at Home4:30 PM1#########Customer 61Dine at HomeConfirmedRoast Spatchcock (2 ppl) $95 x 1, Creamy Tiramisu Cake (Single) $15 x 134781626online
8#########Dine at Home4:30 PM1#########Customer 71Dine at HomeConfirmedJerusalem artichoke agnolotti (Vego) (2 ppl) $95 x 120541130online
9#########Dine at Home4:30 PM1#########Customer 81Dine at HomeConfirmedRoast Spatchcock (2 ppl) $95 x 1, Creamy Tiramisu Cake (Single) $15 x 136682398online
10#########Dine at Home4:45 PM1#########Customer 91Dine at HomeConfirmedShepherd's Pie (2 ppl) $95 x 136535767online
11#########Dine at Home4:45 PM1#########Customer 101Dine at HomeConfirmedShepherd's Pie (2 ppl) $95 x 1, Creamy Tiramisu Cake (Single) $15 x 345983355onlineThank you
12#########Dine at Home4:45 PM1#########Customer 111Dine at HomeConfirmedRoast Spatchcock (2 ppl) $95 x 1, Blackberry Chocolate Cake (Single) $15 x 162363029onlineChicken only, no other meat. Ordering for my wife's birthday. Subject to pick up available during lockdown.
13#########Dine at Home4:45 PM1#########Customer 121Dine at HomeConfirmedJerusalem artichoke agnolotti (Vego) (2 ppl) $95 x 1, Blackberry Chocolate Cake (Single) $15 x 1, Strawberry Cheesecake (Single) $15 x 122093976online
14#########Dine at Home4:45 PM1#########Customer 131Dine at HomeConfirmedRoast Spatchcock (2 ppl) $95 x 1, 1/2 Doz Freshly Shucked Appellation Oysters $25 x 2, 2018 Mount Majura Shiraz $38 x 1, Creamy Tiramisu Cake (Single) $15 x 237788094online
15#########Dine at Home5:00 PM1#########Customer 141Dine at HomeConfirmedRoast Spatchcock (2 ppl) $95 x 1, Blackberry Chocolate Cake (Single) $15 x 1, Creamy Tiramisu Cake (Single) $15 x 172236640online
16#########Dine at Home5:00 PM1#########Customer 151Dine at HomeConfirmedRoast Spatchcock (2 ppl) $95 x 11588227onlineMarried here 26th August 2018
17#########Dine at Home5:00 PM1#########Customer 161Dine at HomeConfirmedRoast Spatchcock (2 ppl) $95 x 151224778online
18#########Dine at Home5:00 PM1#########Customer 171Dine at HomeConfirmedJerusalem artichoke agnolotti (Vego) (2 ppl) $95 x 1, 2018 Mount Majura Shiraz $38 x 1, Creamy Tiramisu Cake (Single) $15 x 157443158online
19#########Dine at Home5:00 PM1#########Customer 181Dine at HomeConfirmedRoast Spatchcock (2 ppl) $95 x 1, Creamy Tiramisu Cake (Single) $15 x 119441989onlineFamily friend of Jamie's
RunSheet 26 Aug 2021 (generated
 

Attachments

  • Result.png
    Result.png
    103.5 KB · Views: 24

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
Hi James,

You could use Power Query to convert your CSV file into a "converting sheet" for your Result, I assume you download these daily for your runsheet. This allows you to save over the runsheet file that power query looks at to update your data.

The Convert sheet has the query tab for converting your CSV, then the Result tab has the format I think your looking for. This way power query can transform your data for you and your convert sheet will call the new data when refreshed.



PowerQuery Output

Split data out over columns, under unique headings-source.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXY
1DateService or EventTimeNumber of PeopleBooked AtCustomerCompanyTable(s)SectionStatusPhoneEmailTagsBooking Options.1.1Booking Options.1.2Booking Options.2.1Booking Options.2.2Booking Options.3.1Booking Options.3.2Booking Options.4.1Booking Options.4.2Payment AmountPayment TypeTransaction IdBooking reference
226/08/2021 0:00Dine at Home3:00:00 PM123/08/2021 15:52:00Customer 11Dine at HomeConfirmedRoast Spatchcock (2 ppl) $952Shepherd's Pie (2 ppl) $95137539657
326/08/2021 0:00Dine at Home3:00:00 PM123/08/2021 12:36:00Customer 21Dine at HomeConfirmedRoast Spatchcock (2 ppl) $951Nick Spencer Grüner $381Creamy Tiramisu Cake (Single) $151Strawberry Cheesecake (Single) $15125768036
426/08/2021 0:00Dine at Home3:00:00 PM123/08/2021 10:47:00Customer 31Dine at HomeConfirmedRoast Spatchcock (2 ppl) $951Strawberry Cheesecake (Single) $15219223131
526/08/2021 0:00Dine at Home3:00:00 PM123/08/2021 12:38:00Customer 41Dine at HomeConfirmedRoast Spatchcock (2 ppl) $951Blackberry Chocolate Cake (Single) $151Creamy Tiramisu Cake (Single) $15125902088
626/08/2021 0:00Dine at Home3:30:00 PM123/08/2021 13:30:00Customer 51Dine at HomeConfirmedRoast Spatchcock (2 ppl) $951Shepherd's Pie (2 ppl) $951Creamy Tiramisu Cake (Single) $152Strawberry Cheesecake (Single) $15229015825
726/08/2021 0:00Dine at Home4:30:00 PM123/08/2021 15:06:00Customer 61Dine at HomeConfirmedRoast Spatchcock (2 ppl) $951Creamy Tiramisu Cake (Single) $15134781626
826/08/2021 0:00Dine at Home4:30:00 PM123/08/2021 11:09:00Customer 71Dine at HomeConfirmedJerusalem artichoke agnolotti (Vego) (2 ppl) $95120541130
926/08/2021 0:00Dine at Home4:30:00 PM123/08/2021 15:38:00Customer 81Dine at HomeConfirmedRoast Spatchcock (2 ppl) $951Creamy Tiramisu Cake (Single) $15136682398
1026/08/2021 0:00Dine at Home4:45:00 PM123/08/2021 15:35:00Customer 91Dine at HomeConfirmedShepherd's Pie (2 ppl) $95136535767
1126/08/2021 0:00Dine at Home4:45:00 PM123/08/2021 18:13:00Customer 101Dine at HomeConfirmedShepherd's Pie (2 ppl) $951Creamy Tiramisu Cake (Single) $15345983355
1226/08/2021 0:00Dine at Home4:45:00 PM122/08/2021 18:59:00Customer 111Dine at HomeConfirmedRoast Spatchcock (2 ppl) $951Blackberry Chocolate Cake (Single) $15162363029
1326/08/2021 0:00Dine at Home4:45:00 PM123/08/2021 11:34:00Customer 121Dine at HomeConfirmedJerusalem artichoke agnolotti (Vego) (2 ppl) $951Blackberry Chocolate Cake (Single) $151Strawberry Cheesecake (Single) $15122093976
1426/08/2021 0:00Dine at Home4:45:00 PM123/08/2021 15:56:00Customer 131Dine at HomeConfirmedRoast Spatchcock (2 ppl) $9511/2 Doz Freshly Shucked Appellation Oysters $2522018 Mount Majura Shiraz $381Creamy Tiramisu Cake (Single) $15237788094
1526/08/2021 0:00Dine at Home5:00:00 PM120/08/2021 14:10:00Customer 141Dine at HomeConfirmedRoast Spatchcock (2 ppl) $951Blackberry Chocolate Cake (Single) $151Creamy Tiramisu Cake (Single) $15172236640
1626/08/2021 0:00Dine at Home5:00:00 PM119/08/2021 18:33:00Customer 151Dine at HomeConfirmedRoast Spatchcock (2 ppl) $9511588227
1726/08/2021 0:00Dine at Home5:00:00 PM121/08/2021 12:07:00Customer 161Dine at HomeConfirmedRoast Spatchcock (2 ppl) $95151224778
1826/08/2021 0:00Dine at Home5:00:00 PM122/08/2021 17:37:00Customer 171Dine at HomeConfirmedJerusalem artichoke agnolotti (Vego) (2 ppl) $9512018 Mount Majura Shiraz $381Creamy Tiramisu Cake (Single) $15157443158
1926/08/2021 0:00Dine at Home5:00:00 PM123/08/2021 10:50:00Customer 181Dine at HomeConfirmedRoast Spatchcock (2 ppl) $951Creamy Tiramisu Cake (Single) $15119441989
Query


Result Sheet

Split data out over columns, under unique headings-source.xlsx
ABCDEFGHIJKLMN
1TimeNameRoast Spatchcock (2 ppl) $95Roast Spatchcock (4 ppl)Shepherd's Pie (2 ppl) $95Shepherd's Pie (4 ppl)Jerusalem artichoke agnolotti (Vego) (2 ppl) $96Jerusalem artichoke agnolotti (Vego) (4 ppl)1/2 Doz Freshly Shucked Appellation Oysters $252018 Mount Majura Shiraz $38Nick Spencer Grüner $38Blackberry Chocolate Cake (Single) $15Creamy Tiramisu Cake (Single) $15Strawberry Cheesecake (Single) $15
23:00:00 PMCustomer 121
33:00:00 PMCustomer 21111
43:00:00 PMCustomer 312
53:00:00 PMCustomer 4111
63:30:00 PMCustomer 51122
74:30:00 PMCustomer 611
84:30:00 PMCustomer 7
94:30:00 PMCustomer 811
104:45:00 PMCustomer 91
114:45:00 PMCustomer 1013
124:45:00 PMCustomer 1111
134:45:00 PMCustomer 1211
144:45:00 PMCustomer 131212
155:00:00 PMCustomer 14111
165:00:00 PMCustomer 151
175:00:00 PMCustomer 161
185:00:00 PMCustomer 1711
195:00:00 PMCustomer 1811
Result
Cell Formulas
RangeFormula
A2A2=IF(ROWS($A$2:A2)>COUNTA(Query!$A$2:$A$100),"",INDEX(Query!$C$2:$C$100,SMALL(IF(ISNUMBER(Query!$A$2:$A$100),ROW(Query!$A$2:$A$100)-ROW(Query!$A$2)+1),ROWS($A$2:A2))))
B2B2=IF(ROWS($B$2:B2)>COUNTA(Query!$A$2:$A$100),"",INDEX(Query!$F$2:$F$100,SMALL(IF(ISNUMBER(Query!$A$2:$A$100),ROW(Query!$A$2:$A$100)-ROW(Query!$A$2)+1),ROWS($B$2:B2))))
C2C2=SUMIFS(Table1_2[Booking Options.1.2],Table1_2[Booking Options.1.1],C$1,Table1_2[Customer],$B2)+SUMIFS(Table1_2[Booking Options.2.2],Table1_2[Booking Options.2.1],C$1,Table1_2[Customer],$B2)+SUMIFS(Table1_2[Booking Options.3.2],Table1_2[Booking Options.3.1],C$1,Table1_2[Customer],$B2)+SUMIFS(Table1_2[Booking Options.4.2],Table1_2[Booking Options.4.1],C$1,Table1_2[Customer],$B2)
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
Solution
Thanks so much RasGhul. That is perfect and totally solves the issue. I'll implement that now. I've not had much experience with power query but before I posted this message I spent a bit of time searching for an answer on the forum and see that it's a hugely powerful tool. Pretty excited to get into it!

Thanks again.
 
Upvote 0
Thanks for the feedback, Power query is great for these scenarios.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
VBA to split data
Replies
1
Views
373
duggie33
D
N
  • Question Question
Split excel Data into separate Workbook rather then tabs.
Replies
2
Views
616
nihad
N
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
C
  • Question Question
Formula to find Max number of items in orders
Replies
1
Views
680
fjns
F
H
  • Solved
split duplicate data from sheet to another based on three columns together
Replies
4
Views
375
Hasson
H

Forum statistics

Threads
1,223,910
Messages
6,175,316
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top