Hi all,
I asked a similar question a week or two ago, but I was a little vague with exactly what I was looking for so the answers given (whilst great) often came with more questions from myself.
To skip to the important part I have a data source that looks like the following. All sheets are contained within one workbook. The below is taken from Worksheets("Source").
This is only the first 20 rows. The data currently goes down to 342, but is variable - though I would be quite shocked if the value was ever above 5k. The data is also sorted by date/time as per B:B - the earliest date/time will always be first.
I wish to create a table (I don't specifically mean the table feature in excel but table rather just organise the data in a more readable way) where the numbers in A:A act as a header across the top starting in C1. These headers should be unique (note values in A:A appear more than once, but the data should be consolidated if that makes sense). This 'table' will be on Worksheets("Results")
The date/time axis should begin in A4 - though this is where things are a little tricky. As per the source data, the first time would be 6:30 on the given date. Times are also dealt with in 15 minute intervals. however, notice that B12:B13 skips 45 minutes (three intervals or so). This means that using B:B as the Axis on the final table is not desireable as the axis may have missing chunks of time depending on the source data. To get around this, the date/time axis already exists on the result sheet - I have a bit of code that looks for the time in B1, places it in A4 and loops through rowno 4 To 148 (final row ~ 36 hours from start time but this doesn't really matter at this stage) adding 15 minutes each step.
With the example source data, the Results sheet should look like below - I'm looking for a VBA solution to this
The gap in B and rows 2,3 is deliberate, I'll be adding other data as part of a sep process in those spaces - though if needs be, these can be ignored and I can simply add an extra column and two new rows at a later time.
I think I've covered everything, but if I've missed anything crucial, please let me know.
I asked a similar question a week or two ago, but I was a little vague with exactly what I was looking for so the answers given (whilst great) often came with more questions from myself.
To skip to the important part I have a data source that looks like the following. All sheets are contained within one workbook. The below is taken from Worksheets("Source").
| 286 | 06:30:00 | 46 |
| 214 | 06:30:00 | 46 |
| 1535 | 06:30:00 | 46 |
| 1580 | 06:30:00 | 46 |
| 1307 | 06:30:00 | 46 |
| 1029 | 06:30:00 | 53 |
| 57 | 06:30:00 | 23 |
| 286 | 06:45:00 | 12 |
| 214 | 06:45:00 | 12 |
| 1535 | 06:45:00 | 12 |
| 1580 | 06:45:00 | 12 |
| 1307 | 06:45:00 | 12 |
| 932 | 07:30:00 | 46 |
| 538 | 07:30:00 | 46 |
| 932 | 07:45:00 | 12 |
| 542 | 07:45:00 | 46 |
| 538 | 07:45:00 | 12 |
| 542 | 08:00:00 | 12 |
| 241 | 08:00:00 | 46 |
| 989 | 08:00:00 | 23 |
This is only the first 20 rows. The data currently goes down to 342, but is variable - though I would be quite shocked if the value was ever above 5k. The data is also sorted by date/time as per B:B - the earliest date/time will always be first.
I wish to create a table (I don't specifically mean the table feature in excel but table rather just organise the data in a more readable way) where the numbers in A:A act as a header across the top starting in C1. These headers should be unique (note values in A:A appear more than once, but the data should be consolidated if that makes sense). This 'table' will be on Worksheets("Results")
The date/time axis should begin in A4 - though this is where things are a little tricky. As per the source data, the first time would be 6:30 on the given date. Times are also dealt with in 15 minute intervals. however, notice that B12:B13 skips 45 minutes (three intervals or so). This means that using B:B as the Axis on the final table is not desireable as the axis may have missing chunks of time depending on the source data. To get around this, the date/time axis already exists on the result sheet - I have a bit of code that looks for the time in B1, places it in A4 and loops through rowno 4 To 148 (final row ~ 36 hours from start time but this doesn't really matter at this stage) adding 15 minutes each step.
With the example source data, the Results sheet should look like below - I'm looking for a VBA solution to this
| ID# | 286 | 214 | 1535 | 1580 | 1307 | 1029 | 57 | 932 | 538 | 542 | 241 | 989 | |
| Part (ignore) | /// | /// | /// | ||||||||||
| Time | |||||||||||||
| 06:30:00 | 46 | 46 | 46 | 46 | 46 | 53 | 23 | ||||||
| 06:45:00 | 12 | 12 | 12 | 12 | 12 | ||||||||
| 07:00:00 | |||||||||||||
| 07:15:00 | |||||||||||||
| 07:30:00 | 46 | 46 | 46 | ||||||||||
| 07:45:00 | 12 | 12 | |||||||||||
| 08:00:00 | 12 | 46 | 23 | ||||||||||
The gap in B and rows 2,3 is deliberate, I'll be adding other data as part of a sep process in those spaces - though if needs be, these can be ignored and I can simply add an extra column and two new rows at a later time.
I think I've covered everything, but if I've missed anything crucial, please let me know.
This will find the end of your data on sheet1.
