Hello Excel Gurus and thanks for any help you're able to provide!
I have a file with 27 columns (A:AA) and 202 rows (plus 1 header row).
I have three columns that I need help with.
I suspect a VBA Loop of some sort is needed but I don’t know how to write it to combine all the elements.
One: Column B, Date Time, appears as: 3/18/2021 8:25:11 PM
I would like to add two new columns, C and D, and,
Two: Column D, Details
This column contains multiple elements that need to be separated into columns and rows
Sometimes this cell contains only one line of Quantity/Price/Age/Product, and other times multiple lines
The first line is always blank, the last line is always Subtotal, the other characters and spacing are consistent
Sample data from one cell (5 lines):
I have used this formula to break this apart on the same row:
AD1 is the occurrence number that I manually added (above data would have 5 columns)
Result in a single cell: 7 x $40.00 : Adult Lift Ticket
I then deleted the columns with the first blank line and the subtotal line.
And, of course, I still didn’t get to my goal of having each element in a separate column.
Ultimately, this approach didn’t help me get to the point of actually analyzing the data set and was a very manual process.
Three: Column C, Amount
This is the Subtotal from Column D and appears as 380
As Step Two is completed, this needs to reflect the subtotal of the row.
Go from this:
To this:
Important notes:
Quantity – no instances in the data of this being more than a single digit
Price – instances in the data include: $00.00 and $0.00 and I'm okay with this becoming 0
Age – there are four categories (see table below)
Product – there are two categories (see table below)
Thanks!
I have a file with 27 columns (A:AA) and 202 rows (plus 1 header row).
I have three columns that I need help with.
I suspect a VBA Loop of some sort is needed but I don’t know how to write it to combine all the elements.
One: Column B, Date Time, appears as: 3/18/2021 8:25:11 PM
I would like to add two new columns, C and D, and,
Capture the Date in column C (I’m using =INT(B2) and then formatting as Short Date)
Capture the Time in column D (I’m using =B2-INT(B2) and then formatting as Time 13:30)
I've also done this using Record Macro and that would be fine if this was the only step needed.
Two: Column D, Details
This column contains multiple elements that need to be separated into columns and rows
Sometimes this cell contains only one line of Quantity/Price/Age/Product, and other times multiple lines
The first line is always blank, the last line is always Subtotal, the other characters and spacing are consistent
Sample data from one cell (5 lines):
7 x $40.00 : Adult Lift Ticket
5 x $20.00 : Youth (6-12) Lift Ticket
4 x $0.00 : 5 and Under Lift Ticket
Subtotal : $380.00
I have used this formula to break this apart on the same row:
= TRIM( MID(SUBSTITUTE( $F2, CHAR(10), REPT( " ",LEN($F2) ) ), (AD$1-1)*LEN($F2)+1, LEN($F2)) )
F2 is DetailsAD1 is the occurrence number that I manually added (above data would have 5 columns)
Result in a single cell: 7 x $40.00 : Adult Lift Ticket
I then deleted the columns with the first blank line and the subtotal line.
And, of course, I still didn’t get to my goal of having each element in a separate column.
Ultimately, this approach didn’t help me get to the point of actually analyzing the data set and was a very manual process.
Three: Column C, Amount
This is the Subtotal from Column D and appears as 380
As Step Two is completed, this needs to reflect the subtotal of the row.
Go from this:
A | B | C | D |
Name | Date Time | Amount | Details |
Mickey | 3/11/21 3:34 | 380 | 7 x $40.00 : Adult Lift Ticket 5 x $20.00 : Youth (6-12) Lift Ticket 4 x $0.00 : 5 and Under Lift Ticket Subtotal : $380.00 |
To this:
- Amount is F*G
- No need to keep the Details column
- keep the other columns to the right, copied by row as necessary
A | B | C | D | E | F | G | H | I |
Name | Date Time | Date | Time | Amount | Quantity | Price | Age | Product |
Mickey | 3/11/21 3:34 | 3/11/21 | 3:34:43 AM | 280 | 7 | 40 | Adult | Lift Ticket |
Mickey | 3/11/21 3:34 | 3/11/21 | 3:34:43 AM | 100 | 5 | 20 | Youth (6-12) | Lift Ticket |
Mickey | 3/11/21 3:34 | 3/11/21 | 3:34:43 AM | 0 | 4 | 0 | 5 and Under | Lift Ticket |
Important notes:
Quantity – no instances in the data of this being more than a single digit
Price – instances in the data include: $00.00 and $0.00 and I'm okay with this becoming 0
Age – there are four categories (see table below)
Product – there are two categories (see table below)
Age | Product |
Adult | Lift Ticket |
Youth (6-12) | Rental |
5 and Under | |
Student/Military/Senior |
Thanks!