Macro summary and entry deletion

Microsoft Office excel 2007 home & stude...
July 27, 2010 at 22:45:43
Specs: Excel 2007
I have a spreadsheet that has the following column headers

Acct #
Maj Com
SS Piece Count
SS Total $
Fresh Piece Count
Fresh Total $

There are multiple entries for each acct # based on the Maj Com (Major Commodity) For example, there can be 8 entries for one acct # because of the different Maj Com codes. I have already created a macro to change the certain Maj Com codes to either 37 (SS) or 63 (Fresh). However, it still leaves all of the multiple row entries per acct #. I would like to create a macro that will summarize all of the Maj Com code 37 and
put the SS piece count total in (1) column and the SS total $ in column, then summarize code 63 and put the Fresh piece count in (1) column and the Fresh Total $ in (1) column and then delete the extra rows, so ultimately there is only (1) row for each acct #

Thanks so much in advance for your assistance


See More: Macro summary and entry deletion

Report •


#1
July 28, 2010 at 04:29:16
Hi,

I am not clear what your output is going to look like.
(I have used [] to identify individual cells)
is it:

A		B	C		D		E		F
[Acct #] [Maj Com] [SS Piece Count] [SS Total $] [Fresh Piece Count] [Fresh Total $]

i.e. SS Piece Count is the sum of all lines SS Piece Count, and SS Total $ is the sum of all lines SS Total $ under the Acct #
or could it be:
A		B	C		D		E		F
[Acct #] [Maj Com] [SS Piece Count] [SS Total $] [SS Piece Count] [SS Total $] ....

i.e., if SS Piece Count and SS Total $ occur more than once for the same Acct #, each one is shown on the same line

Also does the Acct # only occur once in the column, or can there be multiple entries for the same account number.

Under the Acct # is the structure always like this:

Rows	Data in col. A
1	Acct #
2	Maj Com
3	SS Piece Count
4	SS Total $
5	Fresh Piece Count
6	Fresh Total $

or could it be variations such as:
Rows 	Data in col. A
1	Acct #
2	Maj Com
3	SS Piece Count
4	SS Total $
5	Fresh Piece Count
6	Fresh Total $
7	SS Piece Count
8	SS Total $

To propose a macro, it would help to know which column the data is in and the name of the worksheet.

Would it be acceptable to put the sorted data into a second worksheet, leaving the source data unchanged.

Will you be updating this data - if so will new information be added for the same Acct # and will that need to be added to or appended to the existing summarized data.

Regards


Report •

#2
July 28, 2010 at 06:33:04
First of all thank you so very much for your quick response!!!

The structure is in columns, not rows, so each of the above are headers. There is more information on the report that I did not include, but this is the area I am struggling with.

Here is the initial report I get. These are the headers below starting with the "Sls Div. No." in column A:

Sls Div No.
Ledger
Merch Acct
Acct #
Customer Name
Address
City
Window Start
Window End
Truck Number
Stop Number
Driver Name
Driver Cell
Maj Com
Piece Count
Commodity Total $
Assigned To

The information is currently summarized by Acct # and Maj Com. There are the following Majo Com. type (10, 12, 15, 30, 40, 43, 44, 60 and 65) All Major Com types that are 12 or 43 need to be changed to 63 and all the other Maj Com types need to be changed to 37. Because of this an acct # can have multiple rows summarizing each Maj Com and remains that way only now the Maj Com is only 37 or 63. Maj Com 37 will be the Smart Stock code and 63 will be the Fresh code.

Once that is complete, then I want to summarize each Acct to have the headers look like this. Again starting with column A

Sls Div No.
Ledger
Merch Acct
Acct #
Customer Name
Address
City
Window Start
Window End
Truck #
Stop #
Driver Name
Driver Cell
Smart Stock Piece Count
Smart Stock Total $
Fresh Piece Count
Fresh Total $
Time Allotment (minutes)
Assigned To

I only want (1) row entry per Acct # in the 2nd scenario and I need it to summarize all the Smart Stock Piece Count, all the Smart Stock Total $, Fresh Piece Count and Fresh Total $ under each header.

The Time Allotment comes from the Smart Stock piece count multipled by 1 (for 1 minute) and Fresh Piece Count multipled by 1.75 (for 1.75 minutes) and totaled.

I am totally open to the information exporting to a different sheet. I am just looking for the information to be in format described in the 2nd scenario.

I hope this helps give a better explanation.

Thank you again so much for your help. I am truly grateful!!


Report •

#3
July 28, 2010 at 15:30:24
Hi,

I have written a macro that will find all the unique account numbers on a worksheet named "Source" (A/C # in col. D)
It then creates a table of these accounts on a worksheet named "Destination"
It copies columns A to M to the destination worksheet, using the data from the first occurrence of that A/C # on the "Source" worksheet.

It then goes through the source data again.
For each Source row it finds the matching A/C # on the destination worksheet.
It then takes the Maj Com code and converts it (currently based on a hard-coded conversion table - but this could be a table on a worksheet, to make future changes easier).
Based on the converted code (63 or 37), the piece count and the Commodity total are added to either the Fresh Piece or Smart Stock columns.

I suggest running the code from a button embedded in the "Source" worksheet:
From the Ribbon select Developer (If it's not visible go to the Office Button, select Excel options at the bottom and select the Popular tab and check the 'Show Developer tab in the Ribbon' box)

In Developer - Controls select Insert and choose the button icon.
Draw the button on the worksheet
In the 'Assign Macro' dialog box select 'New'

In the code window that opens enter this:

Option Explicit

Private Sub CommandButton1_Click()
Dim rngStart As Range
Dim rngEnd As Range
Dim rngDestStart As Range
Dim rngCell As Range
Dim intDestOffst As Integer
Dim strArrayCnv(8, 1) As String
Dim strCnvCode As String
Dim strACnum As String
Dim blnFound As Boolean
Dim n, o As Integer

On Error GoTo ErrHnd

'disable screen updating
Application.ScreenUpdating = False

'set start of source data
Set rngStart = Worksheets("Source").Range("A2")
'find end of source data
Set rngEnd = Worksheets("Source"). _
        Range("A" & CStr(Application.Rows.Count)).End(xlUp)
'set start of destination data
Set rngDestStart = Worksheets("Destination").Range("A2")
'set destination row offset to 1 (headers in row 1)
intDestOffst = 1

'setup conversion codes
strArrayCnv(0, 0) = 12
strArrayCnv(0, 1) = 63
strArrayCnv(1, 0) = 43
strArrayCnv(1, 1) = 63
strArrayCnv(2, 0) = 10
strArrayCnv(2, 1) = 37
strArrayCnv(3, 0) = 15
strArrayCnv(3, 1) = 37
strArrayCnv(4, 0) = 30
strArrayCnv(4, 1) = 37
strArrayCnv(5, 0) = 40
strArrayCnv(5, 1) = 37
strArrayCnv(6, 0) = 44
strArrayCnv(6, 1) = 37
strArrayCnv(7, 0) = 60
strArrayCnv(7, 1) = 37
strArrayCnv(8, 0) = 65
strArrayCnv(8, 1) = 37

'get all account numbers and place them in destination
For Each rngCell In Range(rngStart, rngEnd)
    'get Acct # (col.D)
    strACnum = rngCell.Offset(0, 3).Text
    'test destination (col.D) for this account number
    If intDestOffst = 1 Then
        'no data in destination yet, so copy this
        rngCell.Resize(1, 13).Copy _
                Destination:=Worksheets("Destination").Range("A2")
        'increment destination offset counter
        intDestOffst = intDestOffst + 1
        Else
        'loop through destination data
        'set found flag to not found
        blnFound = False
        For n = 1 To intDestOffst
            If Worksheets("Destination").Range("D1").Offset(n, 0). _
                Text = strACnum Then
                blnFound = True
            End If
        Next n
        'if not found then copy to next row
        If blnFound = False Then
            rngCell.Resize(1, 13).Copy _
                        Destination:=Worksheets("Destination").Range("A1"). _
                        Offset(intDestOffst, 0)
            'increment destination offset counter
            intDestOffst = intDestOffst + 1
        End If
    End If
Next rngCell

'loop through source, converting codes and adding data to appropriate groups
For Each rngCell In Range(rngStart, rngEnd)
    strACnum = rngCell.Offset(0, 3).Text
    'find matching Acct# on destination sheet
    For n = 1 To intDestOffst - 1
        If strACnum = Worksheets("Destination").Range("D1").Offset(n, 0) _
                .Text Then
            'get Maj Com code (col.N)
            'set converted code to ""
            strCnvCode = ""
            For o = 0 To UBound(strArrayCnv, 1)
                If strArrayCnv(o, 0) = rngCell.Offset(0, 13).Text Then
                    strCnvCode = strArrayCnv(o, 1)
                End If
                If strCnvCode <> "" Then Exit For
            Next o
            'add values based on converted code number
            'code 37-Smart Cols. N & O : code 63-Fresh Cols. P & Q:
            'source  piece count Col.O :Amount Col.P
            If strCnvCode = 37 Then
                'smart stock code 37
                'add piece count
                Worksheets("Destination").Range("A1").Offset(n, 13).Value = _
                    Worksheets("Destination").Range("A1").Offset(n, 13).Value _
                    + rngCell.Offset(0, 14).Value
                'add Amount
                 Worksheets("Destination").Range("A1").Offset(n, 14).Value = _
                    Worksheets("Destination").Range("A1").Offset(n, 14).Value _
                    + rngCell.Offset(0, 15).Value
            Else
                'fresh stock code 63
                'add piece count
                Worksheets("Destination").Range("A1").Offset(n, 15).Value = _
                    Worksheets("Destination").Range("A1").Offset(n, 15).Value _
                    + rngCell.Offset(0, 14).Value
                'add Amount
                 Worksheets("Destination").Range("A1").Offset(n, 16).Value = _
                    Worksheets("Destination").Range("A1").Offset(n, 16).Value _
                    + rngCell.Offset(0, 15).Value
            End If
        End If
    Next n
Next rngCell
're-enable screen updating
Application.ScreenUpdating = True
Exit Sub

'error handler
ErrHnd:
Err.Clear
're-enable screen updating
Application.ScreenUpdating = True
End Sub

Note that Sub Button1_Click() and End sub will already be present, so don't duplicate them. Option explicit goes before Sub Button1_Click().

Click Save from the Visual Basic Menu.

Alt+f11 takes you back to the main Excel window.

Right click the button and Edit the name to something meaningful

As changes made by Macros cannot be undone with the Undo button, test this macro on a copy of your data. Always make a backup of your Workbook before running this macro. This code has only been tested on very limited sample data, and it has not been tested in your environment, so test it on copies of your data to ensure that it works 'as expected'

After selecting any cell, the new command button should now respond to a click and run the macro.

You haven't said how this macro will be used - will the data be added to and then the destination data will need to be erased before running the macro again, or will the source data be removed and new data added, and then the existing data will need to be added to.

Both these scenarios will require changes to the code - as it stands the macro is a one-time deal!
To re-run it for testing, delete the data from the destination worksheet.
I put the header row (row 1) on the destination sheet by hand before starting. Destination data starts on row 2.
To test this two worksheets must be present - named "Source" and "Destination"

Regards


Report •

Related Solutions

#4
July 28, 2010 at 16:35:57
HI -

Thank you again for this work. I screwed up and ran the macro before I had the destination page and now it doesn't work. I am sorry I am unclear on this. I thought from the message the macro would create a "Destination" page is that not correct?

I am open to suggestions how the marco should run. At the end of the day, I just need the column headers to end up like in scenario #2 previously described. I believe your 2nd guess is correct. If this is supposed to create a new page called "Destination", then the information from columns A - M would need to be re-added to the destination sheet. Then the calculations would be there beginning with column N being the Smart Stock Piece Count. I think what I did not explain clearly is that column N (Maj Com) in the inital report will determine if the data goes into column N and O (Smart Stock Piece Count and Smart Stock Total $) or if the data will go to column P and Q (Fresh Piece Count and Fresh Total $). Once the macro determines which type of Maj Com it is, I think it would be able to summarize the information of each type in the appropriate column and have one row entry for each Acct #.

Can you please tell me if I am suppose to create the destination sheet prior to running the macro? If so, do I include the data from column A - M and just remove the duplicate row entries?

Again thank you so much for all your help. Look forward to hearing from you soon.


Report •

#5
July 28, 2010 at 18:59:00
Hi,

Yes, to run this macro your workbook must have two worksheets:
One named "Source" and the other named "Destination".
"I should have been clearer on this - "Destination" is required before running the macro.
The destination worksheet should have the column headers in place on row 1 (although this is not necessary for the macro to work).
The "Source" workbook has columns of data starting in Row 2.
Row 1 contains the headers you described - Acct # in column D, Maj Com in column N etc.

For this to work, column A must contain data in every used row (the macro uses this to determine where the data ends).

Embed the Button in the "Source" worksheet.

With a worksheet named "Destination" in place, the data from columns A to M in the "Source" worksheet is automatically added to the Destination worksheet for every unique account number.

The totals for Fresh and Smart - pieces and amounts - are totaled for each account number in columns N, O, P and Q.

Regards


Report •

#6
July 28, 2010 at 21:55:48
Dear Humar -

IT WORKED!!!!!!!

Oh my goodness I almost started to cry, I was so excited! Thank you so very very much. You have no idea how truly grateful I am. You are simply amazing!

Thank you so very very much. This is so fantastic!

Many thanks - T :)


Report •

#7
July 28, 2010 at 22:05:49
Hi Humar -

I am already back and I think I know what happened. I went back and re-read your replys and it said this is a one time macro, but this is something I would need to use daily. How can I do that?

Please advise

Thanks so much again - T :)


Report •

#8
July 29, 2010 at 04:07:26
Hi,

You need to say how it's going to be used.

Right at the end of response #1 I asked that question.

There are two main possibilities that I can see:
1. The Source data is added to - new rows of data are added, and when re-run, the macro adds any new account numbers and adds new total piece counts etc. to the existing totals.
(In practice, the easiest way to do this is for the macro to delete any existing data on the Destination worksheet, and run the whole thing from the start).

2. The source data is replaced by new data, and the new data is added to the existing data on the Destination worksheet.

The other issue (for option 2) is if any previously entered data is corrected, how is that handled. If it's a rare event, then a manual correction of the data on the Destination worksheet is probably OK.

Also you can add a backup and save to the macro.
The macro starts by saving a copy of the existing workbook (in the same directory is easiest) and with the date attached to the filename.
Then at the end it saves the updated workbook with it's original name (no date added).

Anyway, let me know how you use it / what you want it to do.

So glad to hear it's working, at least as a one-off.
In the interim, you can always add data to the Source worksheet, delete the data from the Destination worksheet and re-run the macro.

Regards


Report •

#9
July 29, 2010 at 17:04:08
Hi Humar -

Very sorry for the delay. Extremely hectic day.

Here is how it will work.

Everyday we receive customer orders for the following day. So the information from the source data will change daily.

On a previous macro I worked with daily on this project, it was save to it's own workbook. I would open that workbook, which showed nothing but the macro was in the VBA section and then open the daily order report. Both workbooks had to be opened and in the order report, I would run the macro and it would convert all the data in the template I needed to a whole new work book. But honestly, I like your deal better with the information going on the destination tab. I think it will be less confusing for people. Would something like that be easier for you?

I will go home tonight and try what you suggested and get back with you later.

Again thanks so much for all your help. I have gone through and read your script trying to help me understand what is happening. I took a day macro class last year. While the instructor said I was doing great, it felt like I was learning a foreign language and was completely lost. I am hoping to be able to enroll in a macro class at the community college this fall. Again thanks so much for all your help and insight.


Report •

#10
July 29, 2010 at 21:03:38
Hi Humar -

Okay, well I think I am making progress.

I got the macro to run several times this evening, but the button doe not work. I have to go into VBA and run. Once I go back to Excel spreadsheet, the information is on the destination tab. There are also no headers on the destination page, but that is an easy fix. Is there anyway to keep the macro separate from the source workbook and just run it from there?

Again thanks for all your help. It's been amazing! Have a good night. - T :)


Report •


Ask Question