Add the contents of two sheets together

I am looking for a way to add the content of two sheets or even 1 shee
together.  I have a large list of part numbers with quantities.  Som
of the part numbers are repeated.  I would like to add the duplicat
part number quantities together and have just 1 line with the par
number and the total quantity.

Does anyone have any insight?

Thank

--
Message posted from http://www.ExcelForum.com

0
5/27/2004 3:10:20 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
337 Views

Similar Articles

[PageSpeed] 14

Hi

Sounds like a job for a pivot table. See here for an intro:
http://www.cpearson.com/excel/pivots.htm

-- 
Andy.


"mwiggins367 >" <<mwiggins367.16x77f@excelforum-nospam.com> wrote in message
news:mwiggins367.16x77f@excelforum-nospam.com...
> I am looking for a way to add the content of two sheets or even 1 sheet
> together.  I have a large list of part numbers with quantities.  Some
> of the part numbers are repeated.  I would like to add the duplicate
> part number quantities together and have just 1 line with the part
> number and the total quantity.
>
> Does anyone have any insight?
>
> Thanks
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
andyb1 (494)
5/27/2004 3:18:36 PM
Hi
have a look at pivot tables for this:
http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm

--
Regards
Frank Kabel
Frankfurt, Germany

"mwiggins367 >" <<mwiggins367.16x77f@excelforum-nospam.com> schrieb im
Newsbeitrag news:mwiggins367.16x77f@excelforum-nospam.com...
> I am looking for a way to add the content of two sheets or even 1
sheet
> together.  I have a large list of part numbers with quantities.  Some
> of the part numbers are repeated.  I would like to add the duplicate
> part number quantities together and have just 1 line with the part
> number and the total quantity.
>
> Does anyone have any insight?
>
> Thanks
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>

0
frank.kabel (11126)
5/27/2004 3:21:54 PM
An array formula (although slow) will do this:

Assume
    Part Nos are in A1:A100
    Quantities are in V1:B100

In B101, type
    =SUM(IF(A1:A100=A101,B1:B100))
Then Hold Down the Shift & Ctrl Keys and press Enter
The formula should appear as {=SUM(IF(A1:A100=A101,B1:B100))} in the formula
bar
Now, type a Part No in Cell A101
The total Quantity will appear in B101

The important thing is that all elements of the array reference the same
number of rows.


-- 
HTH
Roger
Shaftesbury (UK)



"mwiggins367 >" <<mwiggins367.16x77f@excelforum-nospam.com> wrote in message
news:mwiggins367.16x77f@excelforum-nospam.com...
> I am looking for a way to add the content of two sheets or even 1 sheet
> together.  I have a large list of part numbers with quantities.  Some
> of the part numbers are repeated.  I would like to add the duplicate
> part number quantities together and have just 1 line with the part
> number and the total quantity.
>
> Does anyone have any insight?
>
> Thanks
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


