Copy subsequent row to same row based on multiple condition

[yokeloonlo01]

Hi,
Need some help if can do this in VBA or Pivot. Unable to figure out the way on this.
I have the following Data: from Column A to K for Equity Option Trading:

No	Date/Time	Strategy	Method	Qty	Pos Effect	Equity	Expiry Date	Strike Price	Transaction Type	Price
1	7/6/2018 21:45	VERTICAL	SELL	-10	TO OPEN	GOOGL	13-Jul-18	1100	PUT	2.86
			BUY	10	TO OPEN	GOOGL	13-Jul-18	1097.5	PUT	2.64
2	7/6/2018 21:30	VERTICAL	SELL	-10	TO OPEN	TSLA	13-Jul-18	280	PUT	2.64
			BUY	10	TO OPEN	TSLA	13-Jul-18	277.5	PUT	2.34
3	7/5/2018 21:33	VERTICAL	SELL	-5	TO OPEN	EL	20-Jul-18	135	PUT	0.52
			BUY	5	TO OPEN	EL	20-Jul-18	130	PUT	0.27
4	7/5/2018 21:33	VERTICAL	SELL	-5	TO OPEN	EL	20-Jul-18	135	PUT	0.52
			BUY	5	TO OPEN	EL	20-Jul-18	130	PUT	0.27
5	7/4/2018 0:07	VERTICAL	BUY	10	TO CLOSE	TSLA	13-Jul-18	385	CALL	0.33
			SELL	-10	TO CLOSE	TSLA	13-Jul-18	400	CALL	0.18
6	7/3/2018 21:38	VERTICAL	SELL	-2	TO OPEN	GOOGL	6-Jul-18	1117.5	PUT	2.26
			BUY	2	TO OPEN	GOOGL	6-Jul-18	1115	PUT	1.99
7	7/3/2018 21:38	VERTICAL	SELL	-8	TO OPEN	GOOGL	6-Jul-18	1117.5	PUT	2.1
			BUY	8	TO OPEN	GOOGL	6-Jul-18	1115	PUT	1.83
8	6/28/18 21:36:54	VERTICAL	SELL	-10	TO OPEN	MCD	6-Jul-18	152.5	PUT	0.56
			BUY	10	TO OPEN	MCD	6-Jul-18	150	PUT	0.34
9	6/26/18 21:46:59	VERTICAL	SELL	-10	TO OPEN	SPY	27-Jun-18	268	PUT	0.27
			BUY	10	TO OPEN	SPY	27-Jun-18	265	PUT	0.08
10	6/26/18 21:45:56	VERTICAL	BUY	10	TO CLOSE	SPY	27-Jun-18	268	PUT	0.27
			SELL	-10	TO CLOSE	SPY	27-Jun-18	265	PUT	0.07
11	6/26/18 21:45:15	VERTICAL	SELL	-10	TO OPEN	SPY	27-Jun-18	268	PUT	0.25
			BUY	10	TO OPEN	SPY	27-Jun-18	265	PUT	0.08
12	6/25/18 22:37:00	VERTICAL	SELL	-10	TO OPEN	GOOGL	29-Jun-18	1100	PUT	3.08
			BUY	10	TO OPEN	GOOGL	29-Jun-18	1097.5	PUT	2.85
13	6/21/18 21:37:02	VERT ROLL	SELL	-10	TO OPEN	TSLA	13-Jul-18	385	CALL	12.24
			BUY	10	TO OPEN	TSLA	13-Jul-18	400	CALL	8.27
			BUY	10	TO CLOSE	TSLA	6-Jul-18	370	CALL	15.04
			SELL	-10	TO CLOSE	TSLA	6-Jul-18	380	CALL	11.26
14	6/20/18 21:51:43	VERTICAL	SELL	-9	TO OPEN	BIDU	22-Jun-18	250	PUT	0.45
			BUY	9	TO OPEN	BIDU	22-Jun-18	247.5	PUT	0.29
15	6/20/18 21:51:43	VERTICAL	SELL	-1	TO OPEN	BIDU	22-Jun-18	250	PUT	0.45
			BUY	1	TO OPEN	BIDU	22-Jun-18	247.5	PUT	0.29

<tbody style="box-sizing: border-box;">
</tbody>

You can see that based on Column A (No), there were 15 set of trades and each set of trades can have more than one line, example:
For No 1 - 7/6/2018 21:45
We have a trade that involve Sell and Buy for GOOGL equity option that happen in a single trade, i,e., row 2 is Sell trade and row 3 is a buy trade. Note that cell C2 has no date (same as per Cell E2 and so on)
The requirement are:
1) Whether to use combination of VBA or otherwise, for combination of different value of B (Date/Time), and Column C (Vertical or Vert Roll), Column G (Equity), Column H (Expiry Date), Column D (Sell/Buy), cut and paste the Buy row to Column L to V, so we have the result like this:
Example: For 7/6/2018 @ 21.45pm, for GOOGL with expiry @ 13 Jul 2018, copy the row 2 to the right
(Scroll to the right further)
A B C D E F G H I J K L M N O P

No

Exec Time

Spread

Side

Qty

Pos Effect

Symbol

Expiry

Strike

Type

Price

1

7/6/2018 21:45

VERTICAL

SELL

-10

TO OPEN

GOOGL

13-Jul-18

1100

PUT

2.86

BUY

10

TO OPEN

GOOGL

13-Jul-18

1097.5

PUT

2.64

<tbody style="box-sizing: border-box;">
</tbody>

So the row 3 goes to row 2 and paste to L2 to V2

2) Note that in above data, no 13 has 4 rows (Sell- Buy- Buy-Sell) on the same date, so just arrange by Sell-Sell (A-K) and Buy-Buy (L-V) side by side based on added criteria (Column C - Vert RolL)

Any help is appreciated.
Thanks
Lo

 

[sheetspread]

Bring the second up with a formula, copy down, paste as values:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	No	Date/Time	Strategy	Method	Qty	Pos Effect	Equity	Expiry Date	Strike Price	Transaction	Price
2										Type
3	1	7/6/2018 21:45	VERTICAL	SELL	-10	TO OPEN	GOOGL	13-Jul-18	1100	PUT	2.86		BUY	10	TO OPEN	GOOGL	43294	1097.5	PUT	2.64
4				BUY	10	TO OPEN	GOOGL	13-Jul-18	1098	PUT	2.64		SELL	-10	TO OPEN	TSLA	43294	280	PUT	2.64
5	2	7/6/2018 21:30	VERTICAL	SELL	-10	TO OPEN	TSLA	13-Jul-18	280	PUT	2.64		BUY	10	TO OPEN	TSLA	43294	277.5	PUT	2.34
6				BUY	10	TO OPEN	TSLA	13-Jul-18	277.5	PUT	2.34		SELL	-5	TO OPEN	EL	43301	135	PUT	0.52
7	3	7/5/2018 21:33	VERTICAL	SELL	-5	TO OPEN	EL	20-Jul-18	135	PUT	0.52		BUY	5	TO OPEN	EL	43301	130	PUT	0.27
8				BUY	5	TO OPEN	EL	20-Jul-18	130	PUT	0.27		SELL	-5	TO OPEN	EL	43301	135	PUT	0.52
9	4	7/5/2018 21:33	VERTICAL	SELL	-5	TO OPEN	EL	20-Jul-18	135	PUT	0.52		BUY	5	TO OPEN	EL	43301	130	PUT	0.27
10				BUY	5	TO OPEN	EL	20-Jul-18	130	PUT	0.27		BUY	10	TO CLOSE	TSLA	43294	385	CALL	0.33
11	5	7/4/2018 0:07	VERTICAL	BUY	10	TO CLOSE	TSLA	13-Jul-18	385	CALL	0.33		SELL	-10	TO CLOSE	TSLA	43294	400	CALL	0.18
Sheet11

Cell Formulas
Range	Formula
M3	=D4

