Grouping duplicate data sets and displaying once.

[rixcel]

Hi All,

hoping you can help.

I have a spreadsheet with several sheets that have data of students. There are families with 2 or more students and I have formulas that calculate the total cost for each family by combining all the data for siblings in that family. For example, if there are 3 siblings, it will produce the total result for all three in each students row - so there will be three lots of the same data - See Table 1 below. The issue is, I want to have an auto sum of the total and it is adding multiples of the same data where there are 2 or more students from the same family.

Can someone help to create a rule so that it recognises multiples of the same data and just collate it once - it could collect into another (summary? sheet (preferred), similar to Table 2 below.

The unique identifiers that collect the data from the same family are the Surname and Email.

Table 1

Surname	Email	2020	2021	PAYMENTS	CSEF	CREDIT/ DEBIT	CASH PAYOUT	OUTCOME	ACTION
Surname 1	user1@yahoo.com	$ 95.00	$ 31.00	$ 126.00	$ 130.00	$ 256.00	$ 126.00	OVERPAYMENT	REFUND
Surname2	user2@yahoo.com	$ 20.00	$ 116.00	$ 136.00	$ 113.00	$ 249.00	$ 136.00	OVERPAYMENT	REFUND
Surname 1	user1@yahoo.com	$ 95.00	$ 31.00	$ 126.00	$ 130.00	$ 256.00	$ 126.00	OVERPAYMENT	REFUND
Surname 1	user1@yahoo.com	$ 95.00	$ 31.00	$ 126.00	$ 130.00	$ 256.00	$ 126.00	OVERPAYMENT	REFUND
Surname3	user3@yahoo.com	$ 490.00	$ 371.00	$ 861.00	$ -	$ 861.00	$ 861.00	OVERPAYMENT	REFUND
Surname2	user2@yahoo.com	$ 20.00	$ 116.00	$ 136.00	$ 113.00	$ 249.00	$ 136.00	OVERPAYMENT	REFUND

Formula for amount under 2020 column: =SUMIF(OUTCOME!$H$4:$H$278,B4,OUTCOME!$I$4:$I$278)
Formula for amount under 2021 column: =SUMIF(OUTCOME!$H$4:$H$278,B4,OUTCOME!$J$4:$J$278)
Formula for amount under PAYMENTS column: =C4+D4
Formula for amount under CSEF column: =SUMIF(OUTCOME!$H$4:$H$278,B4,OUTCOME!$L$4:$L$278)+SUMIF(OUTCOME!$H$4:$H$278,B4,OUTCOME!$K$4:$K$278)
Formula for amount under CREDIT/DEBIT column: =E4+F4
Formula for amount under CASH PAYOUT column: =IF(E4<=1,"-",E4)
Formula for amount under OUTCOME column: =IF(G4>=1,"OVERPAYMENT","UNDERPAYMENT")
Formula for amount under ACTION column: =IF(I4="OVERPAYMENT","REFUND","COLLECT")


Table 2

Surname	Email	2020	2021	PAYMENTS	CSEF	CREDIT/ DEBIT	CASH PAYOUT	OUTCOME	ACTION
Surname 1	user1@yahoo.com	$ 95.00	$ 31.00	$ 126.00	$ 130.00	$ 256.00	$ 126.00	OVERPAYMENT	REFUND
Surname2	user2@yahoo.com	$ 20.00	$ 116.00	$ 136.00	$ 113.00	$ 249.00	$ 136.00	OVERPAYMENT	REFUND
Surname3	user3@yahoo.com	$ 490.00	$ 371.00	$ 861.00	$ -	$ 861.00	$ 861.00	OVERPAYMENT	REFUND

 

[rixcel]

Anyone?

 

[smozgur]

Anyone?

Please do not mark a post as a solution unless you receive or post an answer to your question since it might also cause other members to visit your question assuming it has been already answered.

 

[Fluff]

Duplicate to: I cannot solve this one...

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.