I have a table that looks a bit like the one below (dummy data)
There are actually 254 rows and 33 Weight columns.
The data in the first row is header data and refers to Weights.
The data in the first column refers to a Destination.
The data in the table in what looks like B2:J6 are Services.
I need to end up with this data turned into a list as shown below.
For each row, where a Service exists under a weight band, the Service is deemed to deliver items upto the Weight listed to the Destination. Thus, for Destination A1, it is delivered to by Service AAAA for all deliveries from 0.01 (0.01 above previous weight band) to the weight band last weight band before the cell value changes, 0.01 to 2. There is a change of Service at 3 and again at 5. So, for Destination A1, there are three rows in the new output table.
Where POA is listed, no service is in place and the weight is skipped. For Destination 2C though, the entries are listed as 2C - 0.01 - 1 BBBB and then 2C - 3.01 - 4.00 BBBB as there are entries of POA between the two sets of Service of BBBB for Weights 2 and 3.
The data in the first table is dynamic and will change from day to day.
What I would like help with is the efficient VBA code that can convert from my table into the desired output format.
Happy to provide further information if my first explanation needs clarification.
Many thanks for your time and effort.
ASC | .25 | .5 | .75 | 1 | 2 | 3 | 4 | 5 | 6 |
A1 | AAAA | AAAA | AAAA | AAAA | AAAA | BBBB | BBBB | AAAA | AAAA |
2B | AAAA | AAAA | BBBB | AAAA | AAAA | AAAA | AAAA | AAAA | BBBB |
2C | BBBB | BBBB | BBBB | BBBB | POA | POA | BBBB | CCCC | CCCC |
3C | CCCC | DDDD | DDDD | AAAA | POA | CCCC | BBBB | CCCC | DDDD |
4D | POA | CCCC | CCCC | POA | EEEE | DDDD | DDDD | EEEE | EEEE |
There are actually 254 rows and 33 Weight columns.
The data in the first row is header data and refers to Weights.
The data in the first column refers to a Destination.
The data in the table in what looks like B2:J6 are Services.
I need to end up with this data turned into a list as shown below.
Destination | PUD | Weight From | Weight To | Service |
A1 | 0.01 | 2 | AAAA | |
A1 | 2.01 | 4 | BBBB | |
A1 | 4.01 | 6 | AAAA | |
2B | 0.01 | 0.5 | AAAA | |
2B | 0.51 | 0.75 | BBBB | |
2B | 0.76 | 5 | AAAA | |
2C | 0.01 | 1 | BBBB | |
2C | 3.01 | 4 | BBBB | |
2C | 4.01 | 6 | CCCC | |
3C | 0.01 | 0.25 | CCCC | |
3C | .026 | 0.75 | DDDD | |
3C | 0.76 | 1 | AAAA | |
3C | 2.01 | 3 | CCCC | |
3C | 3.01 | 4 | BBBB | |
3C | 4.01 | 5 | CCCC | |
3C | 5.01 | 6 | DDDD | |
4D | .026 | 0.75 | CCCC | |
4D | 1.01 | 2 | EEEE | |
4D | 2.01 | 4 | DDDD | |
4D | 4.01 | 6 | EEEE |
For each row, where a Service exists under a weight band, the Service is deemed to deliver items upto the Weight listed to the Destination. Thus, for Destination A1, it is delivered to by Service AAAA for all deliveries from 0.01 (0.01 above previous weight band) to the weight band last weight band before the cell value changes, 0.01 to 2. There is a change of Service at 3 and again at 5. So, for Destination A1, there are three rows in the new output table.
Where POA is listed, no service is in place and the weight is skipped. For Destination 2C though, the entries are listed as 2C - 0.01 - 1 BBBB and then 2C - 3.01 - 4.00 BBBB as there are entries of POA between the two sets of Service of BBBB for Weights 2 and 3.
The data in the first table is dynamic and will change from day to day.
What I would like help with is the efficient VBA code that can convert from my table into the desired output format.
Happy to provide further information if my first explanation needs clarification.
Many thanks for your time and effort.