ana1991
10-02-2015, 02:35 AM
Hi guys,
I have a table in excel that is linked to Bloomberg. I did a macro that copy this table from sheet 1 to sheet 2, deleting the links of bloomberg, sorting the data...
I want excel to copy the table of sheet 2 into a table in sheet 3 that has a specific layout. My data is sorted by date and time (each day has different events that occur at different time). The layout of the table in sheet 3 divides each day with a line.
The problem is that each day has a different number of events and time, which means that I need excel to add or delet rows depending on the number of events per day and then insert a line in the last event of each day.
Any help?
Thanks in advance!
Here is a sample of the table:
Country
Event
Date
Time
Period
Median
Average
Actual
Prior
Brazil
XXX
01-06-2015
9:00:00 AM
SEP
0,34
0,59
0,73
0,88
Portugal
XXX
01-06-2015
9:30:00 AM
SEP
0,54
0,53
0,76
0,79
Euro Zone
XX1
01-06-2015
10:00:00 AM
SEP
0,16
0,01
0,96
0,68
Spain
XX2
02-06-2015
9:00:00 AM
AUG
0,25
0,45
0,19
0,19
Turkey
XXX
02-06-2015
9:00:00 AM
SEP
0,66
0,30
0,81
0,99
France
XX5
02-06-2015
9:00:00 AM
SEP
0,69
0,57
0,19
0,83
Germany
XX7
07-06-2015
9:00:00 AM
AUG
0,51
0,94
0,02
0,46
Romania
XX1
07-06-2015
9:00:00 AM
AUG
0,60
0,25
0,22
0,81
Germany
XX3
07-06-2015
9:00:00 AM
SEP
0,66
0,10
0,57
0,53
Portugal
XX8
07-06-2015
9:00:00 AM
JUL
0,10
0,73
0,02
0,11
Euro Zone
XX6
11-06-2015
9:00:00 AM
SEP
0,08
0,75
0,45
0,32
Spain
XXX
12-06-2015
9:00:00 AM
SEP
0,88
0,58
0,14
0,74
Brazil
XX0
13-06-2015
9:00:00 AM
AUG
0,51
0,65
0,29
0,99
Germany
XX8
14-06-2015
9:00:00 AM
SEP
0,16
0,26
0,79
0,96
Euro Zone
XX2
15-06-2015
9:00:00 AM
SEP
0,92
0,79
0,99
0,01
UK
XXX
16-06-2015
9:00:00 AM
SEP
0,39
0,39
0,11
0,48
And I want something like this:
Date
Country
GMT
Indicator
Period
Survey
Actual
Prior
21-Sep
UK
00:01
XXX
Sep
0,325
0,580
0,588
UK
00:01
XXX
Sep
0,247
0,472
0,655
GE
07:00
XX1
Aug
0,670
0,628
0,889
GE
07:00
XX2
Aug
0,553
0,421
0,630
US
15:00
XXX
Aug
0,153
0,183
0,183
US
15:00
XX5
Aug
0,021
0,680
0,493
PO
-
XX7
Jul
0,238
0,623
0,742
SP
-
XX1
Jul
0,940
0,798
0,248 LINE
22-Sep
UK
09:30
XX3
Aug
0,414
0,541
0,005
UK
09:30
XX8
Aug
0,251
0,150
0,664
UK
09:30
XX6
Aug
0,057
0,816
0,538
UK
09:30
XXX
Aug
0,988
0,967
0,336
UK
11:00
XX0
Sep
0,727
0,692
0,793
UK
11:00
XX8
Sep
0,828
0,399
0,592
US
14:00
XX2
Jul
0,788
0,754
0,876
EC
15:00
XXX
Sep A
0,701
0,989
0,336
US
15:00
XX3
Sep
0,265
0,767
0,582 LINE
23-Sep
CH
02:45
XX8
Sep
0,900
0,160
0,539
GE
08:30
XX6
Sep P
0,064
0,682
0,272
GE
08:30
XXX
Sep P
0,558
0,519
0,443
GE
08:30
XX0
Sep P
0,208
0,493
0,747
EC
09:00
XX8
Sep P
0,567
0,355
0,352
EC
09:00
XX2
Sep P
0,409
0,062
0,842
EC
09:00
XXX
Sep P
0,646
0,225
0,541
US
12:00
XXX
18-set
0,565
0,464
0,116
US
14:45
XX0
Sep P
0,917
0,383
0,844 LINE
I have a table in excel that is linked to Bloomberg. I did a macro that copy this table from sheet 1 to sheet 2, deleting the links of bloomberg, sorting the data...
I want excel to copy the table of sheet 2 into a table in sheet 3 that has a specific layout. My data is sorted by date and time (each day has different events that occur at different time). The layout of the table in sheet 3 divides each day with a line.
The problem is that each day has a different number of events and time, which means that I need excel to add or delet rows depending on the number of events per day and then insert a line in the last event of each day.
Any help?
Thanks in advance!
Here is a sample of the table:
Country
Event
Date
Time
Period
Median
Average
Actual
Prior
Brazil
XXX
01-06-2015
9:00:00 AM
SEP
0,34
0,59
0,73
0,88
Portugal
XXX
01-06-2015
9:30:00 AM
SEP
0,54
0,53
0,76
0,79
Euro Zone
XX1
01-06-2015
10:00:00 AM
SEP
0,16
0,01
0,96
0,68
Spain
XX2
02-06-2015
9:00:00 AM
AUG
0,25
0,45
0,19
0,19
Turkey
XXX
02-06-2015
9:00:00 AM
SEP
0,66
0,30
0,81
0,99
France
XX5
02-06-2015
9:00:00 AM
SEP
0,69
0,57
0,19
0,83
Germany
XX7
07-06-2015
9:00:00 AM
AUG
0,51
0,94
0,02
0,46
Romania
XX1
07-06-2015
9:00:00 AM
AUG
0,60
0,25
0,22
0,81
Germany
XX3
07-06-2015
9:00:00 AM
SEP
0,66
0,10
0,57
0,53
Portugal
XX8
07-06-2015
9:00:00 AM
JUL
0,10
0,73
0,02
0,11
Euro Zone
XX6
11-06-2015
9:00:00 AM
SEP
0,08
0,75
0,45
0,32
Spain
XXX
12-06-2015
9:00:00 AM
SEP
0,88
0,58
0,14
0,74
Brazil
XX0
13-06-2015
9:00:00 AM
AUG
0,51
0,65
0,29
0,99
Germany
XX8
14-06-2015
9:00:00 AM
SEP
0,16
0,26
0,79
0,96
Euro Zone
XX2
15-06-2015
9:00:00 AM
SEP
0,92
0,79
0,99
0,01
UK
XXX
16-06-2015
9:00:00 AM
SEP
0,39
0,39
0,11
0,48
And I want something like this:
Date
Country
GMT
Indicator
Period
Survey
Actual
Prior
21-Sep
UK
00:01
XXX
Sep
0,325
0,580
0,588
UK
00:01
XXX
Sep
0,247
0,472
0,655
GE
07:00
XX1
Aug
0,670
0,628
0,889
GE
07:00
XX2
Aug
0,553
0,421
0,630
US
15:00
XXX
Aug
0,153
0,183
0,183
US
15:00
XX5
Aug
0,021
0,680
0,493
PO
-
XX7
Jul
0,238
0,623
0,742
SP
-
XX1
Jul
0,940
0,798
0,248 LINE
22-Sep
UK
09:30
XX3
Aug
0,414
0,541
0,005
UK
09:30
XX8
Aug
0,251
0,150
0,664
UK
09:30
XX6
Aug
0,057
0,816
0,538
UK
09:30
XXX
Aug
0,988
0,967
0,336
UK
11:00
XX0
Sep
0,727
0,692
0,793
UK
11:00
XX8
Sep
0,828
0,399
0,592
US
14:00
XX2
Jul
0,788
0,754
0,876
EC
15:00
XXX
Sep A
0,701
0,989
0,336
US
15:00
XX3
Sep
0,265
0,767
0,582 LINE
23-Sep
CH
02:45
XX8
Sep
0,900
0,160
0,539
GE
08:30
XX6
Sep P
0,064
0,682
0,272
GE
08:30
XXX
Sep P
0,558
0,519
0,443
GE
08:30
XX0
Sep P
0,208
0,493
0,747
EC
09:00
XX8
Sep P
0,567
0,355
0,352
EC
09:00
XX2
Sep P
0,409
0,062
0,842
EC
09:00
XXX
Sep P
0,646
0,225
0,541
US
12:00
XXX
18-set
0,565
0,464
0,116
US
14:45
XX0
Sep P
0,917
0,383
0,844 LINE