transpose data query

H

heathball

Board Regular
Joined
Apr 6, 2017
Messages
135
Office Version
  1. 365
Platform
  1. Windows
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:confused:

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.



[TABLE="width: 382"]
<tbody>[TR]
[TD]date[/TD]
[TD]number[/TD]
[TD]name[/TD]
[TD]date rank (most recent on top)[/TD]
[/TR]
[TR]
[TD]15/04/2018[/TD]
[TD]34.4[/TD]
[TD]peter[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]22/03/2018[/TD]
[TD]34.6[/TD]
[TD]peter[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]23/12/2017[/TD]
[TD]38.1[/TD]
[TD]peter[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]6/12/2017[/TD]
[TD]36[/TD]
[TD]peter[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]18/11/2017[/TD]
[TD]36.9[/TD]
[TD]peter[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]23/09/2017[/TD]
[TD]40.9[/TD]
[TD]peter[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]23/08/2017[/TD]
[TD]40.3[/TD]
[TD]peter[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]31/12/2016[/TD]
[TD]33.8[/TD]
[TD]peter[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]9/09/2018[/TD]
[TD]52.5[/TD]
[TD]mary[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]26/08/2018[/TD]
[TD]49.8[/TD]
[TD]mary[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]3/10/2018[/TD]
[TD]52.5[/TD]
[TD]mary[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]23/09/2018[/TD]
[TD]50.2[/TD]
[TD]mary[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]2/05/2018[/TD]
[TD]42.7[/TD]
[TD]mary[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]15/01/2016[/TD]
[TD]41[/TD]
[TD]steve[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]28/12/2015[/TD]
[TD]41[/TD]
[TD]steve[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]8/12/2015[/TD]
[TD]27.1[/TD]
[TD]steve[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]28/11/2015[/TD]
[TD]37.3[/TD]
[TD]steve[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]9/11/2015[/TD]
[TD]38.5[/TD]
[TD]steve[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]5/12/2017[/TD]
[TD]29[/TD]
[TD]rod[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]23/11/2017[/TD]
[TD]30.9[/TD]
[TD]rod[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]25/07/2017[/TD]
[TD]30.2[/TD]
[TD]rod[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]6/07/2017[/TD]
[TD]41.7[/TD]
[TD]rod[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]24/09/2016[/TD]
[TD]31.5[/TD]
[TD]Ian[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]3/09/2016[/TD]
[TD]32.6[/TD]
[TD]Ian[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]27/08/2016[/TD]
[TD]37.2[/TD]
[TD]Ian[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]11/06/2016[/TD]
[TD]30.5[/TD]
[TD]Ian[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]21/05/2016[/TD]
[TD]42.3[/TD]
[TD]Ian[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]25/04/2016[/TD]
[TD]37.6[/TD]
[TD]Ian[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]15/04/2016[/TD]
[TD]42.4[/TD]
[TD]Ian[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]24/10/2015[/TD]
[TD]39.6[/TD]
[TD]Ian[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]2/02/2018[/TD]
[TD]45.6[/TD]
[TD]Julie[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]24/01/2018[/TD]
[TD]41.7[/TD]
[TD]Julie[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]17/12/2017[/TD]
[TD]40.2[/TD]
[TD]Julie[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]24/11/2017[/TD]
[TD]43.3[/TD]
[TD]Julie[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]19/03/2017[/TD]
[TD]48.3[/TD]
[TD]Julie[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]3/03/2017[/TD]
[TD]42.1[/TD]
[TD]Julie[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]19/02/2017[/TD]
[TD]46.9[/TD]
[TD]Julie[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]29/01/2017[/TD]
[TD]38.5[/TD]
[TD]Julie[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]6/01/2017[/TD]
[TD]46.2[/TD]
[TD]Julie[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]18/12/2016[/TD]
[TD]47[/TD]
[TD]Julie[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]25/11/2016[/TD]
[TD]45[/TD]
[TD]Julie[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]13/11/2016[/TD]
[TD]43.5[/TD]
[TD]Julie[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]4/03/2016[/TD]
[TD]42.9[/TD]
[TD]Julie[/TD]
[TD="align: right"]13[/TD]
[/TR]
[TR]
[TD]21/02/2016[/TD]
[TD]46.9[/TD]
[TD]Julie[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]31/01/2016[/TD]
[TD]43.6[/TD]
[TD]Julie[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]20/12/2015[/TD]
[TD]41.3[/TD]
[TD]Julie[/TD]
[TD="align: right"]16[/TD]
[/TR]
</tbody>[/TABLE]


Desired result.....

[TABLE="width: 1139"]
<tbody>[TR]
[TD]name[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]16[/TD]
[/TR]
[TR]
[TD]peter[/TD]
[TD="align: right"]34.4[/TD]
[TD="align: right"]34.6[/TD]
[TD="align: right"]38.1[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]36.9[/TD]
[TD="align: right"]40.9[/TD]
[TD="align: right"]40.3[/TD]
[TD="align: right"]33.8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]mary[/TD]
[TD="align: right"]52.5[/TD]
[TD="align: right"]49.8[/TD]
[TD="align: right"]52.5[/TD]
[TD="align: right"]50.2[/TD]
[TD="align: right"]42.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]steve[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]41[/TD]
[TD="align: right"]27.1[/TD]
[TD="align: right"]37.3[/TD]
[TD="align: right"]38.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]rod[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]30.9[/TD]
[TD="align: right"]30.2[/TD]
[TD="align: right"]41.7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ian[/TD]
[TD="align: right"]31.5[/TD]
[TD="align: right"]32.6[/TD]
[TD="align: right"]37.2[/TD]
[TD="align: right"]30.5[/TD]
[TD="align: right"]42.3[/TD]
[TD="align: right"]37.6[/TD]
[TD="align: right"]42.4[/TD]
[TD="align: right"]39.6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Julie[/TD]
[TD="align: right"]45.6[/TD]
[TD="align: right"]41.7[/TD]
[TD="align: right"]40.2[/TD]
[TD="align: right"]43.3[/TD]
[TD="align: right"]48.3[/TD]
[TD="align: right"]42.1[/TD]
[TD="align: right"]46.9[/TD]
[TD="align: right"]38.5[/TD]
[TD="align: right"]46.2[/TD]
[TD="align: right"]47[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]43.5[/TD]
[TD="align: right"]42.9[/TD]
[TD="align: right"]46.9[/TD]
[TD="align: right"]43.6[/TD]
[TD="align: right"]41.3[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Sort by date Sort by votes

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Sorry Fluff, i thought a sent a message but the internet might have died.

yes, that was it!

that problem is solved...i forgot to check for errors:eeek:

I'm not sure if it is the cause of some instability with the way i use macros on a separate file. My macro file blocked twice since i started using the code.

If i cannot work it out, i might put the code as a new message and see if someone may know why it is it not working smoothly as a module in the macro file.

thanks again for your time.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
HELP IDENTIFY ORIGINAL START DATE BASED ON CONTINOUS SERVICE RULES
Replies
0
Views
569
mrshuck
M
V
  • Solved
Formula to calculate automatically progressive average
Replies
5
Views
506
etaf
etaf
C
  • Question Question
Subtotals and counta I think
Replies
1
Views
756
t0ny84
T
H
  • Solved
Creation of arrows for 2 family trees.
Replies
8
Views
372
harzer
H
F
  • Solved
Formula determine if a date falls between a specific M/D range for multiple years for YTD comparison.
Replies
6
Views
945
flashgordie
F

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top