Forum Discussion
MrExcel 2022 Boosting Excel Move Cells Upwards To Combine Entries Using VBA/formulas
I run a ticketing system that gives raw data as below :
1) Each ticket I.D. has 3 categories of waiting time, and some other details
2) If Ticket A has all 3 categories, then A to K of row 1-3 will show the same ticket info, but then cells L1-P1 will show the data of waiting time 1, cells L2-P2 waiting time 2 and so on
3) A ticket will only have a minimum of 1 entry and a maximum of 3 entries
4) Combinations of waiting time 1 and 3, 1 and 2, or 2 and 3 may occur
5) The raw data table has already sorted the list so that the same tickets sit in rows next to each other
6) This table of data has to be presented on a regular basis
I want to present each ticket in one same row such that the info for all categories of waiting time in rows becomes in columns, i.e. : Ticket A will only take row 1, but cells A - K is ticket data, L - P is info for waiting time 1, Q - U is info for waiting time 2 and so on. This ticketing system has around 1000 entries in one go. The attached picture shows the raw data (above) and what is required (below). I have tried my hardest but I really couldn't figure out a way in the macro. :')
1 Reply
Jacqq Perhaps you want to consider Power Query (PQ). If you create a structured table called "Tickets" with the exact same structure as in the attached example (RawData sheet, the blue table). I.e. 16 columns wide with headers "Column01" through "Column16", then the script should work. Columns 12 to 16 must contain the information specific to each category. Just press Refresh All on the Data ribbon, and review the result in the "TicketByRow" sheet (the green table).
It's a quick-and dirty-approach and I have not made any attempts to make it dynamic or very pretty. If you change the column names or add or delete columns it will break.
