i have been working on an efficient way to get this excel data to transpose in the way that appears underneath it, at the bottom of the post
the way i do involves several steps and its painfully slow, and the data set can be large, and as i need to do it 3 times a week, I'm hoping for a faster solution
Cells are all pasted values, no formulas. There may be values of up to 150 in the 'date rank' column.
I'm looking to transpose this data onto a separate sheet in the same work book.
I'm especially interested in a VB code that i can apply as a macro.
Thanks in advance if anybody can offer some assistance.
<tbody>
</tbody>
Desired result.....
<tbody>
</tbody>
the way i do involves several steps and its painfully slow, and the data set can be large, and as i need to do it 3 times a week, I'm hoping for a faster solution
Cells are all pasted values, no formulas. There may be values of up to 150 in the 'date rank' column.
I'm looking to transpose this data onto a separate sheet in the same work book.
I'm especially interested in a VB code that i can apply as a macro.
Thanks in advance if anybody can offer some assistance.
date | number | name | date rank (most recent on top) |
15/04/2018 | 34.4 | peter | 1 |
22/03/2018 | 34.6 | peter | 2 |
23/12/2017 | 38.1 | peter | 3 |
6/12/2017 | 36 | peter | 4 |
18/11/2017 | 36.9 | peter | 5 |
23/09/2017 | 40.9 | peter | 6 |
23/08/2017 | 40.3 | peter | 7 |
31/12/2016 | 33.8 | peter | 8 |
9/09/2018 | 52.5 | mary | 1 |
26/08/2018 | 49.8 | mary | 2 |
3/10/2018 | 52.5 | mary | 3 |
23/09/2018 | 50.2 | mary | 4 |
2/05/2018 | 42.7 | mary | 5 |
15/01/2016 | 41 | steve | 1 |
28/12/2015 | 41 | steve | 2 |
8/12/2015 | 27.1 | steve | 3 |
28/11/2015 | 37.3 | steve | 4 |
9/11/2015 | 38.5 | steve | 5 |
5/12/2017 | 29 | rod | 1 |
23/11/2017 | 30.9 | rod | 2 |
25/07/2017 | 30.2 | rod | 3 |
6/07/2017 | 41.7 | rod | 4 |
24/09/2016 | 31.5 | Ian | 1 |
3/09/2016 | 32.6 | Ian | 2 |
27/08/2016 | 37.2 | Ian | 3 |
11/06/2016 | 30.5 | Ian | 4 |
21/05/2016 | 42.3 | Ian | 5 |
25/04/2016 | 37.6 | Ian | 6 |
15/04/2016 | 42.4 | Ian | 7 |
24/10/2015 | 39.6 | Ian | 8 |
2/02/2018 | 45.6 | Julie | 1 |
24/01/2018 | 41.7 | Julie | 2 |
17/12/2017 | 40.2 | Julie | 3 |
24/11/2017 | 43.3 | Julie | 4 |
19/03/2017 | 48.3 | Julie | 5 |
3/03/2017 | 42.1 | Julie | 6 |
19/02/2017 | 46.9 | Julie | 7 |
29/01/2017 | 38.5 | Julie | 8 |
6/01/2017 | 46.2 | Julie | 9 |
18/12/2016 | 47 | Julie | 10 |
25/11/2016 | 45 | Julie | 11 |
13/11/2016 | 43.5 | Julie | 12 |
4/03/2016 | 42.9 | Julie | 13 |
21/02/2016 | 46.9 | Julie | 14 |
31/01/2016 | 43.6 | Julie | 15 |
20/12/2015 | 41.3 | Julie | 16 |
<tbody>
</tbody>
Desired result.....
name | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 |
peter | 34.4 | 34.6 | 38.1 | 36 | 36.9 | 40.9 | 40.3 | 33.8 | ||||||||
mary | 52.5 | 49.8 | 52.5 | 50.2 | 42.7 | |||||||||||
steve | 41 | 41 | 27.1 | 37.3 | 38.5 | |||||||||||
rod | 29 | 30.9 | 30.2 | 41.7 | ||||||||||||
Ian | 31.5 | 32.6 | 37.2 | 30.5 | 42.3 | 37.6 | 42.4 | 39.6 | ||||||||
Julie | 45.6 | 41.7 | 40.2 | 43.3 | 48.3 | 42.1 | 46.9 | 38.5 | 46.2 | 47 | 45 | 43.5 | 42.9 | 46.9 | 43.6 | 41.3 |
<tbody>
</tbody>
Last edited: