I have a data set that has a grouping of columns by date for each record. There are 6 dates and 8 columns in each group. I am needing to consolidate the columns by date for each record and am trying the Tile tool but am finding it tricky.
Below is the general current and desired data structure.
| Current Structure | | | | | | | | | | | | |
| RecordID | Date1 | Bucket1 | Bucket2 | Bucket3 | Bucket4 | Bucket5 | Bucket6 | Bucket7 | Bucket8 | Date2 | Bucket1 | Bucket2 | Bucket3 |
| 1 | | | | | | | | | | | | | |
| 2 | | | | | | | | | | | | | |
| 3 | | | | | | | | | | | | | |
| 4 | | | | | | | | | | | | | |
| 5 | | | | | | | | | | | | | |
| | | | | | | | | | | | | | |
| Desired Structure | | | | | | | | | | | | |
| RecordID | Date | Bucket1 | Bucket2 | Bucket3 | Bucket4 | Bucket5 | Bucket6 | Bucket7 | Bucket8 | | | | |
| 1 | Date1 | | | | | | | | | | | | |
| 1 | Date2 | | | | | | | | | | | | |
| 1 | Date3 | | | | | | | | | | | | |
| 1 | Date4 | | | | | | | | | | | | |
| 1 | Date5 | | | | | | | | | | | | |
| 1 | Date6 | | | | | | | | | | | | |
| 2 | Date1 | | | | | | | | | | | | |
| 2 | Date2 | | | | | | | | | | | | |
| 2 | Date3 | | | | | | | | | | | | |
| 2 | Date4 | | | | | | | | | | | | |
| 2 | Date5 | | | | | | | | | | | | |
| 2 | Date6 | | | | | | | | | | | | |
Thanks in advance!
UPDATE: I did not describe what was in my data and I apologize. All columns, with the exception of the RecordID are numeric values. The purpose of the analysis is to aggregate inputs to each bucket per record from date to date. This would need an additional column for the amount by date.
| Current Structure | | | | | | | | | | | | | | | |
| Record ID | Amt - Date1 | Bucket1 | Bucket2 | Bucket3 | Bucket4 | Bucket5 | Bucket6 | Bucket7 | Bucket8 | Amt - Date2 | Bucket1 | Bucket2 | Bucket3 | Bucket4 | Bucket5 | Bucket6 |
| 12345 | 50,000.00 | 20.00 | -500.00 | 25.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 49545.00 | 0.00 | 0.00 | 0.00 | 500.00 | 0.00 | 0.00 |
| 55555 | 20000 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 15.00 | 0.00 | 20015.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 80.00 |
| 66666 | 50 | 0.00 | -10.00 | 750.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 790.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| | | | | | | | | | | | | | | | | |
| | | | | | | | | | | | | | | | | |
| | | | | | | | | | | | | | | | | |
| | | | | | | | | | | | | | | | | |
| | | | | | | | | | | | | | | | | |
| Desired Structure | | | | | | | | | | | | | | | |
| Record ID | Date | Amount by Date | Bucket1 | Bucket2 | Bucket3 | Bucket4 | Bucket5 | Bucket6 | Bucket7 | Bucket8 | | | | | | |
| 12345 | Amt - Date1 | 50000.00 | 20.00 | -500.00 | 25.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | | | | | | |
| 12345 | Amt - Date2 | 49545.00 | 0.00 | 0.00 | 0.00 | 500.00 | 0.00 | 0.00 | 0.00 | 0.00 | | | | | | |
| 12345 | Amt - Date3 | 50045.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | | | | | | |
| 12345 | Amt - Date4 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | | | | | | |
| 12345 | Amt - Date5 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | | | | | | |
| 12345 | Amt - Date6 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | | | | | | |
| 55555 | Amt - Date1 | 20000.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 15.00 | 0.00 | | | | | | |
| 55555 | Amt - Date2 | 20015.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 80.00 | 0.00 | 0.00 | | | | | | |
| 55555 | Amt - Date3 | 20095.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | | | | | | |
| 55555 | Amt - Date4 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | | | | | | |
| 55555 | Amt - Date5 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | | | | | | |
| 55555 | Amt - Date6 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | | | | | | |
| 66666 | Amt - Date1 | 50.00 | 0.00 | -10.00 | 750.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | | | | | | |
| 66666 | Amt - Date2 | 790.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 5000.00 | | | | | | |
| 66666 | Amt - Date3 | 5790.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | | | | | | |
| 66666 | Amt - Date4 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | | | | | | |
| 66666 | Amt - Date5 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | | | | | | |
| 66666 | Amt - Date6 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | | | | | | |
