Algorithm to combine identical items in a list

I have a rather large worksheet that contains a list of all items in a
warehouse inventory. I would like to be able to combine duplicate
items as new inventory is added.

Each item has (among other fields) an item ID, a color and a
condition. I would like to combine only the lines where all of these
fields are identical, summing the quantities in each record, and
recalculating the cost based on the cost field in each record.
Currently, I do this by starting at the top of the list and comparing
each record to all of the records below it to the end of the list.
This is rather time-consuming as the macro must do thousands of
comparisons for each of the thousands of records in the list. Is there
a faster way to accomplish this without changing the sort order of the
list?

Thanks for your help,
William
0
William
1/8/2010 11:34:17 AM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
737 Views

Similar Articles

[PageSpeed] 23

On Fri, 8 Jan 2010 03:34:17 -0800 (PST), William <swanberg@msn.com> wrote:

>I have a rather large worksheet that contains a list of all items in a
>warehouse inventory. I would like to be able to combine duplicate
>items as new inventory is added.
>
>Each item has (among other fields) an item ID, a color and a
>condition. I would like to combine only the lines where all of these
>fields are identical, summing the quantities in each record, and
>recalculating the cost based on the cost field in each record.
>Currently, I do this by starting at the top of the list and comparing
>each record to all of the records below it to the end of the list.
>This is rather time-consuming as the macro must do thousands of
>comparisons for each of the thousands of records in the list. Is there
>a faster way to accomplish this without changing the sort order of the
>list?
>
>Thanks for your help,
>William

It's hard for me to be specific without some good examples.

Some methods might include the Data/Consolidation , Pivot tables, or possibly
constructing an array of ID&color&condition; then sorting on that array and
using then Subtotaling and collapsing the display. 
--ron
0
Ron
1/8/2010 11:57:33 AM
Can you post a few rows of the worksheet so we know which columns to
compare and which column contains the quantities.


-- 
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=168107