0
vba1 (6)
5/27/2004 3:24:51 PM
Pls Disregard - complete gibberish.
Clean specs required :0(

Roger


"Roger Whitehead" <vba@cornerplot.fsworld.co.uk> wrote in message
news:OD5MD7$QEHA.3528@TK2MSFTNGP09.phx.gbl...
> An array formula (although slow) will do this:
>
> Assume
>     Part Nos are in A1:A100
>     Quantities are in V1:B100
>
> In B101, type
>     =SUM(IF(A1:A100=A101,B1:B100))
> Then Hold Down the Shift & Ctrl Keys and press Enter
> The formula should appear as {=SUM(IF(A1:A100=A101,B1:B100))} in the
formula
> bar
> Now, type a Part No in Cell A101
> The total Quantity will appear in B101
>
> The important thing is that all elements of the array reference the same
> number of rows.
>
>
> -- 
> HTH
> Roger
> Shaftesbury (UK)
>
>


0
vba1 (6)
5/27/2004 3:33:42 PM
Reply:

Similar Artilces:

Want to Add more Sub description Field
How I can add more sub description field like 1,2,3,4,5 regards Saleem Suri Salaam Saleem, You have only limit up to 3 fields in RMS 1,2,3 sub-description also you can use the extended description and there is main description for the item. so Altogether you have upto 5 description limit and I think that is enough. Let me know if this won't fulfill your requirements else use the Item Notes for more description. Rate if like Regards Akber "Saleem Suri" wrote: > How I can add more sub description field like 1,2,3,4,5 > > regards > Saleem Suri Salam Akbar ...

Usig sdk to add a note to an existing account
Hi, Anyone know how to add a note programatically to an existing Account? Thanks John Hi John, It really depends on your situation the most obvious way is to use the CRM web services and simply use the create method. Public Sub CreateAnnotation() Dim service As New CrmService() service.Credentials = System.Net.CredentialCache.DefaultCredentials Dim myNote As New annotation service.Create(myNote) End Sub Of course you will need to specify all the required fields; the above alone would fail! including the associated object ID (the GUID of the accoun...

How do I add column A data labels to a scatter of columns B and C?
I have made a scatter plot using columns B and C as my x and y coordinates. I would like to use the data in column A to label my points. I already have the XY chart labeler 97 installed and selected, but I don't know how to get the data labels to show up on my graph. Thank you! Hi Christina - There are a couple of well written Excel add-ins you can download to do this: Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tools, http://j-walk.com Both are free downloads, they install easily, and they integrate into Excel's interface very nicely. They l...

range of data from different sheets
I'm having trouble creating a graph because the data is spread on different sheets. I've got a new sheet for each month and I'd like to produce a trend graph. Do I have to bring each set of numbers onto the same sheet, or is there a way to have a range of data spread over various sheets? I hope that makes sense! For any given series, for any given set of values (x or y) the data must be on the same sheet. Other than that, you can have data on multiple sheets. So, for one series you could have the series name on one sheet, the x- values on a 2nd sheet, and the y-values on a...

Add Actual End Date to Resolved Cases view
Hi We would like to add the actual end date to the Case General Tab or possibly to the Resolved Cases and/or My Resolved Cases view. I know that the Actual End Date is available on the Service Activity and Case Resolution but this field is not available for the Case. Any workaround for this? Thanks Mark ...

user flooded with "not read" receipts from two years ago!
Today (9/14/2006) I have a user who just got about 200 "Not Read" receipts from emails originally sent from 11/15/2004 to 12/17/2004. From all kinds of internal recipients, and interestingly, they all say email was deleted without being read on 8/22/2006 at 8:16 AM. Running Exchange 2003, user client is Outlook 2003. Don't (consciously) have any mailbox management policies. Only thing I can think of is that about that time (8/22/2006) I was doing a restore to a recovery storage group. Don't think that should have mattered, and heck, why today? Thanks for any poin...

content conversion errors in Exch 2003
Hello, I am getting the following errors in Exch 2003 when receiving emails from a client who is running Boldon James Enterprise Mail 3 (3.6.1 update 2). I have the patch MS04-002. Any ideas what is going on and how to resolve it? Thanks Jon Global domain identifier (Country, ADMD, PRMD) in first Trace Information of message C=gb;A= ;P=hmg+jon-dev;L=itcuser:267750:20040513134619 does not match MTSID value. [MTA XFER-IN 13 40] (12) Object 0600003E received from entity /DC=UK/DC=MIL/DC=TS/DC=DIS/CN=CONFIGURATION/CN=SERVICES/CN=MICROSOFT EXCHANGE/CN=DISEXCHANGE/CN=ADMINISTRATIVE GROUPS/CN=F...

Can I have one register from two accounts in M03 and in import question
I have just begun trying-out Money 2003 and am having trouble doing what I want to do. I have two accounts, one is a Merrill Lynch money market account which contains my portfolio and my VISA account and I also write checks in that account. The second account is a Credit Union account in which deposits are made and from which I pay bills. I would like to have one register that will contain all deposits and expenditures, including the VISA charges, from both accounts. Can I do this and how? Also, ML only keeps 90 days worth of transactions. I would like to load the entire year into Mone...

Protect sheet #3
I am working on an Excel document witch is a big database. I would like to set a password with the "protect sheet" option in the Tools/Protection menu. "Normal" users who will open the database won't be able to edit the document, but will rather open it in "read only" mode. The problem is that I still want these users to be able to use the "auto filter" and also use the "sort A-Z" option. I have tried to select these options in the configuration of the protection (protect sheet window) but it doesn't work. I can't do any of those tw...

Two Lower Filters
Hello, I have vendor provided USBSTOR lower filter. I wanted to add my filter driver below the USBSTOR lower filter. I actually want to filter the requests further. I'm not sure if this is possible to have two filters one below the other, but I just wanted to check on this. Thanks nd Regards, uba You can have as many filters as needed in a given location. You cannot guarantee that your filter is immediately below another filter because some other filter may get there first. -- Don Burn (MVP, Windows DKD) Windows Filesystem and Driver Consulting Website: http:/...

how do I display cell contents when I am over 32, 767 characters?
Excel was not designed for this. Depending on your purpose, you should use Word or Access. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ "usdrmd" <usdrmd@discussions.microsoft.com> wrote in message news:E30267BD-2CAF-4FC7-83A2-F9E79F7CFE06@microsoft.com... > ...

How do I change the $ currency symbol in a balance sheet template
Can someone please help me remove the $ symbol from a template. I've set thre area settings but the 'format' and then 'Cells' option is not open to me. The sheet must be protected. You will have to unprotect the sheet first from the Tools>Protection>unprotect sheet. If it asks for a password, then probably you are not meant to fiddle around with the format of the template! "Dave" wrote: > Can someone please help me remove the $ symbol from a template. > > I've set thre area settings but the 'format' and then 'Cells' opt...

join two documents
How do I join two files (12 pages one, 10 the other) so that I have only one document of 22 pages? Open both files (run Publisher twice) then cut or copy from one and paste to the other. -- Don Vancouver, USA "MARCO" <MARCO@discussions.microsoft.com> wrote in message news:374A07F4-58E0-4A58-88DF-6B5FCC58435B@microsoft.com... > How do I join two files (12 pages one, 10 the other) so that I have only one > document of 22 pages? ...

How to Remove a Button on sheet
Gidday, I do not like to use buttons on worksheets because I was fooling around and suddenly I have a button on the sheet that cannot be right clicked or deleted. Should I change something in the properties window before I close it? I am using Excel 2000 but also found this problem with Excel 97. Regards Mark PS This is why I only use Userforms or toolbar buttons to initiate macros. You need to get into design mode to edit controls in worksheets. If yo bring up the "control toolbox" toolbar and click the design mod button. This should then enable you to delect the button D...

Retrieving the Top 10 of the Average of Two columns, but displaying a third
I'm new to Excel programing, so please bear with me. I have an excel spreadsheet with 3 columns. Column1 Column2 Column3 -------------------------------------- John Doe 5 3 Jane Smith 2 9 Bill Smith 4 2 What I need to do is take the average of Column 2 and 3, find the highest average of the entire list and display Column 1 as the final result. In other words: I need to create a Top 5 List of the people with the highest grade average. I don't care much for displaying the grade, I just need the top 5...

Add number of days due field on aged trial balance reports (PM/RM)
I would like to add 'number of days past due' field on the aged trial balance reports and sort the reports by this field. I dont know how to proceed. Can someone guide me? ...

User is two
Hi, In Exchange 2k3 SP2, a user send me email internally. When I reply, I get a postmaster report that the address does not exist in the organization. When composing a new message, and when writing the first few letter of that user account name, two identical address appear. I can send to one but receive from the other, to which I can not send. How to fix this creepy situation? Thanks Yba On Mon, 18 Sep 2006 13:59:02 -0700, yba02 <yba02@discussions.microsoft.com> wrote: >Hi, >In Exchange 2k3 SP2, a user send me email internally. When I reply, I get a >postmaster repor...

STDEV down, but not across, two cols??
I don't understand Standard Deviation very well - I was just told to use this function. Up 'til now, I've only had one column of numbers. Now I have two columns of five data points each. Each set across is one pair (two measurements of the same item). I don't think they want the STDEV between the paired points included in the calculations, only between the individual pairs. So how do I reduce this to only five data points and still get a valid STDEV? Or have I asked an irrational question because I don't know STDEV? ED standard deviation is the parameter for the ...

Why does it add two items?
Hi I have the following line of code: ctrlListBox.AddItem Item:=strItem However, where the value of strItem contains a comma, such as KOREA, DEMOCRATIC PEOPLE'S REP, it comes out as two separate items, KOREA and DEMOCRATIC PEOPLE'S REP. Why is this? How can I prevent it? Stapes "Stapes" <steve.staple@gmail.com> wrote in message news:2e1b5878-22da-4f9a-bf9d-f122d5e87006@j20g2000hsi.googlegroups.com... > Hi > > I have the following line of code: > > ctrlListBox.AddItem Item:=strItem > > However, where the value of strItem contains a comma, such...

2B-Inventory December Giveaway!! (RMS/POS Add-on)
Mobile/Handheld Inventory Management & More for Microsoft Retail Management System and Microsoft Point of Sale ENTER TO WIN A FREE COPY OF 2B-INVENTORY! Register to win a FREE copy of 2B-Inventory and one year of free maintenance to be given away December 20th, 2005. Click here to register today: http://www.2Bsolutions.net/WIN.htm Mobility - Anytime, anywhere access to inventory, customer, & supplier details - even away from the store! Easy to use and manage - Simple install, built-in help file, auto save, multiple scanning modes, synchronization options (sync cable or wi...

Does anyone know of a template for bid estimate excel sheets? Is.
Check Microsoft Office Web Site or search google for "Bid Estimate" "Excel Template" "Lenora" <Lenora@discussions.microsoft.com> wrote in message news:00E08EC7-D926-49A2-9362-D59FC9088F88@microsoft.com... : ...

Spread sheet to a Gif.
How do you convert an xls spread sheet to a gif. I want to put it in a word document and use arrows in work to point to the areas of the pic to talk about for instructions. IE explanations When I copy and paste, the image is too large. Thanks. You can use a screen capture program to create a graphic file. I use SnagIt (you can download an evaluation copy here): http://www.techsmith.com/products/snagit/default.asp and have used ScreenSeize, which you can download here: http://www.pcmag.com/article2/0,4149,26282,00.asp anonymous@discussions.microsoft.com wrote: > How do you ...

Add "/" when the cell is empty
Can I use conditional formating to make a cell crossed by a line when it is empty. �@ Thanks for your help. Eling begin 666 clip_image001.gif M1TE&.#EA20`7`'<!,2'^&E-O9G1W87)E.B!-:6-R;W-O9G0@3V9F:6-E`"'Y M! $```$`+ $``0!'`!4`@ ```(&!@0)$C(^IR^U_`("TVB?EW9SF#H9(-HDF M]YUJ1:XNIKVRTLZVD=YSK<MY[^(!5[_A26@T%9,A)!.T?**BT@NURKIB/:6M $J0``.P`` ` end The diagonal border isn't one of the CF choices. You could use VBA, though. Put this in the worksheet code module (right-click on the worksheet tab and choose View Code): Private Sub ...

Comparing two different spreadsheets
I need to compare two different spreadsheets. One spreadsheet has ticket numbers and amounts. The other spreadsheet has ticket number, amount, name, and product name. I need to be able to compare ticket number and amount on both spreadsheet. If they match, the name needs to be entered on the spreadsheet with only the ticket number and amount. I also need the product name to be move but I can repeat the previous solution unless there is a way to do both at the same time. One way to achieve the lookup based on 2 col uniques is to use an array -entered index/match of this structure: ind...

edit a comment while protect sheet is on the cell is unprotected
i want other useres to b able to enter /edit a comment in an unprotected cell when protect sheet is on When protecting the sheet enable "Edit Objects" in the allow users to list. Gord Dibben MS Excel MVP On Thu, 4 Feb 2010 10:20:02 -0800, Rubber 4 u <Rubber4u@discussions.microsoft.com> wrote: >i want other useres to b able to enter /edit a comment in an unprotected cell >when protect sheet is on ...