Transpose column data to one row

[bigroo]

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),"")

Thanks

 

[tonyyy]

Describing how your data is structured is great; posting sample data of before and after would be even better.

Cheers,

tonyyy

 

[bigroo]

Thanks Tonyy for the reply!
I don't see how to post a sample, can you direct me?

Thanks

 

[Peter_SSs]

I don't see how to post a sample, can you direct me?

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.

 

[bigroo]

Thanks Peter, I appreciate your help!!

 

[strooman]

I don't see how to post a sample, can you direct me?

Perhaps this link will fit your needs to do that:
https://theenemy.dk/table/

 

[bigroo]

Thanks, so here is my sample!

Excel 2016 (Windows) 64 bit

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​	M​	N​	O​	P​	Q​	R​	S​	T​	U​	V​	W​
5​	Before
6​	RCDATE​	RCROUT​	RCCOMP​	RCSTOP​	RCLIFT​	RCVEHL	RCDRVR	RCDAY​	RCSTRT​	RCARVL​	RCSTPT​	RCTYPE​	RCLAND​	RCTCKT​	RCMATL​	RCUOM​	RCUNIT​	LF Trips​	LF trip 1​	LF trip 2​	LF trip 3​	LF trip 4​	LF trip 5​
7​	20180716​	A					FRED	1​				LF						13.43​
8​	20180716​	A					FRED	1​				LF						8.19​
9​	20180716​	A					FRED	1​				DT						0​
10​	20180716​	A					FRED	1​				LF						0​
11​	20180716​	B					JOHN	1​				LF						15.25​
12​	20180716​	B					JOHN	1​				LF						15.55​
13​	20180716​	B					JOHN	1​				LF						15.25​
14​
15​
16​	After
17​	RCDATE​	RCROUT​	RCCOMP​	RCSTOP​	RCLIFT​	RCVEHL	RCDRVR	RCDAY​	RCSTRT​	RCARVL​	RCSTPT​	RCTYPE​	RCLAND​	RCTCKT​	RCMATL​	RCUOM​	RCUNIT​	LF Trips​	LF trip 1​	LF trip 2​	LF trip 3​	LF trip 4​	LF trip 5​
18​	20180716​	A					FRED	1​				LF						13.43​	13.43​	8.19​	0​
19​	20180716​	A					FRED	1​				LF						8.19​
20​	20180716​	A					FRED	1​				DT						0​
21​	20180716​	A					FRED	1​				LF						0​
22​	20180716​	B					JOHN	1​				LF						15.25​	15.25​	15.55​	15.25​
23​	20180716​	B					JOHN	1​				LF						15.55​
24​	20180716​	B					JOHN	1​				LF						15.25​

Sheet: Sheet2

 

[Peter_SSs]

Try this, copied across and down.

Excel Workbook
	A	G	L	R	S	T	U	V	W
6	RCDATE	RCDRVR	RCTYPE	LF Trips	LF trip 1	LF trip 2	LF trip 3	LF trip 4	LF trip 5
7	20180716	FRED	LF	13.43	13.43	8.19	0
8	20180716	FRED	LF	8.19
9	20180716	FRED	DT	0
10	20180716	FRED	LF	0
11	20180716	JOHN	LF	15.25	15.25	15.55	15.25
12	20180716	JOHN	LF	15.55
13	20180716	JOHN	LF	15.25
Transpose

 

[bigroo]

Thank you Peter, this is wonderful, works as requested!

 

[Peter_SSs]

Thank you Peter, this is wonderful, works as requested!

Glad it worked for you. Thanks for letting us know.