Then highlight column A, F5(Goto)-Special-Blanks and delete the rows:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	No	Date/Time	Strategy	Method	Qty	Pos Effect	Equity	Expiry Date	Strike Price	Transaction	Price		Method	Qty	Pos Effect	Equity	Expiry Date	Strike Price	Transaction	Price
2	1	7/6/2018 21:45	VERTICAL	SELL	-10	TO OPEN	GOOGL	13-Jul-18	1100	PUT	2.86		BUY	10	TO OPEN	GOOGL	43294	1097.5	PUT	2.64
3	2	7/6/2018 21:30	VERTICAL	SELL	-10	TO OPEN	TSLA	13-Jul-18	280	PUT	2.64		BUY	10	TO OPEN	TSLA	43294	277.5	PUT	2.34
4	3	7/5/2018 21:33	VERTICAL	SELL	-5	TO OPEN	EL	20-Jul-18	135	PUT	0.52		BUY	5	TO OPEN	EL	43301	130	PUT	0.27
5	4	7/5/2018 21:33	VERTICAL	SELL	-5	TO OPEN	EL	20-Jul-18	135	PUT	0.52		BUY	5	TO OPEN	EL	43301	130	PUT	0.27
6	5	7/4/2018 0:07	VERTICAL	BUY	10	TO CLOSE	TSLA	13-Jul-18	385	CALL	0.33		SELL	-10	TO CLOSE	TSLA	43294	400	CALL	0.18
7	6	7/3/2018 21:38	VERTICAL	SELL	-2	TO OPEN	GOOGL	6-Jul-18	1117.5	PUT	2.26		BUY	2	TO OPEN	GOOGL	43287	1115	PUT	1.99
8	7	7/3/2018 21:38	VERTICAL	SELL	-8	TO OPEN	GOOGL	6-Jul-18	1117.5	PUT	2.1		BUY	8	TO OPEN	GOOGL	43287	1115	PUT	1.83
9	8	6/28/2018 21:36	VERTICAL	SELL	-10	TO OPEN	MCD	6-Jul-18	152.5	PUT	0.56		BUY	10	TO OPEN	MCD	43287	150	PUT	0.34
10	9	6/26/2018 21:46	VERTICAL	SELL	-10	TO OPEN	SPY	27-Jun-18	268	PUT	0.27		BUY	10	TO OPEN	SPY	43278	265	PUT	0.08
11	10	6/26/2018 21:45	VERTICAL	BUY	10	TO CLOSE	SPY	27-Jun-18	268	PUT	0.27		SELL	-10	TO CLOSE	SPY	43278	265	PUT	0.07
12	11	6/26/2018 21:45	VERTICAL	SELL	-10	TO OPEN	SPY	27-Jun-18	268	PUT	0.25		BUY	10	TO OPEN	SPY	43278	265	PUT	0.08
13	12	6/25/2018 22:37	VERTICAL	SELL	-10	TO OPEN	GOOGL	29-Jun-18	1100	PUT	3.08		BUY	10	TO OPEN	GOOGL	43280	1097.5	PUT	2.85
14	13	6/21/2018 21:37	VERT ROLL	SELL	-10	TO OPEN	TSLA	13-Jul-18	385	CALL	12.2		BUY	10	TO OPEN	TSLA	43294	400	CALL	8.27
15	14	6/20/2018 21:51	VERTICAL	SELL	-9	TO OPEN	BIDU	22-Jun-18	250	PUT	0.45		BUY	9	TO OPEN	BIDU	43273	247.5	PUT	0.29
16	15	6/20/2018 21:51	VERTICAL	SELL	-1	TO OPEN	BIDU	22-Jun-18	250	PUT	0.45		BUY	1	TO OPEN	BIDU	43273	247.5	PUT	0.29
Sheet11

(might have to unmerge some rows/cells)

edit: missed the multiple lines part, hang on........

 

[sheetspread]

