This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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.

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.

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

=SUMIFS($C$2:$C$17,$A$2:$A

See attached.

Dave

Goods-Total-r1.xlsx

=IF(B2="Goods",SUMIFS($C$2

see attached,

Dave

Goods-Total-r2.xlsx

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

This goes in cell D2.

=SUMPRODUCT(($A$2:$A$17=A2

This in D6

=SUMPRODUCT(($A$2:$A$17=A6

and this in D12

=SUMPRODUCT(($A$2:$A$17=A1

Sid

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.

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

=IF(B2="Goods",SUMIFS($C:$

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

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

=IF(B2="Goods",VLOOKUP(D$1

Place that formula in D2 and copy down

regards, barry

Nice and tight, barry.

Dave

mmi.xlsx

You nailed it! Thanks a bunch!!!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

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