VBA: Creating table & Filling correct data

[PerttiPP]

Hi all and thanks in advance,
I have a list of services and resources what services are using. The list is updating every week and I still want to follow the trends and see how is every service progressing. I have created VBA macro to create new sheet for every time I want to analyze data set and it automatically copies list to the new sheet.

An example below. The list on the left side is my source data which is in the main sheet. I would like to get the data to table form which is on the right side. Then I would have only one row for each service and I would see which of those five resources services are having. Is it possible to create with some lookup function or what kind of VBA code I should create? It should have some kind of "finding" function to fill data to correct place.

 

[severynm]

No need for VBA here - you have two different options.

You can add your data to a pivot table and add your data to rows and columns (and apply conditional formatting) to get the following table.

Or, since you are using M365, you can use the amazing new dynamic array formulas. To make your table, three formulas do the trick. One to generate a unique list of row headers, one for the column headers, and one for the values.

MrExcel.xlsm
	P	Q	R	S	T	U
2		1	2	3	4	5
3	A	1	1	1	1	1
4	B	1	0	1	0	0
5	C	1	0	0	1	1
Sheet1

Cell Formulas
Range	Formula
Q2:U2	Q2	=TRANSPOSE(UNIQUE(Table3[resource]))
P3:P5	P3	=UNIQUE(Table3[service])
Q3:U5	Q3	=COUNTIFS(Table3[service],P3#,Table3[resource],Q2#)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Q3:U5	Other Type	Icon set		NO

However, it also would be possible to procedurally create the table in VBA too, if it's something you definitiely require.

 

[PerttiPP]

No need for VBA here - you have two different options.

You can add your data to a pivot table and add your data to rows and columns (and apply conditional formatting) to get the following table.

View attachment 51359


Or, since you are using M365, you can use the amazing new dynamic array formulas. To make your table, three formulas do the trick. One to generate a unique list of row headers, one for the column headers, and one for the values.

MrExcel.xlsm
	P	Q	R	S	T	U
2		1	2	3	4	5
3	A	1	1	1	1	1
4	B	1	0	1	0	0
5	C	1	0	0	1	1
Sheet1

Cell Formulas
Range	Formula
Q2:U2	Q2	=TRANSPOSE(UNIQUE(Table3[resource]))
P3:P5	P3	=UNIQUE(Table3[service])
Q3:U5	Q3	=COUNTIFS(Table3[service],P3#,Table3[resource],Q2#)
Dynamic array formulas.

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
Q3:U5	Other Type	Icon set		NO

However, it also would be possible to procedurally create the table in VBA too, if it's something you definitiely require.

Thanks for answer. Hope this will help. I actually have to create VBA code for this because the source data is quite big and I anyway need to automate this process.