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...

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)

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? :) :) :)

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)