trying to do a match by relavence using excel

Question

I have this really poorly formatted data that I have to work with every week. Here's a sample:

Code:     To:     Total:       Description:      
-------------------------------------------------------------
FD987     00001   5            Food/Snack/M&M

FD987     00001   5            Food/Snack/Pretzels

NA654     00001   5            Non-Alc/Soda/Sprite

NA654     00002   2            Non-Alc/Soda/Sprite

NA987     00002   2            Non-Alc/Soda/Lemonde

I want the data to end up sorted by the "To" code, with the various data summed for subtotals in each category and subtotals for the whole "To" entry. Like this:

To:     Total:       Description:     
---------------------------------------------------
00001   10           Food Subtotal

00001   5            Non-Alc Subtotal

00001   15           Grand total

00002   0            Food Subtotal

00002   4            Non-Alc Subtotal

00002   4            Grand total

I have written code that gets "00001" and "00002". But I don't know what to do from there. Should I loop through using a combination of if and for?

example:

pseudo code:

for all i in UniqueToCodes
    if (cell.value = i) then
        Descriptions = {"Food", "Non-Alc"} 
        tempsum = 0
        for all j in Descriptions 
            q = total for item j belonging to Descriptions belonging to UniqueToCodes
            tempSum = tempSum + q
        next j
    end if
next i 

or would looping backwards be more efficient?

for example as this:

pseudo-code:

for i = lastOccurenceOf(ToCode) to firstOccurenceOf(ToCode) -1
    Descriptions = {"Food", "Non-Alc"}
    for all j in Descriptions
        q = total for item j belonging to Descriptions belonging to UniqueToCodes
        tempSum = tempSum + q
    next j
next i

(I know that the "To" codes appear always as blocks and never with interjections by other codes i.e. always as this:

00001
00001
00002

never as this:

00001
00001
00003
00001

)

also how i store all these temp sums? i.e. tempSum of "Food" and tempSum of "Non-Alc" for "To" code "00001"? I know dictionary objects map only one property to a key. But I need to match the subtotal to the Description and the Grand Total to the "To" Code. What would you say is the best approach to this problem? Any help would be greatly appreciated! Thanks!

Answer 1

Like the people said in the comments, a Pivot Table would probably be the easiest way to sum up your data. However, in order to subtotal the way you want, you need to break apart the Description column.

In Excel 2007 you can use Text to Columns as follows:

1. Select your Description column.
2. Click Data -> Text to Columns.
3. On the Convert Text to Columns Wizard make sure Original data type is set to Delimited and click Next.
4. Select Delimiters to Other with a / and then click Finish.
5. Rename the new columns whatever you want.

If Text to Columns isn't available, just create a formula to pull the first food type out of the Description. For example, using the first picture above, put this formula in cell E2. =LEFT(D2,FIND("/",D2)-1)

Now you can create a Pivot Table.

1. Make sure the active cell is within the table of data and click Insert -> PivotTable. When the Create PivotTable dialog box displays, click OK.
2. Drag To and Type to Row Lables area and Total to Values area.
3. Done. There is your basic pivot table. You can change the basic format thru Pivot Table Options and Field Settings.