Rearranging Data in a Table

[rwmill9716]

I have stock trading results in Columns AX through BD (hundreds of rows). I need formula for Columns BE through BI that will let me rearrange the data as shown. Essentially, I'm taking data that's presented as 1 row per symbol and converting it to 2 rows per symbol then rearranging the data as shown into those two rows: the 1/2 position trade results (date and profit) are moved to single columns.

Thanks for your help,

Ric

Excel 2010
	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI
20	Symbol	Date Purchased	Price	Date 1st half sold	Profit	Date 2nd half sold	Profit	Symbol	Date Purchased	Price	Date 1/2 Position Sold	Profit
21	CC	01/05/17	$20.82	1/10/2017	2.25	1/10/2017	$2.45	CC	1/5/2017	$20.82	1/10/2017	$2.25
22	THO	01/12/17	$100.29	1/13/2017	$2.50	1/19/2016	$2.00				1/10/2017	$2.45
23	SF	01/12/17	$48.69	1/13/2017	$3.00	1/13/2017	$3.00	THO	1/12/2017	$100.29	1/13/2017	$2.50
24	PAA	01/19/17	$30.53	1/24/2017	$1.75	1/25/2017	$1.50				1/19/2017	$2.00
25	WDC	01/19/17	$72.01	1/20/2017	$1.25	1/20/2017	$1.25	SF	1/12/2017	$48.69	1/13/2017	$3.00
26	SCHN	01/25/17	$23.80	1/31/2017	$0.55	2/1/2017	$0.55				1/13/2017	$3.00
2016 (2)

 

[sunny102]

Just copy Symbol column into new sheet, make a space between each symbol using macro and then copy it back to col BE and then use vlookup formula to fetch the rest of the columns for that symbol.

I have stock trading results in Columns AX through BD (hundreds of rows). I need formula for Columns BE through BI that will let me rearrange the data as shown. Essentially, I'm taking data that's presented as 1 row per symbol and converting it to 2 rows per symbol then rearranging the data as shown into those two rows: the 1/2 position trade results (date and profit) are moved to single columns.

Thanks for your help,

Ric

Excel 2010

	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH	BI
CC
THO
SF

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]20[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Symbol[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Date Purchased[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Price[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Date 1st half sold[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Profit[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Date 2nd half sold[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Profit[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Symbol[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Date Purchased[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Price[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Date 1/2 Position Sold[/TD]
[TD="bgcolor: #D9D9D9, align: center"]Profit[/TD]

[TD="align: center"]21[/TD]
[TD="bgcolor: #C0C0C0"]CC[/TD]
[TD="bgcolor: #C0C0C0"] 01/05/17[/TD]
[TD="bgcolor: #C0C0C0, align: right"]$20.82 [/TD]
[TD="bgcolor: #C0C0C0, align: right"]1/10/2017[/TD]
[TD="align: right"]2.25[/TD]
[TD="align: right"]1/10/2017[/TD]
[TD="align: right"]$2.45 [/TD]

[TD="align: right"]1/5/2017[/TD]
[TD="align: right"]$20.82 [/TD]
[TD="align: right"]1/10/2017[/TD]
[TD="align: right"]$2.25 [/TD]

[TD="align: center"]22[/TD]
[TD="bgcolor: #C0C0C0"]THO[/TD]
[TD="bgcolor: #C0C0C0"] 01/12/17[/TD]
[TD="bgcolor: #C0C0C0, align: right"]$100.29 [/TD]
[TD="bgcolor: #C0C0C0, align: right"]1/13/2017[/TD]
[TD="align: right"]$2.50 [/TD]
[TD="align: right"]1/19/2016[/TD]
[TD="align: right"]$2.00 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1/10/2017[/TD]
[TD="align: right"]$2.45 [/TD]

[TD="align: center"]23[/TD]
[TD="bgcolor: #C0C0C0"]SF[/TD]
[TD="bgcolor: #C0C0C0"] 01/12/17[/TD]
[TD="bgcolor: #C0C0C0, align: right"]$48.69 [/TD]
[TD="bgcolor: #C0C0C0, align: right"]1/13/2017[/TD]
[TD="align: right"]$3.00 [/TD]
[TD="align: right"]1/13/2017[/TD]
[TD="align: right"]$3.00 [/TD]

[TD="align: right"]1/12/2017[/TD]
[TD="align: right"]$100.29 [/TD]
[TD="align: right"]1/13/2017[/TD]
[TD="align: right"]$2.50 [/TD]

[TD="align: center"]24[/TD]
[TD="bgcolor: #C0C0C0"]PAA[/TD]
[TD="bgcolor: #C0C0C0"] 01/19/17[/TD]
[TD="bgcolor: #C0C0C0, align: right"]$30.53 [/TD]
[TD="bgcolor: #C0C0C0, align: right"]1/24/2017[/TD]
[TD="align: right"]$1.75 [/TD]
[TD="align: right"]1/25/2017[/TD]
[TD="align: right"]$1.50 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1/19/2017[/TD]
[TD="align: right"]$2.00 [/TD]

[TD="align: center"]25[/TD]
[TD="bgcolor: #C0C0C0"]WDC[/TD]
[TD="bgcolor: #C0C0C0"] 01/19/17[/TD]
[TD="bgcolor: #C0C0C0, align: right"]$72.01 [/TD]
[TD="bgcolor: #C0C0C0, align: right"]1/20/2017[/TD]
[TD="align: right"]$1.25 [/TD]
[TD="align: right"]1/20/2017[/TD]
[TD="align: right"]$1.25 [/TD]

[TD="align: right"]1/12/2017[/TD]
[TD="align: right"]$48.69 [/TD]
[TD="align: right"]1/13/2017[/TD]
[TD="align: right"]$3.00 [/TD]

[TD="align: center"]26[/TD]
[TD="bgcolor: #C0C0C0"]SCHN[/TD]
[TD="bgcolor: #C0C0C0"] 01/25/17[/TD]
[TD="bgcolor: #C0C0C0, align: right"]$23.80 [/TD]
[TD="bgcolor: #C0C0C0, align: right"]1/31/2017[/TD]
[TD="align: right"]$0.55 [/TD]
[TD="align: right"]2/1/2017[/TD]
[TD="align: right"]$0.55 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1/13/2017[/TD]
[TD="align: right"]$3.00 [/TD]

</tbody>

2016 (2)​

 

[rwmill9716]

Thanks, Sunny,

What would the macro look like that would loop through 1000 of these rows? I like the Vlookup approach. Since the same symbol will occur multiple times, I will have to confine each vlookup formula to two rows and copy it down the list.

Ric