Johnny Thunder
Well-known Member
- Joined
- Apr 9, 2010
- Messages
- 693
- Office Version
- 2016
- Platform
- MacOS
Hi Everyone,
I am working on a project that takes manually entered data in a horizontal layout and converts into a vertical data table. I have already built several things in the workbook to automate the process of data entry but am now stuck on the portion that will convert the data into a more vertical style data table.
Here is what the current report looks like:
For the sample data my PreChecks end at #2 but my working file goes out to #5 so will need to factor that in with any suggestions made.
What I am hoping to achieve is to Paste the values from Column A:K in a new Sheet "Mock Table" and append the Precheck Comments to the right of the data and also include the Precheck number at the end of the pasted data. There are occasions where there is more than one PreCheck that happen on the same row which is why I need to spin the data into a more vertical style table (see below example).
Here is what I am hoping the new Table would look like. You can see in the example Row 2 And 3 have the same details from Columns A:K but the PreCheck data was from two different numbers. This is what I am hoping to achieve. Also, in the event that there is no data in the Precheck fields the code will skip that section and move to the next. Any ideas are appreciated. ?
I am working on a project that takes manually entered data in a horizontal layout and converts into a vertical data table. I have already built several things in the workbook to automate the process of data entry but am now stuck on the portion that will convert the data into a more vertical style data table.
Here is what the current report looks like:
PreRoll Working File V1.xlsm | ||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | |||
1 | Week To Copy: | Friday, May 15, 2020 | ||||||||||||||||||||
2 | ||||||||||||||||||||||
3 | PreCheck 1 | PreCheck 1 | PreCheck 1 | PreCheck 1 | PreCheck 2 | PreCheck 2 | PreCheck 2 | PreCheck 2 | ||||||||||||||
4 | Comment: | Example Comment: 4th of July Coming Soon | PreCheck 1 | PreCheck 2 | ||||||||||||||||||
5 | Line | Order No | Product | Seasonal | Count | Start Date | Product Type | Class | Pre Awareness | Sustain length in days | End Date | Promo | Seasonal | Action | Date | Promo | Seasonal | Action | Date | |||
6 | 1 | 100001 | Wrench | S01 | 01 | 11/1/20 | Hand Tools | Consumer | Skip | 28 | 11/29/20 | Summer Sale | Summer | 25% Sale | 11/15/20 | Fall | Clearance Sale | 11/20/20 | ||||
7 | 2 | 100002 | Hammer | S01 | 02 | 11/1/20 | Hand Tools | Consumer | Skip | 28 | 11/29/20 | Spring | Clearance Sale | 11/21/20 | ||||||||
8 | 3 | 100003 | Drill | S01 | 03 | 11/1/20 | Hand Tools | Consumer | Skip | 28 | 11/29/20 | Home Improvement | Display | 11/7/20 | Tool Time | Banner | 11/7/20 | |||||
9 | 4 | 100004 | Tool Belt | S01 | 04 | 11/8/20 | Hand Tools | Consumer | Skip | 28 | 12/6/20 | Toolman | Winter | Display | 11/30/20 | |||||||
Mock Data |
For the sample data my PreChecks end at #2 but my working file goes out to #5 so will need to factor that in with any suggestions made.
What I am hoping to achieve is to Paste the values from Column A:K in a new Sheet "Mock Table" and append the Precheck Comments to the right of the data and also include the Precheck number at the end of the pasted data. There are occasions where there is more than one PreCheck that happen on the same row which is why I need to spin the data into a more vertical style table (see below example).
Here is what I am hoping the new Table would look like. You can see in the example Row 2 And 3 have the same details from Columns A:K but the PreCheck data was from two different numbers. This is what I am hoping to achieve. Also, in the event that there is no data in the Precheck fields the code will skip that section and move to the next. Any ideas are appreciated. ?
PreRoll Working File V1.xlsm | ||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | |||
1 | Line | Order No | Product | Seasonal | Count | Start Date | Product Type | Class | Pre Awareness | Sustain length in days | End Date | Promo | Seasonal | Action | Date | Check Status | ||
2 | 1 | 100001 | Wrench | S01 | 01 | 44136 | Hand Tools | Consumer | Skip | 28 | 11/29/20 | Summer Sale | Summer | 25% Sale | 11/15/20 | PreCheck 1 | ||
3 | 1 | 100001 | Wrench | S01 | 01 | 44136 | Hand Tools | Consumer | Skip | 28 | 11/29/20 | Fall | Clearance Sale | 11/20/20 | PreCheck 2 | |||
4 | 2 | 100002 | Hammer | S01 | 02 | 44136 | Hand Tools | Consumer | Skip | 28 | 11/29/20 | Spring | Clearance Sale | 11/21/20 | PreCheck 2 | |||
5 | 3 | 100003 | Drill | S01 | 03 | 44136 | Hand Tools | Consumer | Skip | 28 | 11/29/20 | Home Improvement | Display | 11/7/20 | PreCheck 1 | |||
6 | 3 | 100003 | Drill | S01 | 03 | 44136 | Hand Tools | Consumer | Skip | 28 | 11/29/20 | Tool Time | Banner | 11/7/20 | PreCheck 2 | |||
7 | 4 | 100004 | Tool Belt | S01 | 04 | 44143 | Hand Tools | Consumer | Skip | 28 | 12/6/20 | Toolman | Winter | Display | 11/30/20 | PreCheck 2 | ||
Mock Table |