# Excel Formula

Closed
Ron - Feb 4, 2009 at 04:25 AM
Helper - Feb 4, 2009 at 05:15 PM
Hello,

I have a excel spreadsheet where I have 10,000 rows and columns are filled with data, column headers are Name, country,total users, total usage, the problem I am facing here is there are duplicates for example :-

Name Country Tota lUsers Total usage
Nick US 5 6
Adam UK 4 3
Nick US 5 4

I have sorted according to name coz I have many duplicates and now the date shows this way
Name Country Tota lUsers Total usage
Nick US 5 6
Nick US 5 4
Adam UK 4 3

My question is I want to add the columns in one row for example
Name Country Tota lUsers Total usage
Nick US 10 9
Basically add the data and make sure there are no duplicates, I cannot do this manually as its a huge file, Is there a Macro or a formula where I can work on. Please help!

Thanx!
Ron
Related:

## 2 responses

kingsjay Posts 17 Registration date Saturday August 23, 2008 Status Member Last seen July 2, 2009 3
Feb 4, 2009 at 06:45 AM
hi,
try this link and see if this software can solve your problem:
https://www.rocketdownload.com/query.php?q=excel+macro+sum+workbook
Assumptions for this code:
1) You have already sorted column A (by Name) as you stated.
2) There are no empty cells within column A of your 10,000 rows of data.

This code will check for duplicate Names in column A, and then sum column C values and column D values while
deleting the duplicates to leave just one unique Name with the totals as you stated.

Private Sub AddQuantities()

Dim i
i = 2

Do Until IsEmpty(Range("A" & i))
dup = i + 1

Do While Range("A" & i) = Range("A" & dup)

Range("C" & i) = Range("C" & i) + Range("C" & dup)
Range("D" & i) = Range("D" & i) + Range("D" & dup)
Rows(dup).EntireRow.Delete Shift:=xlUp

Loop

i = i + 1

Loop

End Sub
Sorry, use this instead. I did not have the code totaling the unique value left for columns C & D.

Dim i
Dim tot
Dim tot2
i = 2

Do Until IsEmpty(Range("A" & i))
dup = i + 1

Do While Range("A" & i) = Range("A" & dup)

Range("C" & i) = Range("C" & i) + Range("C" & dup)
Range("D" & i) = Range("D" & i) + Range("D" & dup)
Rows(dup).EntireRow.Delete Shift:=xlUp

Loop

tot = tot + Range("C" & i)
tot = tot + Range("D" & i)
i = i + 1

Loop