Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 10th, 2012, 01:39 PM
Registered User
 
Join Date: Feb 2012
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Stupid question - help condensing a list

Need some quick help with either code or an algorithm to condense data in a table I have in an Excel (v 2007) workbook to a single instance (row) of every unique item which sums all the individual quantities of every instance of each item.

Data is basically of this format:

Qty | Description | Other Columns....

235 | Connectors
10 | Straps
110 | Couplings
90 | Straps
105 | Connectors
80 | Straps
... etc.

I need to condense all the items of like description, combining all their quantities into one row of data for each unique item E.g. from the above:

340 | Connectors
110 | Couplings
180 | Straps

Need to create a procedure / algorithm that will accomplish the above. Data will have many items and many instances of each item.

I've done a fair amount of VBA coding in Excel and can do all the worksheet basics, but have a brain fart or mental block re the specific algorithm or code I need to do what I'm sure is a very simple routine. And frankly it's a little embarrassing I haven't been able to figure it out as yet...
 
Old February 11th, 2012, 09:26 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

Try to do a SumIf. I think that should solve the problem.

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
 
Old February 12th, 2012, 05:57 PM
Registered User
 
Join Date: Feb 2012
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
Originally Posted by Shasur View Post
Hi

Try to do a SumIf. I think that should solve the problem.

Cheers
Shasur
Thanks Shasur, but Sumif wouldn't work - there would be too many unique items in the list to create a Sumif for each one (could easily number in the scores, if not hundreds of unique items).

It needs to be done programmatically.

I initially tried a "brute force" algorithm where I started with the first item in the list, copied it to my condensed list, then looping through each successive item in the un-condensed list, I'd search the condensed list to see if that item existed there yet and if so, would simply add the quantity to that quantity. And if it didn't exist, I'd add it to the condensed list then move to the next item in my uncondensed list - again, looping through each item in that list.

In theory, that algorithm should work fine; and it's possible my first attempts at it floundered merely because I'd coded it incorrectly. Getting it working wasn't a priority when I first attempted it (having a plethora of more important tasks to complete); but now risen to the fore and I'd like to attack it again - only I'm still busy and mentally just can't seem to wrap my brain around it, kwim? :) :) :)
 
Old February 12th, 2012, 10:19 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

Have your tried using Excel as a Database. I think that would give you a simple and swift solution for your requirement.

You can use GroupBy on Description and then Get the Count (*) etc to get that out.

If you want to avoid that, you can do a Pivot and get the count .

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips & Tricks (http://www.vbadud.blogspot.com)





Similar Threads
Thread Thread Starter Forum Replies Last Post
Stupid Question Yowie BOOK: Beginning Regular Expressions 1 July 23rd, 2010 05:08 AM
stupid question... seymour_glass C# 3 May 2nd, 2007 06:40 AM
Probably stupid question skiloff BOOK: ASP.NET Website Programming Problem-Design-Solution 0 July 6th, 2005 02:44 PM
stupid question Warbird General .NET 6 December 7th, 2004 09:33 AM
A really stupid question Phaedrus VB.NET 2002/2003 Basics 3 October 29th, 2003 02:50 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.