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)

 

[Hart]

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)

Is there a way for this function to ignore grades which contain an alpha character? At this point, I have to create a separate sheet with grades with numeric values only.

 

[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.

Is there a way for this function to ignore grades which contain an alpha character? At this point, I have to create a separate sheet with grades with numeric values only.

 

[Kaper]

Simple modification of my formula (at the cost of automatic headers) could be:

=GROUPBY(FILTER(HSTACK(Table1[Last Name],Table1[Parents]),ISNUMBER(Table1[Grade])),FILTER(Table1[Grade],ISNUMBER(Table1[Grade])),ARRAYTOTEXT,0,0)

And my proposition of modification of Peter's formula:

=GROUPBY(CHOOSECOLS(FILTER(Table1,ISNUMBER(Table1[Grade])),1,6),FILTER(Table1[Grade],ISNUMBER(Table1[Grade])),ARRAYTOTEXT,0,0)

again: no headers automatically added to final formula output, but I don't think it is an issue - headers are a constant part

 

[Peter_SSs]

Though if you did want to include the headers (with the same assumptions* as post 4) ..
* If those assumptions are incorrect then modifications would most likely still be possible.

Hart.xlsm
	A	B	C				G							N	O	P	Q	R
1		Last Name	First Name				Parents							Grade		Last Name	Parents	Grade
2		Anderson	Peter				Maria, John							3		Anderson	Maria, John	3, 4
3		Anderson	Anne				Maria, John							3x		Scott	Maria, Jack	5
4		Anderson	Ian				Maria, John							4		Scott	Maria, John	6
5		Brown	Kevin				Anna, Peter							3A
6		Scott	Lily				Maria, Jack							B4
7		Scott	Peter				Maria, Jack							5
8		Scott	Peter				Maria, John							6
9
10
Sheet1

Cell Formulas
Range	Formula
P1:R4	P1	=LET(g,Table1[[#All],[Grade]],f,CHOOSECOLS(FILTER(Table1[#All],ISNUMBER(g)+(g="grade")),1,6,13),GROUPBY(TAKE(f,,2),TAKE(f,,-1),ARRAYTOTEXT,3,0))
Dynamic array formulas.