Create list of last(family) names when parents have children in more than 1 class

[Hart]

MASTER CLASS LIST Nov 12 2024.xlsm
	B	C	D	E	F	G	H	I			L	M	N
1	Last Name	First Name	DOB	Gender	Phone	Parents	PHN	Address			Name	PEN	Grade
Families

I would like to create a sheet containing the last (family) name of all students in a school as well the grade. The family name will appear in ColumnA and the Grade in ColumnB. Additionally, a family may have more than 1 student in a class (twins or triplets) and if so, the resulting grade cell would also include the number of children in the grade, for example 5(2x) twins or 5(3x) triplets etc. The information is contained in a table (above). The determining factor when there are multiple identical last names is the "contact field" contents - i.e. same parent/guardian. The table can be sorted in any sequence but will likely be by last name, contact and grade. Ideally, after 55 (or so) family names and grades, the macro will then start populating at row 1, columns D and E, Then Columns G and H. After G and H are filled, back to column a&B with a page break. Continue until the table is processed.

Resulting output sheet called "families":
ColumnA ColumnB
Name Grade

Anderson 3
Brown 3 x 2, 4, 5 (for 2 students in grade 3) one in grade 4, one in grade 5
etc.....
Many thanks in advance,
Hart

 

[Kaper]

As you are using (also) Microsoft 365 - have you been thinking of a new function GroupBy ? Something like:

=GROUPBY(HSTACK(Table1[[#All],[Last Name]],Table1[[#All],[Parents]]),Table1[[#All],[Grade]],ARRAYTOTEXT,3,0)

Well, it does not list Anderson as 3x2; 4 , just simple 3; 3; 4 but may be it would be enough? If not lambda function could be used instead of ARRAYTOTEXT
PS. in your case the output can be with commas not semicolons - it depends on system local settings.

I used HSTACK inside, because there are 2 Scott families in my school

Zeszyt1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Last Name	First Name	DOB	Gender	Phone	Parents	PHN	Address	Column1	Column2	Name	PEN	Grade		Last Name	Parents	Grade
2	Anderson	Peter				Maria, John							3		Anderson	Maria, John	3; 3; 4
3	Anderson	Anne				Maria, John							3		Brown	Anna, Peter	3
4	Anderson	Ian				Maria, John							4		Scott	Maria, Jack	4; 5
5	Brown	Kevin				Anna, Peter							3		Scott	Maria, John	6
6	Scott	Lily				Maria, Jack							4
7	Scott	Peter				Maria, Jack							5
8	Scott	Peter				Maria, John							6
9
Arkusz1

Cell Formulas
Range	Formula
O1:Q5	O1	=GROUPBY(HSTACK(Table1[[#All],[Last Name]],Table1[[#All],[Parents]]),Table1[[#All],[Grade]],ARRAYTOTEXT,3,0)
Dynamic array formulas.

 

[Hart]

As you are using (also) Microsoft 365 - have you been thinking of a new function GroupBy ? Something like:

=GROUPBY(HSTACK(Table1[[#All],[Last Name]],Table1[[#All],[Parents]]),Table1[[#All],[Grade]],ARRAYTOTEXT,3,0)

Well, it does not list Anderson as 3x2; 4 , just simple 3; 3; 4 but may be it would be enough? If not lambda function could be used instead of ARRAYTOTEXT
PS. in your case the output can be with commas not semicolons - it depends on system local settings.

I used HSTACK inside, because there are 2 Scott families in my school

Zeszyt1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	Last Name	First Name	DOB	Gender	Phone	Parents	PHN	Address	Column1	Column2	Name	PEN	Grade		Last Name	Parents	Grade
2	Anderson	Peter				Maria, John							3		Anderson	Maria, John	3; 3; 4
3	Anderson	Anne				Maria, John							3		Brown	Anna, Peter	3
4	Anderson	Ian				Maria, John							4		Scott	Maria, Jack	4; 5
5	Brown	Kevin				Anna, Peter							3		Scott	Maria, John	6
6	Scott	Lily				Maria, Jack							4
7	Scott	Peter				Maria, Jack							5
8	Scott	Peter				Maria, John							6
9
Arkusz1

Cell Formulas
Range	Formula
O1:Q5	O1	=GROUPBY(HSTACK(Table1[[#All],[Last Name]],Table1[[#All],[Parents]]),Table1[[#All],[Grade]],ARRAYTOTEXT,3,0)
Dynamic array formulas.

I haven't even looked at the groupby function but will now. This is perfect. Thanks!

 

[Peter_SSs]

If you are not going to be moving the columns around then you could compact that a bit.
I am assuming 'Last Name' is the 1st column in the table and 'Parents' is the 6th column. Adjust the 1, 6 in the formula if that is not correct.

=GROUPBY(CHOOSECOLS(Table1[#All],1,6),Table1[[#All],[Grade]],ARRAYTOTEXT,3,0)