Merge Data from multiple sheets into one by transposing and counting(VBA)

[coolguyvarun01]

Hi Experts,

Got a requirement from my manager that have to merge data of multiple sheets into one by transposing the dates and generating columns based on different values and then showing total count in the respective column and all through the click of a button.

Kindly help.

Attached is the workbook with 4 data sheets and 1 summary sheet, Summary sheet is supposed to be generated by clicking the button in the sheet.

Kindly help.

Thank You in Advance

OutPut

problem statement.xlsm
	A	B	C	D	E	F
1
2
3	Date	P	A	CL	PL	WFH
4	01-05-2021	40	0	0	0	0
5	02-05-2021	36	4	0	0	0
6	03-05-2021	32	4	4	0	0
7	04-05-2021	36	4	0	0	0
8	05-05-2021	24	12	0	4	0
9	06-05-2021	40	0	0	0	0
10	07-05-2021	28	8	4	0	0
11	08-05-2021	36	4	0	0	0
12	09-05-2021	32	4	0	0	4
13	10-05-2021	32	4	0	0	4
Required Output

Sample Data

problem statement.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Name	Age	Target	Joining Date	01-05-2021	02-05-2021	03-05-2021	04-05-2021	05-05-2021	06-05-2021	07-05-2021	08-05-2021	09-05-2021	10-05-2021
2	aaaa	24	1013		P	P	P	P	P	P	P	P	P	P
3	bbbb	43	2809		P	P	P	P	A	P	A	A	P	A
4	cccc	37	3946		P	P	P	P	PL	P	CL	P	WFH	WFH
5	dddd	36	2917		P	P	A	P	P	P	P	P	P	P
6	eeee	21	2053		P	P	P	P	A	P	P	P	P	P
7	ffff	38	3723		P	P	P	P	P	P	P	P	P	P
8	gggg	37	1630		P	A	CL	A	A	P	A	P	A	P
9	hhhh	25	1781		P	P	P	P	P	P	P	P	P	P
10	iiii	32	3146		P	P	P	P	P	P	P	P	P	P
11	jjjj	40	2596		P	P	P	P	P	P	P	P	P	P
San Christiano

 

[Alex Blakenburg]

I would recommend using Power Query and it would be good to know how you get the data to this point.
If your source system is creating 4 csv files which you are manually copying into this workbook, it would be better to get Power Query import the source files directly.
Can you please clarify how the data is getting into this workbook ?
If they are csv files how are they named, and what tells you which file is for which city.

 

[Alex Blakenburg]

If you are interested in a Power Query (PQ) solution and you are happy to have the summary report in a different workbook below is one option.
Note: If you want it to be in the same workbook we would need each sheet to have the data area converted to a table, which one of the reasons I wanted to know how you get to that point and as a follow on how you refresh the data. A better way would be to read in all the initial source files if that is how you get the data in the first place.

Here are the steps to implement it as is with the summary in a different workbook
1) Create a new workbook
2) Create an Excel Table in that workbook and call it Parameters
3) Enter the full path and file name for the Workbook with your source data in it.

20210509 PQ Merge Sheets and transpose Combined v02.xlsx
	A	B
1	Parameter	Value
2	FullFileName	C:\PQ\20210509 PQ Merge Sheets and transpose v02.xlsx
Required Output

4) now go into Power Query via Data > Get Data > From Other Sources > Blank Query
5) In the white Name box on the right hand side change it from Query1 to fnGetParameter
6) On the home tab select Advanced Editor (3rd from the right)
7) Select All in the editor and replace what you see with this.

(ParameterName as text) =>
let
    ParamSource = Excel.CurrentWorkbook(){[Name="Parameters"]}[Content],
    ParamRow = Table.SelectRows(ParamSource, each ([Parameter] = ParameterName )),
    Value=
        if Table.IsEmpty(ParamRow)=true
        then null
        else Record.Field(ParamRow{0},"Value")
in
    Value

8) In the Query Pane on the left, right click in the grey area and select Neq Query > Other Sources > Blank Query
9) On the home tab select Advanced Editor (3rd from the right)
10) Select All in the editor and replace what you see with this.

let
    filepath = fnGetParameter("FullFileName"),
    Source = Excel.Workbook(File.Contents(filepath), null, true),
    #"Filtered Rows" = Table.SelectRows(Source, each not Text.StartsWith([Name], "Required")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Item", "Kind", "Hidden"}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Removed Columns", "Data", {"Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10", "Column11", "Column12", "Column13", "Column14"}, {"Data.Column1", "Data.Column2", "Data.Column3", "Data.Column4", "Data.Column5", "Data.Column6", "Data.Column7", "Data.Column8", "Data.Column9", "Data.Column10", "Data.Column11", "Data.Column12", "Data.Column13", "Data.Column14"}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Expanded Data", [PromoteAllScalars=true]),
    #"Renamed Columns City" = Table.RenameColumns(#"Promoted Headers",{{List.First(Table.ColumnNames(#"Promoted Headers")), "City"}}),
    #"Filtered Rows1" = Table.SelectRows(#"Renamed Columns City", each ([Age] <> "Age")),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Filtered Rows1", {"Joining Date", "Target", "Age", "Name", "City"}, "Attribute", "Value"),
    #"Removed Other Columns" = Table.SelectColumns(#"Unpivoted Other Columns",{"Attribute", "Value"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Other Columns",{{"Attribute", type date}, {"Value", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Count", each 1),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Count", Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Changed Type1", List.Distinct(#"Changed Type1"[Value]), "Value", "Count", List.Sum),
    #"Renamed Columns Date" = Table.RenameColumns(#"Pivoted Column",{{"Attribute", "Date"}}),
    #"Replaced Value nulls" = Table.ReplaceValue(#"Renamed Columns Date",null,0,Replacer.ReplaceValue,Table.ColumnNames(#"Renamed Columns Date")),
    #"Reordered Columns" = Table.ReorderColumns(#"Replaced Value nulls",{"Date", "P", "A", "CL", "PL", "WFH"})
in
    #"Reordered Columns"

11) In the white Name box on the right hand side, give it a meaningful name
12) Home > Close and Load to Select Table and then either leave it as New Worksheet or select Existing Worksheet and give it a location.

Let me know what you decide and if this works for you.

 

[coolguyvarun01]

Hi Alex,

Thanks for your reply.
The source of the data is different regions sending the xlsx files at the end of every month.
The client specifically asked for VBA script for this.


Regards

 

[Alex Blakenburg]

Thanks for the update. I will leave a VBA solution for someone else.