If you highlight A3:C33 or wherever your data ends, F5(Goto)-Special-Blanks, press the = key then the up arrow then ctrl-enter, you'll get:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1	No	Date/Time	Strategy	Method	Qty	Pos Effect	Equity	Expiry Date	Strike Price	TransactionType	Price
2	1	7/6/2018 21:45	VERTICAL	SELL	-10	TO OPEN	GOOGL	13-Jul-18	1100	PUT	2.86
3	1	7/6/2018 21:45	VERTICAL	BUY	10	TO OPEN	GOOGL	13-Jul-18	1097.5	PUT	2.64
4	2	7/6/2018 21:30	VERTICAL	SELL	-10	TO OPEN	TSLA	13-Jul-18	280	PUT	2.64
5	2	7/6/2018 21:30	VERTICAL	BUY	10	TO OPEN	TSLA	13-Jul-18	277.5	PUT	2.34
6	3	7/5/2018 21:33	VERTICAL	SELL	-5	TO OPEN	EL	20-Jul-18	135	PUT	0.52
7	3	7/5/2018 21:33	VERTICAL	BUY	5	TO OPEN	EL	20-Jul-18	130	PUT	0.27
8	4	7/5/2018 21:33	VERTICAL	SELL	-5	TO OPEN	EL	20-Jul-18	135	PUT	0.52
9	4	7/5/2018 21:33	VERTICAL	BUY	5	TO OPEN	EL	20-Jul-18	130	PUT	0.27
10	5	7/4/2018 0:07	VERTICAL	BUY	10	TO CLOSE	TSLA	13-Jul-18	385	CALL	0.33
11	5	7/4/2018 0:07	VERTICAL	SELL	-10	TO CLOSE	TSLA	13-Jul-18	400	CALL	0.18
12	6	7/3/2018 21:38	VERTICAL	SELL	-2	TO OPEN	GOOGL	6-Jul-18	1117.5	PUT	2.26
13	6	7/3/2018 21:38	VERTICAL	BUY	2	TO OPEN	GOOGL	6-Jul-18	1115	PUT	1.99
14	7	7/3/2018 21:38	VERTICAL	SELL	-8	TO OPEN	GOOGL	6-Jul-18	1117.5	PUT	2.1
15	7	7/3/2018 21:38	VERTICAL	BUY	8	TO OPEN	GOOGL	6-Jul-18	1115	PUT	1.83
16	8	6/28/2018 21:36	VERTICAL	SELL	-10	TO OPEN	MCD	6-Jul-18	152.5	PUT	0.56
17	8	6/28/2018 21:36	VERTICAL	BUY	10	TO OPEN	MCD	6-Jul-18	150	PUT	0.34
18	9	6/26/2018 21:46	VERTICAL	SELL	-10	TO OPEN	SPY	27-Jun-18	268	PUT	0.27
19	9	6/26/2018 21:46	VERTICAL	BUY	10	TO OPEN	SPY	27-Jun-18	265	PUT	0.08
20	10	6/26/2018 21:45	VERTICAL	BUY	10	TO CLOSE	SPY	27-Jun-18	268	PUT	0.27
21	10	6/26/2018 21:45	VERTICAL	SELL	-10	TO CLOSE	SPY	27-Jun-18	265	PUT	0.07
22	11	6/26/2018 21:45	VERTICAL	SELL	-10	TO OPEN	SPY	27-Jun-18	268	PUT	0.25
23	11	6/26/2018 21:45	VERTICAL	BUY	10	TO OPEN	SPY	27-Jun-18	265	PUT	0.08
24	12	6/25/2018 22:37	VERTICAL	SELL	-10	TO OPEN	GOOGL	29-Jun-18	1100	PUT	3.08
25	12	6/25/2018 22:37	VERTICAL	BUY	10	TO OPEN	GOOGL	29-Jun-18	1097.5	PUT	2.85
26	13	6/21/2018 21:37	VERT ROLL	SELL	-10	TO OPEN	TSLA	13-Jul-18	385	CALL	12.2
27	13	6/21/2018 21:37	VERT ROLL	BUY	10	TO OPEN	TSLA	13-Jul-18	400	CALL	8.27
28	13	6/21/2018 21:37	VERT ROLL	BUY	10	TO CLOSE	TSLA	6-Jul-18	370	CALL	15
29	13	6/21/2018 21:37	VERT ROLL	SELL	-10	TO CLOSE	TSLA	6-Jul-18	380	CALL	11.3
30	14	6/20/2018 21:51	VERTICAL	SELL	-9	TO OPEN	BIDU	22-Jun-18	250	PUT	0.45
31	14	6/20/2018 21:51	VERTICAL	BUY	9	TO OPEN	BIDU	22-Jun-18	247.5	PUT	0.29
32	15	6/20/2018 21:51	VERTICAL	SELL	-1	TO OPEN	BIDU	22-Jun-18	250	PUT	0.45
33	15	6/20/2018 21:51	VERTICAL	BUY	1	TO OPEN	BIDU	22-Jun-18	247.5	PUT	0.29
Sheet11

then you can set two pivot tables next to each other and filter buys on one, sells on the other