Excel Macro to split sheets by managers direct/indirect employees

[tpadd8]

I am attempting to write a macro in Excel that is a bit complex (at least for me) to assist at work. I have a list of employees and their salary ranges and I want to be able to break the sheet up by managers and their direct/indirect reports. For example: John Smith is the VP and has 5 direct reports, I want the macro to grab the 5 ranges of those direct reports, but also the ranges of the direct reports (if any) of those 5 people. So John Smith would receive the salary ranges of all the people in this example, but Sam Jones would only receive his 2 direct reports and the 3 direct reports of Jane Doe as she is a direct report of Sam. Hopefully that somewhat makes sense! It can also delete any duplicate ranges (if career level, grade, zone, structure are the same for more than one person). One more, if it could please save the file under the name of the manager - thank you!

 

[jdellasala]

How about just pulling the main Data table and splitting it up from there. Clean it up as needed and sort by Manager, then create copies of it for each Manager referencing that table, filter for one manager and then load those tables.

Data

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Location", Int64.Type}, {"Employee", Int64.Type}, {"Employee Name", type text}, {"W", type text}, {"Manager Name", type text}, {"Manager", Int64.Type}, {"# of Direct", Int64.Type}, {"# of Indirect Career Level Grade", Int64.Type}, {"Column1", type text}, {"Column2", Int64.Type}, {"Zone", type text}, {"Structure", type text}, {"202.3 Min", Int64.Type}, {"2023 MRP", Int64.Type}, {"2023 Max", Int64.Type}}),
    #"Replaced Value" = Table.ReplaceValue(#"Changed Type","Doe, Ja ne","Doe, Jane",Replacer.ReplaceText,{"Manager Name"}),
    #"Sorted Rows" = Table.Sort(#"Replaced Value",{{"Manager Name", Order.Ascending}})
in
    #"Sorted Rows"

JaneDoe

let
    Source = Data,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Manager Name] = "Doe, Jane"))
in
    #"Filtered Rows"

SamJones

let
    Source = Data,
    #"Filtered Rows" = Table.SelectRows(Source, each ([Manager Name] = "Jones, Sam"))
in
    #"Filtered Rows"

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Data
2	Location	Employee	Employee Name	W	Manager Name	Manager	# of Direct	# of Indirect Career Level Grade	Column1	Column2	Zone	Structure	202.3 Min	2023 MRP	2023 Max
3	90	350	Test, Test	Smith, John	Doe, Ja ne	356			Sl	1	Zone3	A	$1.00	$11.00	$21.00
4	90	351	None, None	Smith, John	Doe, Jane	356			Sl	1	Zone4	A	$2.00	$12.00	522
5	90	352	Person, Person	Smith, John	Smith, John	440			S2	2	Zone7	A	$3.00	$13.00	$23.00
6	90	353	Human, Human	Sm ith, John	Sm ith, John	440	6		M2	6	Zone7	A	$4.00	$14.00	$24.00
7	90	354	Page, Cedric	Sm ith, John	Jones, Sam	356			B2	5	Zone7	A	$5.00	$15.00	$25.00
8	90	355	Employee, Employee	Smith, John	Sm ith, John	440	1		M.2	6	Zone7	A	$6.00	$16.00	526
9	90	356	Doe, Jane	Sm ith, John	Jones, Sam	359	3		Ml	5	Zone7	A	$7.00	$17.00	$27.00
10	90	357	Worker, Worker	Smith, John	Smith, John	440			M2	6	Zone7	A	$8.00	$18.00	$28.00
11	90	358	Nobody, Nobody	Smith, John	Doe, Ja ne	356			Sl	1	Zones	A	$9.00	$19.00	$29.00
12	90	359	Jones, Sam	Sm ith, John	Smith, John	440	2	3	M.2	6	Zone7	A	$10.00	$20.00	$30.00
13
14	JaneDoe
15	Location	Employee	Employee Name	W	Manager Name	Manager	# of Direct	# of Indirect Career Level Grade	Column1	Column2	Zone	Structure	202.3 Min	2023 MRP	2023 Max
16	90	358	Nobody, Nobody	Smith, John	Doe, Jane	356			Sl	1	Zones	A	9	19	29
17	90	351	None, None	Smith, John	Doe, Jane	356			Sl	1	Zone4	A	2	12	522
18	90	350	Test, Test	Smith, John	Doe, Jane	356			Sl	1	Zone3	A	1	11	21
19
20	SamJones
21	Location	Employee	Employee Name	W	Manager Name	Manager	# of Direct	# of Indirect Career Level Grade	Column1	Column2	Zone	Structure	202.3 Min	2023 MRP	2023 Max
22	90	356	Doe, Jane	Sm ith, John	Jones, Sam	359	3		Ml	5	Zone7	A	7	17	27
23	90	354	Page, Cedric	Sm ith, John	Jones, Sam	356			B2	5	Zone7	A	5	15	25
Sheet1

The name above each Query and Table is their name. NOTE: Do NOT put the resulting tables ( JaneDoe, SamJones ) or ANYTHING under the Data Table. Best to put each in their own Worksheet. Now, as the Data Table grows, a simple Refresh All will update the appropriate tables. No VB code required.

Pretty nifty, eh? BTW, please use XL2BB. I used Power Query to import the Data Table from a Screenshot, and assume the correction I did in that table was caused by that, but I let it go to show that you can fix the data in the Data Table and those fixes will flow to the subsequent tables.