Macro for transforming the data

[Ganja]

Hello!
I would truly appreciate if someone could help me out with macro code for the task described below. I need to repeat this process daily and it would save me quite some time if I would have a macro in hand.

Many thanks in advance!

Description:
· there are three sheets in the workbook: time_series,lookup_table and code
· all three sheets – headings are in the first row
Tasks:
1. Insert two columns at the beginning (column A and B) in the sheet time_series
a. New column B of the sheet time_series should have values from column C of the lookup_table sheet based on matching concatenated value of original column A and B of the sheet time_series (when two columns are inserted it will be columns C and D) andcolumn K of the lookup_table sheet

i. for example – cell B2 of the time_series sheet should be the value of the following formula, including the blank cell option

=IF(ISBLANK(C2), INDEX(lookup_table!$A$2:$L$501, MATCH(time_series!D2, lookup_table!$K$2:$K$501, 0), 3), INDEX(lookup_table!$A$2:$L$501, MATCH(CONCATENATE(time_series!C2,", ", time_series!D2), lookup_table!$K$2:$K$501, 0), 3))
b. New column A of the sheet time_series should have a value from column B from the sheet code based on matching the values of new column B of the sheet time_series with column A of code sheet.
i. for example – cell A2 of the time_series sheet should be the value of this formula
=INDEX(code!$A$2:$D$350, MATCH(time_series!B2, code!$A$2:$A$350, 0), 2)
2. Create a new worksheet that would be based on data from time_series sheet. Column B of time_seriessheet has repetitive values and the data in each column starting from column G should be summed up based on unique values in column B. Currently I am doing it with pivot table.

 

[mole999]

you can record the steps to do each action with the macro recorder, and if necessary post that code in between code tags here to help with refining

 

[vmjan02]

@Ganja

Here is the code
I have not tested it.

You can give it a try.

Sub test()
'Inset 2 columns in sheet time_series
Sheets("time_series").Select
Range("a:b").EntireColumn.Insert

'Vlookup in coulmn B
With Worksheets("time_series")
    With .Range("B2:B" & .Range("C" & .Rows.Count).End(xlUp).Row)  'You can "c" as it will print the formula till the last raw
        .Formula = "=IF(ISBLANK(C2),INDEX(lookup_table!$A$2:$L$501,MATCH(time_series!D2,lookup_table!$K$2:$K$501,0),3),INDEX(lookup_table!$A$2:$L$501,MATCH(CONCATENATE(time_series!C2,"", "",time_series!D2),lookup_table!$K$2:$K$501,0),3))"
        '.Value = .Value
     End With
End With

'Vlookup in coulmn A
With Worksheets("time_series")
    With .Range("A2:A" & .Range("C" & .Rows.Count).End(xlUp).Row)  'You can "c" as it will print the formula till the last raw
        .Formula = "=INDEX(code!$A$2:$D$350,MATCH(time_series!B2,code!$A$2:$A$350,0),2)"
        '.Value = .Value
     End With
End With
End Sub

 

[Ganja]

It works like a charm! I will play with creating a pivot table with macro. Thank you so much!

 

[vmjan02]

Welcome.