Grouping duplicate data sets and displaying once.

Status
Not open for further replies.

rixcel

New Member
Joined
Dec 1, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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
SurnameEmail20202021PAYMENTSCSEFCREDIT/ DEBITCASH PAYOUTOUTCOMEACTION
Surname 1user1@yahoo.com$ 95.00$ 31.00$ 126.00$ 130.00$ 256.00$ 126.00OVERPAYMENTREFUND
Surname2user2@yahoo.com$ 20.00$ 116.00$ 136.00$ 113.00$ 249.00$ 136.00OVERPAYMENTREFUND
Surname 1user1@yahoo.com$ 95.00$ 31.00$ 126.00$ 130.00$ 256.00$ 126.00OVERPAYMENTREFUND
Surname 1user1@yahoo.com$ 95.00$ 31.00$ 126.00$ 130.00$ 256.00$ 126.00OVERPAYMENTREFUND
Surname3user3@yahoo.com$ 490.00$ 371.00$ 861.00$ -$ 861.00$ 861.00OVERPAYMENTREFUND
Surname2user2@yahoo.com$ 20.00$ 116.00$ 136.00$ 113.00$ 249.00$ 136.00OVERPAYMENTREFUND

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
SurnameEmail20202021PAYMENTSCSEFCREDIT/ DEBITCASH PAYOUTOUTCOMEACTION
Surname 1user1@yahoo.com$ 95.00$ 31.00$ 126.00$ 130.00$ 256.00$ 126.00OVERPAYMENTREFUND
Surname2user2@yahoo.com$ 20.00$ 116.00$ 136.00$ 113.00$ 249.00$ 136.00OVERPAYMENTREFUND
Surname3user3@yahoo.com$ 490.00$ 371.00$ 861.00$ -$ 861.00$ 861.00OVERPAYMENTREFUND
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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.
 
Upvote 0
Status
Not open for further replies.

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top