Search : in
By :

Excel Formula

Last answer on Feb 4, 2009 10:15:32 pm GMT Ron, on Feb 4, 2009 9:25:25 am GMT 
 Report this message to moderators

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

Configuration: Windows XP
Internet Explorer 6.0

Best answers for « Excel Formula » in :
[Excel] – Countif Formula Show[Excel] – Countif Formula Issue Solution Note Issue I have one excel file in that Column "B" has the "pending or Closed" Status and Column "C" has "Severity 1, Severity 2 and Severity 3" I want to count in one cell with "Pending...
Basic Excel Formulas ShowBasic Excel Formulas Below are some basic formulas for Microsoft excel: Basic formula : ADDITION cell A1 to A10 = sum (A1: A10) AVERAGE cell A1 to A10 = average (A1: A10) MAXIMUM cell A1 to A10 = max (A1: A10) MINIMUM...
Avoid duplicates in Excel ShowAvoid duplicates in Excel In order to avoid duplication when encoding in a column from an excel sheet: take the conditional format on the first cell under the headings (eg A2) choose the following formula:...
Ask your question Reply

1

kingsjay, on Feb 4, 2009 11:45:53 am GMT

Hi,
try this link and see if this software can solve your problem:
http://www.rocketdownload.com/products/excel-macro-sum-workb­ook.html

   
Reply to kingsjay

2

Helper, on Feb 4, 2009 9:52:54 pm GMT

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

   
Reply to Helper

3

 Helper, on Feb 4, 2009 10:15:32 pm GMT

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

   
Reply to Helper
Ask your question Reply
They need your help