Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using DSum in Excel

Posted on 2000-04-24
6
Medium Priority
?
843 Views
Last Modified: 2012-08-14
I have a spreadsheet with a large number of records.  These were taken down from a database.  The numbers unfortunately have duplicated.  One list shows part Numbers and a further field shows quantities.  I have been advised to use dsum to get rid of the the duplicate numbers and to add the quantities.  I find the online help files defeat me and am lost how to use this function or even am unsure if it is the correct way to do this.  Can anyone help
0
Comment
Question by:martin_mcginn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 4

Expert Comment

by:Noggy
ID: 2743961
Hmm, I wouldn't use DSum for this. Instead, use Data|Filter|Advanced Filter:
1. To remove duplicates, select the table (all of the fields and the column headers and the data).
2. Goto Data|Filter|AdvancedFilter in the Excel menu.
3. Check the Unique Records Only checkbox.
4. Select Copy to another location.
5. Put your insertion point in the Copy To textbox.
6. Select a cell ON THE SAME SHEET where you want the data to go. Note that the cell must not be in the source table.
7. Press OK.
8. You will now only have unique records.

I think this is what you want. If not, can you define a bit more the structure of the data table and in which fields the duplicates are in?
0
 

Author Comment

by:martin_mcginn
ID: 2745446
The data I have are a large number of purchasing records.  Each time an article was purchased the table shows the article number together with the quantity purchased   Unfrotunately whenever further stock was purchased it repeats the Article number and all the other unique information o/n etc.  I want to be able to list an article number and the total quantity bought and it is driving me around the twist.
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 400 total points
ID: 2745723
If it's a once off exercise, you could import the data into Access, write a group-by query for the article number and DSUM the other values and export it back to excel.
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 4

Expert Comment

by:Noggy
ID: 2746715
Aah, so they're not really duplicate numbers in the field that you want to sum (quantity purchased). You can ignore(or delete) all the other repeated fields and just basically look at your Article Number and Quantity Purchased fields. Hence the DSUM.

Ok, so here it is. If you have the following data in a spreadsheet, with the headers in row 1 and the data starting at row 2:
Col A                            Col B                                              Col C
Article Number      Quantity Purchased            Article Number
1                                      50                                           2
2                                      60            
3                                      70            
1                                      80            
2                                      90            
3                                      100            

$C$1 = The Header of the field that the criteria need to match.
$C$2 = The criteria match value

For your DSUM, you need:
=DSUM($A$1:$B$7,$B$1,C1:C2)
Returns the value 150

where:
$A$1:$B$7 = All the table that you want the DSUM to apply to. This should include at least the field that you want to sum and the field that the criteria is to apply to.
$B$1 = Either the label of the column in double quotation marks or a number that represents the column's position in the list. Here I have used the former.
C1:C2 = The Criteria cells containing the conditions for the Sum. The range must include a column label (the same as in the table)  and, one cell below that,  the value for the condition.

Nico's suggestion is also good too. I would use that unless this data is going to be continually coming in in this horrible format.
0
 

Expert Comment

by:DrewD1
ID: 2748466
How about a pivot table?
You can select the item number and the quantity, as well as the headers for bot columns.
Then under the data menu, select Pivot Table.
Your data source is the current excel file, and you can output to a new sheet or the same one.  for the layout. you can make the row equal to the item number and drag the quantity to the dat section. It shopuld automatcially create it as a sum. the resulting pivot table should be what you are looking for.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 2748806
Nice to know this did the trick !

Success.

Nico
0

Featured Post

Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question