Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Tough excel math formula

Posted on 2011-03-19
13
Medium Priority
?
335 Views
Last Modified: 2012-06-21
This should be simple, but it’s driving me nuts…

I have a spreadsheet that looks like this:

Part Number      Lot Number      Quantity
020-044      Goods      8
020-044      CAH217      -5
020-044      VEG076      1
020-044 Total      4      
020-045      Goods      6
020-045      CNS015      -4
020-045      SPG015      -1
020-045      CAH217      -1
020-045      VEG076      1
020-045 Total      1      
020-056      Goods      16
020-056      FLX017      -4
020-056      CAS037      -6
020-056      LAS006      -3
020-056      FLP476      -1
020-056 Total      2


What I need is a formula that will go through the spread sheet and copy the total of each part number to the cel next to the “goods” line for each entry. For instance, part #020-044 has a total of 4 units. I simply need that “4” copied to cel D2. The end result should look like this:

Part Number      Lot Number      Quantity      Total
020-044      Goods      8      4
020-044      CAH217      -5      
020-044      VEG076      1      
020-044 Total            4      
020-045      Goods      6      1
020-045      CNS015      -4      
020-045      SPG015      -1      
020-045      CAH217      -1      
020-045      VEG076      1      
020-045 Total      1      2
020-056      Goods      16      
020-056      FLX017      -4      
020-056      CAS037      -6      
020-056      LAS006      -3      
020-056      FLP476      -1      
020-056 Total      2      


There are over 50,000 part numbers on this spreadsheet. Some have one or two lot numbers; some have over a dozen, so doing this manually is not an option. I am using MS Excel 2010.
0
Comment
Question by:dan9591
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 
LVL 13

Expert Comment

by:gamarrojgq
ID: 35172047
Hi,

You can use a Macro to do that, in you excel spreadshee, add a module in the visual Basic Editor and copy the macro attached, then assign a Shortcut to the macro and run it, it would what you want and will give you a message when finish.

Hope that helps
Sub CopyTotal()
        
    Dim lngLastRow As Long
    Dim lngRow As Long
    Dim lngCopyToCell As Long
    Dim objRange As Range
    Dim strPartNumber As String
    
    'Search for any entry, by searching backwards by Rows.
    lngLastRow = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

    'Get the Range
    Set objRange = ActiveSheet.Range("A2:A" & CStr(lngLastRow))
    
    'Copy the Totals
    strPartNumber = ""
    lngCopyToCell = 2
    For lngRow = 1 To lngLastRow
        'If found the Total copy to
        If strPartNumber <> Trim(objRange(lngRow, 1).Value) Then
            If strPartNumber <> "" Then
                objRange(lngCopyToCell, 4).Value = objRange(lngRow - 1, 3).Value
            End If
            strPartNumber = Trim(objRange(lngRow, 1).Value)
            lngCopyToCell = lngRow
        End If
    Next

    MsgBox "Totals Copied."

End Sub

Open in new window

0
 
LVL 42

Expert Comment

by:dlmille
ID: 35172082
Here, you can do this with a simple SUMIFS formula:  Put this in column D, on the Goods line and see:

=SUMIFS($C$2:$C$17,$A$2:$A$17,$A2,$B$2:$B$17,"<>Total")

See attached.

Dave
Goods-Total-r1.xlsx
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35172088
This is a bit better - so paste the formula and copy down...

=IF(B2="Goods",SUMIFS($C$2:$C$17,$A$2:$A$17,$A2,$B$2:$B$17,"<>Total"),"")

see attached,

Dave
Goods-Total-r2.xlsx
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35172103
Not for points.

Since I had already worked on it. Here is an alternative.

This goes in cell D2.

=SUMPRODUCT(($A$2:$A$17=A2)*($B$2:$B$17="Total")*(C2:C17))

This in D6

=SUMPRODUCT(($A$2:$A$17=A6)*($B$2:$B$17="Total")*($C$2:$C$17))

and this in D12

=SUMPRODUCT(($A$2:$A$17=A12)*($B$2:$B$17="Total")*($C$2:$C$17))

Sid
0
 

Author Comment

by:dan9591
ID: 35172177
Sorry, maybe I wasn’t too clear…

1.      I do not want to re-calculate anything. The totals are already calculated. I simply need to copy the total from one cel to another.
2.      There are thousands of part numbers, each with up to 20 lot numbers. It is not feasible to paste a formula for each part number manually.
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35172188
You only need to copy and paste once.  for all your thousands of part numbers.  I've done this with > 100,000 items before and no issues.

The formula works for an infinite variety of part numbers...  here, this is more generic:

=IF(B2="Goods",SUMIFS($C:$C,$A:$A,$A,$B:$B,"<>Total"),"")

Now you copy that down for as much data as you have - and if its the entire set of rows it will work.  For several thousand, its not intensive...

Otherwise, the macro is your solution and maintaining it should be the only concern...

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35172191
PS - You copy this formula down (not individually for each part number)

:)

Cheers,

Dave
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 35172201
....that still involves re-summing a total that already exists......

This version will just lookup the next Total row and give you the relevant number

=IF(B2="Goods",VLOOKUP(D$1,B3:C$50000,2,0),"")

Place that formula in D2 and copy down

regards, barry
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35172212
Incorrect.  The formula I provided does not resum any totals.

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 35172219
Ah - I see what you mean by re-summing, in that it just needs to be looked up.  I responded too quickly :)

Nice and tight, barry.

Dave
0
 

Author Comment

by:dan9591
ID: 35282187
I appreciate the help, but I think I’m missing something here… I’ve tried each formula, but it seems to die after the first few entries. I attached the entire spreadsheet and yes, I’ve tried with and without the subtotals… also, keep in mind that the other column on the spreadsheet need to stay there…
mmi.xlsx
0
 
LVL 42

Accepted Solution

by:
dlmille earned 400 total points
ID: 35283584
Well:

1.  The format of your file is different than what you originally suggested (re: the Word TOTAL is not in column B)
2.  The range is limited to only the first 17 rows in column C.

Let's open that up and correct for format (now not counting BLANK rows in column B which seems to align with the Total lines).

Attached has this corrected in column C


Dave
mmi-r1.xlsx
0
 

Author Comment

by:dan9591
ID: 35284310
See Dave, I told you I was missing something... Turns out it's my mind...

You nailed it! Thanks a bunch!!!
0

Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question