need macro to group in excel

[kpalazzi]

I import a query of information into excel. Then I have macros to do "clean Up" stuff like change heading names.

I then have insert a line between groups of PO Numbers and subtotal the value of the PO Numbers. Can a Macro do this?
Kimberly Palazzi
kpal29@hotmail.com

 

[Dreamboat]

Why not just record another macro that does:

Data-Subtotals....

set your subtotal preferences there.

Then, you can make another macro that removes the subtotals if you want. Also, you can place the subtotals macro code at the end of your other macro instead of having it be a separate one.

http://www.TheWordExpert.com

 

[Dreamboat]

Sorry....while recording, and after doing your subtotals thingee, hit the 2 at the top left of your document to see only the subtotal lines if you like.

http://www.TheWordExpert.com

 

[kpalazzi]

How do I record a macro that does data sub-totals, I have never done it before?
Kimberly Palazzi
kpal29@hotmail.com

 

[xlbo]

Tools - Macro - Record Macro
Select range
Data - Subtotals - follow wizard

Stop recording
Da Daaaaa
Geoff

 

[kpalazzi]

ALRIGHT!! That works great. Now, is there a way to insert a blank line in between groups of data?

And maybe leave out the bolded "TOTAL FOR MJH45263" That is the PO Number under each group?
Kimberly Palazzi
kpal29@hotmail.com

 

[SkipVought]

Hi, kpalazzi,

It is a VERY BAD practice to insert empty rows or columns in tables of data that you are analyzing and reporting. Use a border or increase the height of a row, but don't insert empty rows into a table. By doing that you destroy the ability of Excel to do any further data manipulation on that data.

Skip,
metzgsk@voughtaircraft.com

 

[kpalazzi]

I agree. But this is an invoice for a VERY picky client that wants it to look eacactly as they want it, and they want blank rows inbetween the groups.

Also, they want the totals to the right of the group, not the bottom, is there a way to do that, I didn't see that option in the Subtotals dialog box.
Kimberly Palazzi
kpal29@hotmail.com

 

[SkipVought]

Keep in mind that there should be a difference between source data and reporting data, such as an invoice. Try to keep your source data in tact and format your invoice like a mail merge perhaps.

Excel has a powerful set of data analysis features, like Sub Totals. But when your requirements exceed the capability of the built-ins, you will need to write VBA code to perform the actions and formatting that you want. Try recording the things that you want to happen. Then take that code and refine it. You can get help here at Tek-Tips, to do just about anything. Skip,
metzgsk@voughtaircraft.com

 

[SkipVought]

Hey, kpalazzi,

If you made that row containing the sum, 2 times as high, wouldn't it look like a blank row? Skip,
metzgsk@voughtaircraft.com

 

[kpalazzi]

Can you give me sample code to change something that looks like this:

Unit # PO Number Value
55555 aaa 75.00
54125 aaa 75.00
Total aaa 150.00

into something that looks like this
Unit # PO Number Value subtotal
55555 aaa 75.00
54125 aaa 75.00 150.00

and tell me how to include it in my macro?
Kimberly Palazzi
kpal29@hotmail.com

 

[SkipVought]

1. Get rid if the rows that have subtotals
2. This could be done on the sheet or as a VBA procedure...

spreadsheet solution...
1. col D formula, entered in row 2

=IF(B1=B2,D1+C2,C2)

2. col E formula, entered in row 2

=IF(B2=B3,"",D2)

VBA solution

Sub RightSubTotals()
    Dim rng As Range, PrevPO, vValue, lRow, iCol
    Application.DisplayAlerts = False
    Cells(1, 1).CurrentRegion.CreateNames _
        Top:=True, Left:=False, Bottom:=False, Right:=False
    PrevPO = Empty
    iCol = Range("Value").Column
    vValue = 0
    For Each rng In Range("PO_Number")
        lRow = rng.Row
        If Not IsEmpty(PrevPO) Then
            If PrevPO = rng.Value Then
                vValue = vValue + Cells(lRow, iCol).Value
            Else
                Cells(lRow - 1, iCol + 1).Value = vValue
                vValue = Cells(lRow, iCol).Value
            End If
        End If
        PrevPO = rng.Value
    Next
    Cells(lRow, iCol + 1).Value = vValue
End Sub

Hope this helps ;-)
Skip,
metzgsk@voughtaircraft.com