A complicated Data Clean Up Problem

VGaleoto · Apr 7, 2020

Hi all,

I am new to this message board and am slightly above novice at excel after learning just how much the program can do. However, I have come across a very common request. I am trying to take all of the staggered data in each month and move it all to one line as long as it matches the columns of "Counterparty" Strike/Price" and "Trade Date". I have tried multiple things, i don't feel like indexmatch is capable of this. I have had some trouble thinking of the proper VBA code to achieve this. This is just a picture of a portion of a very large file. I would ultimately like to create a macro that will be able to do this for the whole worksheet. Is anyone able to offer any guidance?

alansidman · Apr 7, 2020

Would you repost the sample data using the XL2BB function on this site so that we don't have try and recreate (read type) your data.

VGaleoto · Apr 7, 2020

I'm sorry if this isn't right. This is my first time with this so please let me know if this is what you needed. I very much appreciate the help.

Excel VBA Unwind Test.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

2

Trade ID

Trade Date

Underlying

Strategy

Strike/Price

Counterparty

Apr-20

May-20

Jun-20

Jul-20

Aug-20

Sep-20

Oct-20

Nov-20

Dec-20

3

184735

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

1,050,000

4

184736

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

600,000

5

184737

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

(1,050,000)

6

184739

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

(600,000)

7

184786

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

1,085,000

8

184787

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

620,000

9

184788

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

(1,085,000)

10

184790

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

(620,000)

11

184837

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

1,050,000

12

184838

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

600,000

13

184839

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

(1,050,000)

14

184841

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

(600,000)

15

184888

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

1,085,000

16

184889

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

620,000

17

184890

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

(1,085,000)

18

184892

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

(620,000)

19

184937

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

1,085,000

20

184938

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

620,000

21

184939

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

(1,085,000)

22

184941

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

(620,000)

23

184986

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

1,050,000

24

184987

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

600,000

25

184988

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

(1,050,000)

26

184990

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

(600,000)

27

185033

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

1,085,000

28

185034

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

620,000

29

185035

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

(1,085,000)

30

185037

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

(620,000)

31

185080

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

1,050,000

32

185081

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

600,000

33

185082

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

(1,050,000)

34

185084

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

(600,000)

35

185122

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

1,085,000

36

185123

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

620,000

37

185124

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

(1,085,000)

38

185126

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

(620,000)

Sheet1

CA_Punit · Apr 7, 2020

Book1

A

B

C

D

E

F

G

H

I

J

K

L

1

2

3

Sum of Value

Attribute

4

Column1

Strategy

04/01/2020

05/01/2020

06/01/2020

07/01/2020

08/01/2020

09/01/2020

10/01/2020

11/01/2020

12/01/2020

Grand Total

5

4/16/2018 2.8 Bank A

Long Puts

1650000

1705000

1650000

1705000

1650000

1705000

1650000

1705000

15125000

6

4/16/2018 3 Bank A

Short Calls (collars)

-1650000

-1705000

-1650000

-1705000

-1650000

-1705000

-1650000

-1705000

-15125000

7

Grand Total

0

8

9

10

11

Sheet3

VGaleoto · Apr 7, 2020

Yes the result is exactly like what I need. However, it can't be in pivot because I need to do more analysis with this information. I also need the columns to remain as they are if possible.

alansidman · Apr 7, 2020

Is this what you are looking for?

Book1

A

B

C

D

E

F

G

H

1

Trade ID

Trade Date

Underlying

Strategy

Strike/Price

Counterparty

Attribute

Value

2

184735

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

4/1/2020

1050000

3

184736

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

4/1/2020

600000

4

184737

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

4/1/2020

-1050000

5

184739

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

4/1/2020

-600000

6

184786

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

5/1/2020

1085000

7

184787

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

5/1/2020

620000

8

184788

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

5/1/2020

-1085000

9

184790

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

5/1/2020

-620000

10

184837

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

6/1/2020

1050000

11

184838

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

6/1/2020

600000

12

184839

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

6/1/2020

-1050000

13

184841

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

6/1/2020

-600000

14

184888

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

7/1/2020

1085000

15

184889

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

7/1/2020

620000

16

184890

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

7/1/2020

-1085000

17

184892

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

7/1/2020

-620000

18

184937

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

8/1/2020

1085000

19

184938

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

8/1/2020

620000

20

184939

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

8/1/2020

-1085000

21

184941

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

8/1/2020

-620000

22

184986

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

9/1/2020

1050000

23

184987

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

9/1/2020

600000

24

184988

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

9/1/2020

-1050000

25

184990

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

9/1/2020

-600000

26

185033

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

10/1/2020

1085000

27

185034

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

10/1/2020

620000

28

185035

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

10/1/2020

-1085000

29

185037

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

10/1/2020

-620000

30

185080

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

11/1/2020

1050000

31

185081

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

11/1/2020

600000

32

185082

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

11/1/2020

-1050000

33

185084

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

11/1/2020

-600000

34

185122

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

12/1/2020

1085000

35

185123

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

12/1/2020

620000

36

185124

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

12/1/2020

-1085000

37

185126

4/16/2018

NYMEX Henry Hub (LD)

Short Calls (collars)

3

Bank A

12/1/2020

-620000

Sheet3

CA_Punit · Apr 7, 2020

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

1

2

3

Sum of Value

Attribute

4

Trade Date

Underlying

Strategy

Strike/Price

Counterparty

04/01/2020

05/01/2020

06/01/2020

07/01/2020

08/01/2020

09/01/2020

10/01/2020

11/01/2020

12/01/2020

Grand Total

5

4/16/2018

NYMEX Henry Hub (LD)

Long Puts

2.8

Bank A

1650000

1705000

1650000

1705000

1650000

1705000

1650000

1705000

15125000

6

Short Calls (collars)

3

Bank A

-1650000

-1705000

-1650000

-1705000

-1650000

-1705000

-1650000

-1705000

-15125000

7

Grand Total

0

8

9

10

11

12

13

14

15

Sheet5

VGaleoto · Apr 7, 2020

alansidman said:
Is this what you are looking for?

