Linking multiple texts to a single text

[mugi]

Hey all, Am trying to find a formula to show School Fees Billing, Collection and Outstanding balance
The students are registered with parent account. I need to create parent account(names) and should be linked with students account( in most cases more than one).

Then the students, eg
student A -attends Class1 and Boarding pays 400
student B- attends Class 1 and Day Schooling pays 700

can anyone help me create such a worksheet?
Best regards!

[KOKOSEK]

It looks like that it can be easily solved but without more details we can't help you.
Can you attach any samples file, please?
Remove fragile data, put some random/fake names on there and describe expected result in there.
In banner at top you have got info about attachments.

[mugi]

I have attached a worksheet.
I need a formula linking PARENT'S NAME (one) to PUPIL'S NAME (multiple pupils).

And also a formula linking CLASS to PROGRAMME to result specific TOTAL DUES.

[KOKOSEK]

Question:

Excel 2016 (Windows) 32 bit

	A	B
1	PARENT'S NAME	PUPIL'S NAME
2	AKZOL BOB	CISSE NOBLE
3		AKANYA TIMOTHY
4		AKAN REBECCA
5	APOLLO EDWARDS	TUNGI EDWARD
6		ABAZI PROMISE
7		AINO PEACE

Sheet: FESS COLLECTION

is that means that:
Akzol Bob is a parent for: Cisse & Akanya & Akan
Apollo Edwards is a parent for: Tungi & Abazi & Aino?

[Glenn Kennedy]

Not clear at all. What do you expect to see? Where do you expect to see it?
Please amend your sheet to show manually calculated results.

[mugi]

Yes please!
Its exactly that.The names in column 1 (eg AKZOL BOB is a father to CISSE,AKANYA and AKAN)are parents to the kids in column 2!
So i need to link the parents to their kids.

[mugi]

Not clear at all. What do you expect to see? Where do you expect to see it?
Please amend your sheet to show manually calculated results.

I tried to explain as best as i could.
In column 1 The parent(eg AKZOL BOB has 3 kids CISSE,AKANYA and AKAN)
I need a formula to show to link BOB (A2 to B2,B3 and B4)
APOLLO(A5 to B5,B6 and B7)

Is my query understandle now?

[KOKOSEK]

Could you put expected layout of results on sheet2?
To be honest I still do not understand what kind of data rebuild you expect?
Everything what I've done so far it is only assign parent name into group of kids (it will be easier to grouping or dealing with data in general)

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J	K
1	PARENT'S NAME	PUPIL'S NAME	CLASS	PROGRAMME	PREVIOUS DEBT	TOTAL DUES	TOTAL PYT	BALANCE	DATE EDITED		helper
2	AKZOL BOB	CISSE NOBLE	CLASS 4	DAY SCHOLAR		UGX 80,000	UGX 100,000	UGX 20,000	15/9/2019		AKZOL BOB
3		AKANYA TIMOTHY	CLASS 5	BOARDING	UGX 380,000	UGX 240,000		UGX 620,000			AKZOL BOB
4		AKAN REBECCA	CLASS 1	DAY SCHOLAR		UGX 50,000		UGX 50,000			AKZOL BOB
5	APOLLO EDWARDS	TUNGI EDWARD	CLASS 3	DAY SCHOLAR	UGX 10,000	UGX 80,000		UGX 90,000			APOLLO EDWARDS
6		ABAZI PROMISE	CLASS 2	DAY SCHOLAR		UGX 70,000		UGX 70,000			APOLLO EDWARDS
7		AINO PEACE	CLASS 7	BOARDING		UGX 315,000		UGX 315,000			APOLLO EDWARDS
8	AGABA LINUS	AKANA TIMOTHY	CLASS 4	BOARDING		UGX 240,000	UGX 390,000	UGX 150,000	15/9/2019		AGABA LINUS
9		ATUHA PRINCESS	CLASS 2	BOARDING		UGX 230,000		UGX 230,000			AGABA LINUS
10	AGABA EDISON	AINEI NICOLE	CLASS 6	DAY SCHOLAR		UGX 100,000	UGX 30,000	UGX 70,000			AGABA EDISON
11	AINO TONNY	AINI ANGEL	CLASS 2	DAY SCHOLAR		UGX 70,000		UGX 70,000			AINO TONNY
12	AMBER RICHARD	ANAZI RECO	CLASS 5	DAY SCHOLAR	UGX 120,000	UGX 90,000	UGX 120,000	UGX 90,000	10/09/2019		AMBER RICHARD
13	ADIDA COLLINE	ATWINE ELIZABETH	CLASS 1	BOARDING		UGX 220,000		UGX 220,000			ADIDA COLLINE
14	ABLE CHARLES	KWASA MAURICE	CLASS 7	BOARDING		UGX 315,000		UGX 315,000			ABLE CHARLES
15		AINOMUGASHO BLESSED	CLASS 2	BOARDING		UGX 230,000		UGX 230,000			ABLE CHARLES
16		NAHURIRA FAITH	CLASS 3	DAY SCHOLAR		UGX 80,000		UGX 80,000			ABLE CHARLES
17		NAYEBARE ELLY	CLASS 3	BOARDING		UGX 230,000	UGX 400,000	UGX 170,000	26/10/2019		ABLE CHARLES
18	GEOFREY GUY	MWEBESA ARTHUR	CLASS 6	BOARDING		UGX 250,000		UGX 250,000			GEOFREY GUY
19		ARINDA PROSSY	CLASS6	DAY SCHOLAR		UGX 100,000		UGX 100,000			GEOFREY GUY
20		JOSHUA	CLASS 7	DAY SCHOLAR		UGX 100,000	UGX 450,000	UGX 350,000	26/10/2019		GEOFREY GUY

	K
1	helper
2	=IF(A2<>"",A2,K1)

Sheet: FESS COLLECTION

[Glenn Kennedy]

You have not told us what you expect to see and where you expect to see it. So a guess. I inserted a column after the pupil's name:

=IF(A2="",C1,A2)

copied down.

[mugi]

Could you put expected layout of results on sheet2?
To be honest I still do not understand what kind of data rebuild you expect?
Everything what I've done so far it is only assign parent name into group of kids (it will be easier to grouping or dealing with data in general)

Excel 2016 (Windows) 32 bit

	A	B	C	D	E	F	G	H	I	J	K
1	PARENT'S NAME	PUPIL'S NAME	CLASS	PROGRAMME	PREVIOUS DEBT	TOTAL DUES	TOTAL PYT	BALANCE	DATE EDITED		helper
2	AKZOL BOB	CISSE NOBLE	CLASS 4	DAY SCHOLAR		UGX 80,000	UGX 100,000	UGX 20,000	15/9/2019		AKZOL BOB
3		AKANYA TIMOTHY	CLASS 5	BOARDING	UGX 380,000	UGX 240,000		UGX 620,000			AKZOL BOB
4		AKAN REBECCA	CLASS 1	DAY SCHOLAR		UGX 50,000		UGX 50,000			AKZOL BOB
5	APOLLO EDWARDS	TUNGI EDWARD	CLASS 3	DAY SCHOLAR	UGX 10,000	UGX 80,000		UGX 90,000			APOLLO EDWARDS
6		ABAZI PROMISE	CLASS 2	DAY SCHOLAR		UGX 70,000		UGX 70,000			APOLLO EDWARDS
7		AINO PEACE	CLASS 7	BOARDING		UGX 315,000		UGX 315,000			APOLLO EDWARDS
8	AGABA LINUS	AKANA TIMOTHY	CLASS 4	BOARDING		UGX 240,000	UGX 390,000	UGX 150,000	15/9/2019		AGABA LINUS
9		ATUHA PRINCESS	CLASS 2	BOARDING		UGX 230,000		UGX 230,000			AGABA LINUS
10	AGABA EDISON	AINEI NICOLE	CLASS 6	DAY SCHOLAR		UGX 100,000	UGX 30,000	UGX 70,000			AGABA EDISON
11	AINO TONNY	AINI ANGEL	CLASS 2	DAY SCHOLAR		UGX 70,000		UGX 70,000			AINO TONNY
12	AMBER RICHARD	ANAZI RECO	CLASS 5	DAY SCHOLAR	UGX 120,000	UGX 90,000	UGX 120,000	UGX 90,000	10/09/2019		AMBER RICHARD
13	ADIDA COLLINE	ATWINE ELIZABETH	CLASS 1	BOARDING		UGX 220,000		UGX 220,000			ADIDA COLLINE
14	ABLE CHARLES	KWASA MAURICE	CLASS 7	BOARDING		UGX 315,000		UGX 315,000			ABLE CHARLES
15		AINOMUGASHO BLESSED	CLASS 2	BOARDING		UGX 230,000		UGX 230,000			ABLE CHARLES
16		NAHURIRA FAITH	CLASS 3	DAY SCHOLAR		UGX 80,000		UGX 80,000			ABLE CHARLES
17		NAYEBARE ELLY	CLASS 3	BOARDING		UGX 230,000	UGX 400,000	UGX 170,000	26/10/2019		ABLE CHARLES
18	GEOFREY GUY	MWEBESA ARTHUR	CLASS 6	BOARDING		UGX 250,000		UGX 250,000			GEOFREY GUY
19		ARINDA PROSSY	CLASS6	DAY SCHOLAR		UGX 100,000		UGX 100,000			GEOFREY GUY
20		JOSHUA	CLASS 7	DAY SCHOLAR		UGX 100,000	UGX 450,000	UGX 350,000	26/10/2019		GEOFREY GUY

	K
1	helper
2	=IF(A2<>"",A2,K1)

Sheet: FESS COLLECTION

Hey!.That helps alot.
Thanks!

[mugi]

You have not told us what you expect to see and where you expect to see it. So a guess. I inserted a column after the pupil's name:

=IF(A2="",C1,A2)

copied down.

I did not how to explain it
But that's exactly what i wanted!
Thanks!