Which Formula to use for data consolidation?

[jrubin]

Hello - I’m wondering the best way to do the following:

I have exported a list of data which contains the following information about customers and their orders with respect to a food delivery service:

Column A - First Name

Column B - Last Name

Column C - Date of Birth

Column D - Phone number

Column E - Email

Column F - Total Orders Placed

Column G - Total Sales

The issue is that there are duplicate customer entries IE  customers have created a couple of different accounts using different email addresses.

Example:

Row 1 : John Doe john.doe@gmail.com 10 orders and 750.00

Row 2 : John Doe john.doe25@gmail.com 3 orders and 400.00

I am trying to find the fastest way to consolidate the information and turn Row 1 and Row 2 into one row that would show as follows:

Row 1: John Doe john.doe@gmail.com 13 orders and 1150.00

Now multiply this by 100’s of entries that have created multiple accounts and it creates a tedious problem!

Is there a function that will automatically Sum Column F and Column G based on Column A and Column B having duplicate values? I would imagine the SUMIF function? Any help would be greatly appreciated.

[#2]

@jrubin 

Welcome to your Excel discussion space!

If you’re looking to ask a question or start a conversation about Excel, you’re in the right place!  Ask away.

Please include your Excel version, Operationsystem, sample file, and a bit of context to help others answer your question.

Thank you for your understanding and patience

[#3]

@NikolinoDE

I would be able to provide an example it just contains sensitive data related to individuals and their DOB’s etc so I’m not able to post it online.

[#4]

@jrubin 

Please include your Excel version, Operationsystem, sample file only if not with sensitive data, and a bit of context to help others answer your question.

thank you!

[#5]

@jrubin 

Let's say your table data range is A4:G19 (Headers are in A3:G3).

Then you can enter formula below in H4 and copy down:

=SUMIFS($F$4:$F$19,$A$4:$A$19,A4,$B$4:$B$19,B4,$C$4:$C$19,C4)

This formula sums up the Total Orders Placed for all customers with same First Name, Last Name and Date of Birth. Formula can be adapted to larger ranges and you can delete criteria for Date of Birth if you want to. In the example i include Date of Birth as there are people with same First Name and Last Name.

Enter formula below in I4 to sum Total Sales:

=SUMIFS($G$4:$G$19,$A$4:$A$19,A4,$B$4:$B$19,B4,$C$4:$C$19,C4)  

I would enter this formula below in J4 in order to return TRUE if a customer appears two or more times in the dataset:

=COUNTIFS($A$4:$A$19,A4,$B$4:$B$19,B4,$C$4:$C$19,C4)>=2

After that copy and paste only values for columns that contain formulas (H, I and J in this example) and then sort table by columns A, B and C and delete double entries.