Book1
A B C D E F G H
1 Trade ID Trade Date Underlying Strategy Strike/Price Counterparty Attribute Value
2 184735 4/16/2018 NYMEX Henry Hub (LD) Long Puts 2.8 Bank A 4/1/2020 1050000
3 184736 4/16/2018 NYMEX Henry Hub (LD) Long Puts 2.8 Bank A 4/1/2020 600000
4 184737 4/16/2018 NYMEX Henry Hub (LD) Short Calls (collars) 3 Bank A 4/1/2020 -1050000
5 184739 4/16/2018 NYMEX Henry Hub (LD) Short Calls (collars) 3 Bank A 4/1/2020 -600000
6 184786 4/16/2018 NYMEX Henry Hub (LD) Long Puts 2.8 Bank A 5/1/2020 1085000
7 184787 4/16/2018 NYMEX Henry Hub (LD) Long Puts 2.8 Bank A 5/1/2020 620000
8 184788 4/16/2018 NYMEX Henry Hub (LD) Short Calls (collars) 3 Bank A 5/1/2020 -1085000
9 184790 4/16/2018 NYMEX Henry Hub (LD) Short Calls (collars) 3 Bank A 5/1/2020 -620000
10 184837 4/16/2018 NYMEX Henry Hub (LD) Long Puts 2.8 Bank A 6/1/2020 1050000
11 184838 4/16/2018 NYMEX Henry Hub (LD) Long Puts 2.8 Bank A 6/1/2020 600000
12 184839 4/16/2018 NYMEX Henry Hub (LD) Short Calls (collars) 3 Bank A 6/1/2020 -1050000
13 184841 4/16/2018 NYMEX Henry Hub (LD) Short Calls (collars) 3 Bank A 6/1/2020 -600000
14 184888 4/16/2018 NYMEX Henry Hub (LD) Long Puts 2.8 Bank A 7/1/2020 1085000
15 184889 4/16/2018 NYMEX Henry Hub (LD) Long Puts 2.8 Bank A 7/1/2020 620000
16 184890 4/16/2018 NYMEX Henry Hub (LD) Short Calls (collars) 3 Bank A 7/1/2020 -1085000
17 184892 4/16/2018 NYMEX Henry Hub (LD) Short Calls (collars) 3 Bank A 7/1/2020 -620000
18 184937 4/16/2018 NYMEX Henry Hub (LD) Long Puts 2.8 Bank A 8/1/2020 1085000
19 184938 4/16/2018 NYMEX Henry Hub (LD) Long Puts 2.8 Bank A 8/1/2020 620000
20 184939 4/16/2018 NYMEX Henry Hub (LD) Short Calls (collars) 3 Bank A 8/1/2020 -1085000
21 184941 4/16/2018 NYMEX Henry Hub (LD) Short Calls (collars) 3 Bank A 8/1/2020 -620000
22 184986 4/16/2018 NYMEX Henry Hub (LD) Long Puts 2.8 Bank A 9/1/2020 1050000
23 184987 4/16/2018 NYMEX Henry Hub (LD) Long Puts 2.8 Bank A 9/1/2020 600000
24 184988 4/16/2018 NYMEX Henry Hub (LD) Short Calls (collars) 3 Bank A 9/1/2020 -1050000
25 184990 4/16/2018 NYMEX Henry Hub (LD) Short Calls (collars) 3 Bank A 9/1/2020 -600000
26 185033 4/16/2018 NYMEX Henry Hub (LD) Long Puts 2.8 Bank A 10/1/2020 1085000
27 185034 4/16/2018 NYMEX Henry Hub (LD) Long Puts 2.8 Bank A 10/1/2020 620000
28 185035 4/16/2018 NYMEX Henry Hub (LD) Short Calls (collars) 3 Bank A 10/1/2020 -1085000
29 185037 4/16/2018 NYMEX Henry Hub (LD) Short Calls (collars) 3 Bank A 10/1/2020 -620000
30 185080 4/16/2018 NYMEX Henry Hub (LD) Long Puts 2.8 Bank A 11/1/2020 1050000
31 185081 4/16/2018 NYMEX Henry Hub (LD) Long Puts 2.8 Bank A 11/1/2020 600000
32 185082 4/16/2018 NYMEX Henry Hub (LD) Short Calls (collars) 3 Bank A 11/1/2020 -1050000
33 185084 4/16/2018 NYMEX Henry Hub (LD) Short Calls (collars) 3 Bank A 11/1/2020 -600000
34 185122 4/16/2018 NYMEX Henry Hub (LD) Long Puts 2.8 Bank A 12/1/2020 1085000
35 185123 4/16/2018 NYMEX Henry Hub (LD) Long Puts 2.8 Bank A 12/1/2020 620000
36 185124 4/16/2018 NYMEX Henry Hub (LD) Short Calls (collars) 3 Bank A 12/1/2020 -1085000
37 185126 4/16/2018 NYMEX Henry Hub (LD) Short Calls (collars) 3 Bank A 12/1/2020 -620000
Sheet3

I need the format I used to remain constant if that is possible. I do a lot of analysis that is easiest to read in the format I supplied. Is it possible to keep the format I gave and still achieve the goal?

CA_Punit · Apr 7, 2020

1. Create a Excel Table of The Data
2. Load it into Power Qwery.
3. Unpivot the Data.
4. Load it into Excel
5. Use Pivot table

alansidman · Apr 7, 2020

I need the format I used to remain constant if that is possible.

Show us an example.

A complicated Data Clean Up Problem

VGaleoto

New Member

Attachments

Excel Facts

alansidman

Well-known Member

VGaleoto

New Member

CA_Punit

Well-known Member

VGaleoto

New Member

alansidman

Well-known Member

CA_Punit

Well-known Member

VGaleoto

New Member

CA_Punit

Well-known Member

alansidman

Well-known Member

Similar threads

Forum statistics

Share this page

A complicated Data Clean Up Problem

New Member

Attachments

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Well-known Member

New Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock