VBA Help for Copy range & Transpose

[Not_Excel_lent]

Thanks in advance for the help.

I have a spreadsheet of around 40,000+ rows (and growing) which I need help getting to transpose.

Raw data is in column A to C and looks like the below

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date time[/TD]
[TD]Name[/TD]
[TD]Temperature[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130, align: right"]1/10/2018 1:55:49[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]C1tc1[/TD]
[TD]18.35[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130, align: right"]1/10/2018 1:55:49[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="width: 85"]c1tc2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]19.11
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130, align: right"]1/10/2018 1:55:49[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="width: 85"]c1tc3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]20.11[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130, align: right"]1/10/2018 1:55:49[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="width: 85"]c1tc4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]16.35[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130, align: right"]1/10/2018 2:06:18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]C1tc1[/TD]
[TD]17.22[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130, align: right"]1/10/2018 2:06:18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]c1tc2[/TD]
[TD]21.45[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130, align: right"]1/10/2018 2:06:18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]c1tc3[/TD]
[TD]19.44[/TD]
[/TR]
[TR]
[TD][TABLE="width: 130"]
<tbody>[TR]
[TD="class: xl65, width: 130, align: right"]1/10/2018 2:06:18[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]c1tc4[/TD]
[TD]18.39[/TD]
[/TR]
</tbody>[/TABLE]

Column E to AA looks like the below (manually transposed the first 10 rows).

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date time[/TD]
[TD]C1tc1[/TD]
[TD]c1tc2[/TD]
[TD]c1tc3[/TD]
[TD]C1tc4[/TD]
[/TR]
[TR]
[TD]1/10/2018 1:55:49[/TD]
[TD]18.35[/TD]
[TD]19.11[/TD]
[TD]20.11[/TD]
[TD]16.35[/TD]
[/TR]
[TR]
[TD]1/10/2018 2:06:18[/TD]
[TD]17.22[/TD]
[TD]21.45[/TD]
[TD]19.44[/TD]
[TD]18.39[/TD]
[/TR]
</tbody>[/TABLE]


****** id="cke_pastebin" style="position: absolute; top: 415px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD]19.11[/TD]
[/TR]
</tbody>[/TABLE]
</body>I have taken all the date/time values in column A, removed duplicates and placed the unique values in column E so each row has its own date and time.

I want a macro that looks at column E and picks up the date and time then,
looks at column A and identifies all rows that match then,
Looks at the Sensor values and Temperatures in that range and copies the Temperatures to the correct Column against that date time then
loops to the next date time value in column E until it is done.

i.e.
1/10/2018 1:15:49 in column E matches A2:A5
Therefore copy C2 (18.35) from sensor c1tc1 to column F (because F1 = c1tc1) and paste it into F2 as 1/10/20181:15:49 is in E2.
C3 to G2
C4 to H2
etc....to AA

Then go pick up E3 1/10/2018 2:06:18 and repeat until no values left.

Hope this makes sense.....if not let me know.

Many thanks again

Jay

 

[bobsan42]

Why not try using Pivot table ?

 

[Not_Excel_lent]

Hi Bobsan42,

When I do a pivot chart I cannot get it to drill down to day. I get as low as month but I need it to be reading by reading as in many readings per day.

When I go the pivot chart it lets me have year, drill down to Qtr, drill down to Month.....Is there a setting somewhere or a way to add the additional fields to get down to reading by reading.