[url=&quot;http://www.thecodecage.com&quot;]Microsoft Office Help[/url]

0
joel
1/8/2010 12:05:19 PM
Reply:

Similar Artilces:

Recovering 'Lost' Sent Items from Outlook 2000
Recently a colleague of mine complained to me that his sent items kept getting stuck at 1000, regardless of what he sent, etc. I noticed that the auto-archiving was switched on for the sent items - so I switched it off, and now its okay. Only problem now is that my colleague wants to retrieve/recover the sent items that were pushed out of the sent items. The auto-archive was pointing to a file called "archive.pst" - however the only .pst files on the computer were one which I manually created a few moments ago and the other was automatically created by Outlook and chan...

compare 2 lists #2
I import into Excel 2 reports, One report is today's machine status, the other report is yesterdays machine status. Each report has 2 columns. One column is the machine number. The other is the lot number running on the machine. If the lot number changes during the day, that machine number will show up 2 times, once with the old lot number, and once with the new lot number. The only way to tell which is the new lot number, is to look at the old lot on the machine yesterday. I currently do this manually. Is there a function or functions I can use to automate this? Below is an example. Toda...

Get Computer Listing
I don't know if this is the correct forum, but I'm creating a remote installation program using VB.NET 2005 and am looking for a really fast way to scan the computers in my domain and create a listing them in a treeview. Is there a way to grab the computer information in the domain and display it quickly from a central workstation programmatically in VB.net? Thanks On Feb 1, 4:29=A0pm, Sean Winfield <swinfi...@cinci.rr.com> wrote: > I don't know if this is the correct forum, but I'm creating a remote > installation program using VB.NET 2005 and am looki...

when selecting contacts from drop down the list is blank
When selecting contacts from the drop down (ie if I am composing and click on the "To:" field) my contact list is blank. If I type a letter in the "Type Name or slect from list" field, all my contacts come up. What needs to be done so all my contacts are visible when I select contacts from the drop down ? Thank you mikebach <it_mikeb@hotmail.com> wrote: > When selecting contacts from the drop down (ie if I am composing and > click on the "To:" field) my contact list is blank. If I type a > letter in the "Type Name or slect from list" f...

Money 2005 Standard payee dropdown list
My list of payees (approx. 30-40) is visible in the payee list but none are there when clicking the dropdown box in the transaction form. Adding a new payee in the dropdown box adds it to the general payee list though. Typing the first letter of a payee will allow the "hidden" payee to appear in the payee field but I wish to see the entire payee list in the dropdown box. Searched settings and setup info without finding solution. The payee list normally shows those payees used in the last year... could any of the ones you don'#t see be ones older that that? It would be...

Public Folders are blank in Global Address List...
I have a rights problem on publc folders. Domain Admins are able to see public folders in Global Address List but when a Domain user look in the Global Address List all the public folders are blank. I don't know where to look... The OS is Windows 2003 and Exchange 2003 SP1 Thanks! Morten ...

List of files in a directory?
I need the modern equivalent of the MSDOS function call that provides a list of all of the files in a directory, including all the other directories. It could take a wildcard parameter so that you only get the filenames and directories that matched the wildcard. What is the modern equivalent of this? It needs to work on the command line, not in an MS Windows GUI program. Thanks. "Peter Olcott" <olcott@att.net> schrieb im Newsbeitrag news:Y84Kf.65519$bF.10987@dukeread07... >I need the modern equivalent of the MSDOS function call that provides a >list of all of t...

Help combining worksheets into a workbook
I have a template that I use to create a weekly business report, normally what I do is open the template fill in the data then "save as" (date is the name) The other day I was thinking there has to be a way to have one workbook that has all of the weekly reports in one book, here is my question. 1. How can I take all of the excel documents and put them into a workbook so that each weekly report will be a worksheet in that work book. 2. Once I get this done how can I open the template and then save that report as a worksheet within the workbook, this question is for future reports no...

distribution list #22
I dont want to show the users names on the to: line when sent. How can I turn this off and just send the name of the distribution list, not the individuals. Wally b <anonymous@discussions.microsoft.com> wrote: > I dont want to show the users names on the to: line when > sent. How can I turn this off and just send the name of > the distribution list, not the individuals. You can't with a DL. Outlook expands the DL propr to sending. Instead, use Wordmail Merge. -- Brian Tillman ...

Disappearing menu item
I set up Outlook 2003 to include News groups under the Go menu as described by the help files. It worked fine for a while but has now disappeared from the menu! Not only that but now it isn't in the Customise options either so Ican't add it again. Now I can only access news groups by loading Outlook Express directly. Anyone know of a way of adding News to the dropdown menus again? Regards, Jerry ...

Directory listing where Great Plains calls reports from
I would like to see a document that lists the calling locations of each report, such as 'Transactions/Sales/Print Sales Documents' and selecting options like Document Type as Invoice, Print: Alignment Form and Include Document: Blank Paper will produce the Invoice report: SOP Blank Invoice Form. This would facilitate the creation of reports in Report Writer. Product: Great Plains 8.00g34 ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in ...

combining files into a single newsletter
My application is developing a newsletter. The newsletter articles comes from various sources. Some from Word files without pictures, some from scanned articles with text and pictures (.doc), some from scanned pictures (.jpg) and one file contains the cover template. I wish to insert each of these different files as a full separate page/s in the newsletter. My question is how do I do that? Thanks, Tomc This is how I would do it: Create pdf files of each of them. Now combine all the pdf files into one pdf file. News letter complete. Tools needed to do the job: A pdf creating prog...

Blocked sender list #2
is it possible to export the blocked sender list out of express and into outlook?? No. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Todd asked: | is it possible to export the blocked sender list out of | express and into outlook?? ...

Separating a comma separated list
Not having a great deal of experience with macros, I thought I might see if I could pick the brains of the group. I have to essentially break out a comma separated text string in a single cell into a vertical list, with a reference number thrown in for good measure. Example. Data I have: A B 1 NUMBER RESOURCE 2 101 Smith John,Jones Adam,Brown Philip,Greene Thomas 3 102 Smith John,Jackson Arthur Needs to look like: A B 1 NUMBER RESOURCE 2 101 Smith John 3 101 Jones Adam 4 101 Brown Philip 5 101 Gr...

CtreeCtrl & item.iIntegral with manifest file not working correctly
Hi I working on a project and the tree items have different heights, i am changing the height with item.iIntegral see TVITEMEX Structure. Thats working perfect but if apply my application the manifest file for XP-Style see http://www.codeproject.com/macro/vc-xp.asp The tree lines are broken, is there a work around? If i remove the manifest files the tree control is working perfect.I want to use the XP-Style but also have the TreeCtrl working, any idea? I hope somebody can help me.... bye bye Lorenz Test Code i am using : m_tree is an CTreeCtrl; First call fillTree after that unequ...

Combining bars
I have a set of actual values and project values for Years 01-03. For 03 I have actual and forecast. I can create a chart that has one bar for each item. However I need to stack only the F03 actual and forecast values into one bar. Is this possible? 01 02 03 Fcst 03 Act 04 Proj 205 215 241 261 304 4.6% 4.9% 12.1% 8.3% 16.5% Thanks, Jason I'm not sure how you want to present the data. Stacking the actual an expected 03 data would be easy, but you'd get a very high column. Is this what you're after Attachment filename: tiered x.xls Download at...

SQL for list manipulation (a mess)
Hello, I have a set of queries which have taken on a life of their own! I don't believe there's room here for all the SQLs (nor would I wish that on anyone) but there has to be a simplier way to do this. I have a form (zfmMain) which has a listbox (lstEMainE), a toggle (tglECInactive) and subform (subfmMainE) with a listbox (lstECsubEc) with 8 or 9 columns dependeng on the selection in lstEMainE (specific Engagement or "*" - All), three toggles (tglECOpen, tglECHold, tglECClosed) and 8/9 label column headings (lblEC1…lblEC9). In VBA on the Click Event fo...

Converting crosstab data to a list
Hi, I'm struggeling to find an efficient way of converting data that are stored in a crosstable format to an excel list. Anyone any solutions? Thanks in advance. Hi! Can you describe precisely how your crosstab is organised? Al -- Message posted from http://www.ExcelForum.com To normalize data that's in a crosstab format, you can use the "unpivot' technique described by John Walkenbach: http://j-walk.com/ss/excel/usertips/tip068.htm Igor, Annet, Linda & Ramon wrote: > I'm struggeling to find an efficient way of converting data that are stored > ...

Permissions for not allowing users to delete items out of generic mailboxes created for a department...
Hey everyone, Quick question that i'm stumbiling over. I have a manager who wants his group of employees to be able to edit emails within the generic mailbox that we created for their department. However the one thing they DO NOT want certain people to be able to do is delete emails that have come into these boxes. Any ideas on how to put this in place -- I have full access to my ms exchange server however when i attempted to give the groups that i assigned these guys to, and denied them access to Deleting Storage that did not work. Thanks - any ideas would be appriciated...

List Styles and TOC
Hello, Word Gurus! I have a question regarding List Styles in Word. I created my numbering and linked Heading styles 1 - 9 with all nine levels in my list. I named and saved the new style and started list in new document. Although I linked my heading styles to each level, Word does not recognize when I try to use in my Table of Contents. Does anyone know how Word 2007 see's these linked styles and how do I get it to recognize for my TOC? Thanks So MUCH! "Mariann" wrote: > Hello, Word Gurus! I have a question regarding List Styles in Word. I > ...

Treeview and hide items
Hallo! In a CTreeview, I want hide some items (via a filter). Does anyone know, whether this is possible? I tried customdraw, but I got only some empty space for the items to hide. Thanks in advance Tim Tim Fields wrote: > Hallo! > > In a CTreeview, I want hide some items (via a filter). Does anyone know, > whether this is possible? I tried customdraw, but I got only some empty > space for the items to hide. > > Thanks in advance > > Tim > > As far as i know, you cannot hide items in treeview. Only operations you have is insert and delete. So you h...

Combine TWO Worksheets
Greetings! I have two "almost identical" worksheets and would like to combine them. How can I do that without having duplicates in the final worksheet? Greatly appreciated! Copy all data into a single sheet, flag duplicates, apply a filter and delete the flagged duplicates. To flag duplicates, in a new column, enter a Countif() formula Example, if the key field to find duplicates is column A, enter the following formula in the first cell of the first vacant row (say, R1 if you data goes to column Q) =COUNTIF(A:A, A1) Copy down so every row of your data contains the formul...

Exceptions to block list configuration and IMF
Hello, I have Exch 2003 SP2. The IMF is great but I do have a need to allow some external senders messages to get past the IMF. 1. If add the senders email address to the Block List Configuration Exceptions, will the IMF still spam it out? 2. If the IMF ignores the Block List Configuration Exception, is there another way of specifying a whitelist for the IMF. Thanks Robin You could add the senders to the Global Settings-Message Delivery-Connection Filtering-Global Accept. This should overrride all other settings. Nue <nwral@nospam.postalias> wrote in message news:C34CBCC9-...

Combining two Publisher 2007 Files
I need to know how to combine two Publisher 2007 files into one. There is no "import" function for this and I have tried cutting and pasting that does not give me the desired result, especially when working with linked text and pictures. I have also tried dragging pages from one open publisher file to another and still nothing. Thanks for helping me out. Did you try copy/paste from one file to the other's new page? -- Don Vancouver, USA "Gwendolyn Brown" <GwendolynBrown@discussions.microsoft.com> wrote in message news:9DDE89EF-F67A-41AE-847C-0225F0D7...

How to sort by "To" field while inserting items?
When in the Insert > Item window, you can only sort by "To" when under "sent items". I'm trying to find out how you can add the "To" field to other folders. Thanks Rather difficult to say since we have no idea what Outlook version you are using and from which view you invoked Insert > Item. Insert > Item uses the view you set for the Folder you invoke, which you are free to set to whatever you please. You can sort the view by any field you have chosen to display in that view. -- Russ Valentine "EJ_Denton" <EJ_Denton@di...