Transpose column data to one row

B

bigroo

Board Regular
Joined
Jun 22, 2008
Messages
74
I am looking for a formula to do this if possible, but VBA will work if not available as a formula.

So, in column A there are dates, column G has a driver name, column L has either LF or DT two show either landfill or downtime status. Over in column R I have weights of the landfill load, but only if LF is present in column L. This data can cover 3 to 5 rows for one date and one driver. So I want to transpose the weights for each landfill trip from column R to the first row under columns S, T, U, V & W for what ever number of landfill tips there is.

I hope this makes sense!

I have tried this formula
=If(and(($G8=$G7),($L8="LF")),transpose($R8),"")
Click to expand...

Thanks
 
Last edited:

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Describing how your data is structured is great; posting sample data of before and after would be even better.

Cheers,

tonyyy
 
Upvote 0
bigroo said:
I don't see how to post a sample, can you direct me?
Click to expand...
Read Tony's signature block at the bottom of his post. Or mine for that matter. :)

Edit: Actually, on checking, I note that Tony's link to Excel jeanie is broken so better stick to my signature block.
 
Last edited:
Upvote 0
Thanks, so here is my sample!

Excel 2016 (Windows) 64 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][th]
G​
[/th][th]
H​
[/th][th]
I​
[/th][th]
J​
[/th][th]
K​
[/th][th]
L​
[/th][th]
M​
[/th][th]
N​
[/th][th]
O​
[/th][th]
P​
[/th][th]
Q​
[/th][th]
R​
[/th][th]
S​
[/th][th]
T​
[/th][th]
U​
[/th][th]
V​
[/th][th]
W​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]Before[/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][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td=bgcolor:#5B9BD5]
RCDATE​
[/td][td=bgcolor:#5B9BD5]
RCROUT​
[/td][td=bgcolor:#5B9BD5]
RCCOMP​
[/td][td=bgcolor:#5B9BD5]
RCSTOP​
[/td][td=bgcolor:#5B9BD5]
RCLIFT​
[/td][td=bgcolor:#5B9BD5]RCVEHL[/td][td=bgcolor:#5B9BD5]RCDRVR[/td][td=bgcolor:#5B9BD5]
RCDAY​
[/td][td=bgcolor:#5B9BD5]
RCSTRT​
[/td][td=bgcolor:#5B9BD5]
RCARVL​
[/td][td=bgcolor:#5B9BD5]
RCSTPT​
[/td][td=bgcolor:#5B9BD5]
RCTYPE​
[/td][td=bgcolor:#5B9BD5]
RCLAND​
[/td][td=bgcolor:#5B9BD5]
RCTCKT​
[/td][td=bgcolor:#5B9BD5]
RCMATL​
[/td][td=bgcolor:#5B9BD5]
RCUOM​
[/td][td=bgcolor:#5B9BD5]
RCUNIT​
[/td][td=bgcolor:#FFD966]
LF Trips​
[/td][td=bgcolor:#FFD966]
LF trip 1​
[/td][td=bgcolor:#FFD966]
LF trip 2​
[/td][td=bgcolor:#FFD966]
LF trip 3​
[/td][td=bgcolor:#FFD966]
LF trip 4​
[/td][td=bgcolor:#FFD966]
LF trip 5​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td=bgcolor:#DDEBF7]
20180716​
[/td][td=bgcolor:#DDEBF7]A[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]FRED[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]LF[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
13.43​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]
20180716​
[/td][td]A[/td][td][/td][td][/td][td][/td][td][/td][td]FRED[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]LF[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
8.19​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td=bgcolor:#DDEBF7]
20180716​
[/td][td=bgcolor:#DDEBF7]A[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]FRED[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]DT[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
0​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]
20180716​
[/td][td]A[/td][td][/td][td][/td][td][/td][td][/td][td]FRED[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]LF[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
0​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td=bgcolor:#DDEBF7]
20180716​
[/td][td=bgcolor:#DDEBF7]B[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]JOHN[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]LF[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
15.25​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td]
20180716​
[/td][td]B[/td][td][/td][td][/td][td][/td][td][/td][td]JOHN[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]LF[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
15.55​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td=bgcolor:#DDEBF7]
20180716​
[/td][td=bgcolor:#DDEBF7]B[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]JOHN[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]LF[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
15.25​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14​
[/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][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15​
[/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][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16​
[/td][td]After[/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][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
17​
[/td][td=bgcolor:#5B9BD5]
RCDATE​
[/td][td=bgcolor:#5B9BD5]
RCROUT​
[/td][td=bgcolor:#5B9BD5]
RCCOMP​
[/td][td=bgcolor:#5B9BD5]
RCSTOP​
[/td][td=bgcolor:#5B9BD5]
RCLIFT​
[/td][td=bgcolor:#5B9BD5]RCVEHL[/td][td=bgcolor:#5B9BD5]RCDRVR[/td][td=bgcolor:#5B9BD5]
RCDAY​
[/td][td=bgcolor:#5B9BD5]
RCSTRT​
[/td][td=bgcolor:#5B9BD5]
RCARVL​
[/td][td=bgcolor:#5B9BD5]
RCSTPT​
[/td][td=bgcolor:#5B9BD5]
RCTYPE​
[/td][td=bgcolor:#5B9BD5]
RCLAND​
[/td][td=bgcolor:#5B9BD5]
RCTCKT​
[/td][td=bgcolor:#5B9BD5]
RCMATL​
[/td][td=bgcolor:#5B9BD5]
RCUOM​
[/td][td=bgcolor:#5B9BD5]
RCUNIT​
[/td][td=bgcolor:#FFD966]
LF Trips​
[/td][td=bgcolor:#FFD966]
LF trip 1​
[/td][td=bgcolor:#FFD966]
LF trip 2​
[/td][td=bgcolor:#FFD966]
LF trip 3​
[/td][td=bgcolor:#FFD966]
LF trip 4​
[/td][td=bgcolor:#FFD966]
LF trip 5​
[/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
18​
[/td][td=bgcolor:#DDEBF7]
20180716​
[/td][td=bgcolor:#DDEBF7]A[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]FRED[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]LF[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
13.43​
[/td][td=bgcolor:#DDEBF7]
13.43​
[/td][td=bgcolor:#DDEBF7]
8.19​
[/td][td=bgcolor:#DDEBF7]
0​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
19​
[/td][td]
20180716​
[/td][td]A[/td][td][/td][td][/td][td][/td][td][/td][td]FRED[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]LF[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
8.19​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
20​
[/td][td=bgcolor:#DDEBF7]
20180716​
[/td][td=bgcolor:#DDEBF7]A[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]FRED[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]DT[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
0​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
21​
[/td][td]
20180716​
[/td][td]A[/td][td][/td][td][/td][td][/td][td][/td][td]FRED[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]LF[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
0​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
22​
[/td][td=bgcolor:#DDEBF7]
20180716​
[/td][td=bgcolor:#DDEBF7]B[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]JOHN[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]LF[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
15.25​
[/td][td=bgcolor:#DDEBF7]
15.25​
[/td][td=bgcolor:#DDEBF7]
15.55​
[/td][td=bgcolor:#DDEBF7]
15.25​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
23​
[/td][td]
20180716​
[/td][td]B[/td][td][/td][td][/td][td][/td][td][/td][td]JOHN[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td]LF[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]
15.55​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
24​
[/td][td=bgcolor:#DDEBF7]
20180716​
[/td][td=bgcolor:#DDEBF7]B[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]JOHN[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]LF[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
15.25​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet2[/td][/tr][/table]
 
Upvote 0
Try this, copied across and down.

Excel Workbook
AGLRSTUVW
6RCDATERCDRVRRCTYPELF TripsLF trip 1LF trip 2LF trip 3LF trip 4LF trip 5
720180716FREDLF13.4313.438.190
820180716FREDLF8.19
920180716FREDDT0
1020180716FREDLF0
1120180716JOHNLF15.2515.2515.5515.25
1220180716JOHNLF15.55
1320180716JOHNLF15.25
Transpose
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Identifying Groups of Data in a Column, Transposing Groups to a Row
Replies
3
Views
264
KRice
K
L
  • Question Question
Prevent Pagebreak from hidden columns and rows, combine visible columns to one page wide
Replies
1
Views
250
bobsan42
bobsan42
Q
  • Question Question
Macro needed to insert 4 rows in table, copy data from above, copy data from another sheet and paste special (transpose values) to those new rows...re
Replies
1
Views
402
sijpie
sijpie
T
  • Solved
Transposing(?) data from row into multiple shorter columns
Replies
5
Views
327
arun_eagertolearn
A
R
  • Solved
Adding multiple columns with criteria in one column
Replies
7
Views
431
SanjayGMusafir
SanjayGMusafir

Forum statistics

Threads
1,224,819
Messages
6,181,153
Members
453,021
Latest member
Justyna P

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