Calculations with multiple criteria based on multiple and duplicate data

V

VBBlindman

New Member
Joined
Sep 21, 2012
Messages
22
Hi, I'm new to this forum and this is my first post, however I have been reading threads from here for many months and finally got the nerve to try and join in. I may have gone way overboard with what I'm asking help with here, but I'm at my wits end and feel like a very stupid person.

You can skip this next paragraph if you want, I wanted to tell you that I have tried to do this on my own and what I have been doing to get to that end. I have spent the last past 3 or more months reading Excel 2002 VBA Programmers Reference, Access 97 Bible, Excel Version 5, Visual Basic 6, hundreds (perhaps thousands) of google searches, countless hours at Barnes & Nobles and at the public library reading books I can no longer remember. I've typed a ba-zillion cell formulas (okay maybe not a ba-zillion), many that even worked, but in just about every case none would work when I tried to write them into a VB module sheet as a sub() procedure. Any code or procedures I copied from the Web would work, but as soon as I made a modification (minor from what I thought) to fit my needs it would bomb.

I will attemp to explain what I'm trying to accomplish.

Below is a sample spreadsheet (provided I did the HTML maker correctly) with a small sample of data with an area that I'm trying to populate with results from what I think are simple straight forward calulations. Today I do this with pivot tables, cell formulas and brute force, but it takes weeks because the actual data I'm dealing with is 79,000 items and 750,000 lines of cost data.

The desired result is to write a VB sub macro titled ImpactCalc() that fills in cells E9 thru P11 with the cost impact based on the information in the table titled "Data." The calculation is done by comparing the "Item A" number and "Month A" to what is in "Data," when a match is found take the corresponding "Last Cost" minus the "New Cost" times the "Qty" for that month. When there is more than one set of data for a month for the item it must temporarily store the previous calculation result to be subsequently added to the next additional calculation for the next impact. However, the next calculation for the duplicate month does not use the value from "Last Cost", it takes the "New Cost" from the previous calculation minus the next new cost for the duplicate months data times that next corresponding qty. It keeps looping through and incrementing the new cost and qty for each duplicate month until the next different month. It again temporily saves the last new cost used for the start of the next different months calculations and loops through until all months are filled in for that "Item A" then resets to start the next "Item A."

Example for item 32-99954-00, Months 1 and 2 would calc to zero as there is no 1 or 2 in "Data." Month 3 however has duplicates so the first calc is (881 - 994) x 60, then (994 - 1,015) x 72, then (1,015 - 976) x 72 all added together and placed in cell G9. Month 4 is then (976 - 944) x 72 and placed in cell H9. There is no month 5 data so I9 would have zero placed in it. Month 6 would be (944 - 999) x 38 and value placed in cell J9, and so on. Once month 12 is filled in, reset and do the calculations for the next "Item A" using its "Last Cost" to start with.

Hopefully I've given enough to make sense and not to much to confuse.

Any assistance it truly appreciated and if this is way too much how about a recommendation on reference material I can go study.

Excel 2007
ABCDEFGHIJKLMNOP
1ACDEFGHIJKLMNOP
2
3
4
5
6
7Month A
88Item ALast Cost123456789101112
9932-99954-00881
101032-99954-0119
111132-99954-0231
12
13
14
15
16
17Data
1818ItemNew CostQtyMonth
191932-99954-00994 603
202032-99954-001,015 723
212132-99954-00976 723Cell formula testing examples
222232-99954-00944 72460direct range references
232332-99954-00999 38660Named ranges
242432-99954-00987 7211
252532-99954-00996 7211Find duplicates
262632-99954-00996 481132-99954-00996
272732-99954-0124 3632-99954-0124
282832-99954-0119 15732-99954-0119
292932-99954-0119 10732-99954-0119
303032-99954-0120 13832-99954-0120
313132-99954-0120 79
323232-99954-0121 1711
333332-99954-0231 181
343432-99954-0231 202
353532-99954-0231 164
363632-99954-0232 144
373732-99954-0233 166
383832-99954-0246 116
39

<COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
</TBODY>
Data​

Array Formulas
CellFormula
N22{=INDEX(E19:E38,MATCH(1,(C9=C19:C38)*(G8=F19:F38),0))}
N23{=INDEX(Qty,MATCH(1,(C9=Item)*(G8=Month),0))}
O26{=IF(COUNTIF(N$26:N26,N26)=1,INDEX($D$26:$D$30,MATCH($N26,$C$26:$C$30,0)),INDEX($D$26:$D$30,SMALL(IF($C$26:$C$30=N26,ROW($C$26:$C$30)-ROW($C$25),""),COUNTIF(N$26:N26,N26))))}
O27{=IF(COUNTIF(N$26:N27,N27)=1,INDEX($D$26:$D$30,MATCH($N27,$C$26:$C$30,0)),INDEX($D$26:$D$30,SMALL(IF($C$26:$C$30=N27,ROW($C$26:$C$30)-ROW($C$25),""),COUNTIF(N$26:N27,N27))))}
O28{=IF(COUNTIF(N$26:N28,N28)=1,INDEX($D$26:$D$30,MATCH($N28,$C$26:$C$30,0)),INDEX($D$26:$D$30,SMALL(IF($C$26:$C$30=N28,ROW($C$26:$C$30)-ROW($C$25),""),COUNTIF(N$26:N28,N28))))}
O29{=IF(COUNTIF(N$26:N29,N29)=1,INDEX($D$26:$D$30,MATCH($N29,$C$26:$C$30,0)),INDEX($D$26:$D$30,SMALL(IF($C$26:$C$30=N29,ROW($C$26:$C$30)-ROW($C$25),""),COUNTIF(N$26:N29,N29))))}
O30{=IF(COUNTIF(N$26:N30,N30)=1,INDEX($D$26:$D$30,MATCH($N30,$C$26:$C$30,0)),INDEX($D$26:$D$30,SMALL(IF($C$26:$C$30=N30,ROW($C$26:$C$30)-ROW($C$25),""),COUNTIF(N$26:N30,N30))))}

<THEAD>
</THEAD><TBODY>
</TBODY>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<TBODY>
</TBODY>

Worksheet Defined Names
NameRefers To
Data!Item=Data!$C$19:$C$38
Data!Month=Data!$F$19:$F$38
Data!Qty=Data!$E$19:$E$38

<THEAD>
</THEAD><TBODY>
</TBODY>

<TBODY>
</TBODY>
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
Oh Boy VBBlindman, you are asking quite a lot at first. I would love to help, but I'm having quite a time understanding the spreadsheet and the lengthy iterative process. Can you break it down into multiple steps? Can you provide some code you have and tell us where you are having problems? You're showing formulas, but you're asking for VB code to solve it. I'm expecting that you want the VB code to provide most, if not all, of the totals! Maybe if I had the workbook I would see it more clearly.

Jeff
 
Upvote 0
Hi Jeff, yep, I thought I may have gone way too big for a first time. Now that I see what the spreadsheet example I had looks like after using the HTML maker I can understand why it would be hard to follow. Does this forum have a way to attach downloadable examples, I haven't been able to find that option.

Yes, I've been trying to write code to calculate all the totals and fill in all the cell values. I've found various formulas that do certain parts and thought I could easily make some modifications, combine them, and put them into a macro. I've found some nifty formulas using Index, Match, Countif, etc. but once I try puting them in a module, puff. I'd managed to get some progress once I found worksheetfunction. and application. but range and embedded if's seem to have a uniqueness I haven't been able to crack to tie things together, let alone when I try putting the whole thing into a loop.


To bite off a small piece to start, I tried just writing the code for the formula that's in cell O26 thru O30 above and just store the value in a variable, say y. It doesn't seem to like the IF statement within the SMALL function when you write it in a module, or I should say I just don't know how to write it to work. It gives, compile error, expected expression, but I suspect that's just the first issue it has.

Here's what I tried for the one in O28:
Code: 
Sub FindDup()
Dim y As Integer

If Application.CountIf(Range("N$26:N28"), Range("N28")) = 1 Then
y = Application.Index(Range("$D$26:$D$30"), Application.Match(Range("$N28"), Range("$C$26:$C$30"), 0))
Else
y = application.index(range("$D$26:$D$30"), application.small( _
If Range("$C$26:$C$30") = Range("N28") Then
Application.Row (Range("$C$26:$C$30")) - Application.Row(Range("$C$25"))
Else
""""_
, application.CountIf(Range("N$26:N28"), Range("N28"))))
End If

Range("E14") = y ' this is just to put value some where that I can see it on the spreadsheet

End Sub
 
Upvote 0
For the sample data shown could you calculate, manually if necessary, the values that should appear in E9:P11 and post those?
 
Upvote 0
Here it is, I broke in up into three pieces when using the HTML maker, hopefully it will fit on the screen better than what is shown above and be more readable.


Excel 2007
CDEFGH
7Month A
8Item ALast Cost1234
932-99954-008810.000.00(5,484.00)2,304.00
1032-99954-01190.000.000.000.00
1132-99954-02310.000.000.00(14.00)
Data



Excel 2007
IJKL
7Month A
85678
90.00(2,090.00)0.000.00
100.00(15.00)75.00(13.00)
110.00(159.00)0.000.00
Data



Excel 2007
MNOP
7Month A
89101112
90.000.00216.000.00
100.000.00(17.00)0.00
110.000.000.000.00
Data
 
Upvote 0
Here's just the Data table so maybe it will display cleaner and more understandable.

Excel 2007
C
D
E
F
17
Data
18
Item
New Cost
Qty
Month
19
32-99954-00
994
60
3
20
32-99954-00
1,015
72
3
21
32-99954-00
976
72
3
22
32-99954-00
944
72
4
23
32-99954-00
999
38
6
24
32-99954-00
987
72
11
25
32-99954-00
996
72
11
26
32-99954-00
996
48
11
27
32-99954-01
24
3
6
28
32-99954-01
19
15
7
29
32-99954-01
19
10
7
30
32-99954-01
20
13
8
31
32-99954-01
20
7
9
32
32-99954-01
21
17
11
33
32-99954-02
31
18
1
34
32-99954-02
31
20
2
35
32-99954-02
31
16
4
36
32-99954-02
32
14
4
37
32-99954-02
33
16
6
38
32-99954-02
46
11
6

<TBODY>
</TBODY>
Data​
 
Upvote 0
The following code assumes that the data is sorted by Item and Month as your sample appears to be.
Give it a try in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> VBBlindman()<br>    <SPAN style="color:#00007F">Dim</SPAN> aItems, aData, aResults<br>    <SPAN style="color:#00007F">Dim</SPAN> LastCost <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN>, tmp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> ItemRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, DataRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> currMnth <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, DataRws <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, ItemRws <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> bItemFound <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br>    <br>    aItems = Range("C9", Range("D8").End(xlDown)).Value<br>    ItemRws = <SPAN style="color:#00007F">UBound</SPAN>(aItems, 1)<br>    aData = Range("C19", Range("C" & Rows.Count).End(xlUp).Offset(1)).Resize(, 4).Value<br>    DataRws = <SPAN style="color:#00007F">UBound</SPAN>(aData, 1)<br>    <SPAN style="color:#00007F">ReDim</SPAN> aResults(1 <SPAN style="color:#00007F">To</SPAN> ItemRws, 1 <SPAN style="color:#00007F">To</SPAN> 12) <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Single</SPAN><br>    DataRow = 1<br>    currMnth = 1<br>    <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> DataRow < DataRws And ItemRow <= ItemRws<br>        <SPAN style="color:#00007F">If</SPAN> bItemFound <SPAN style="color:#00007F">Then</SPAN><br>            <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> aData(DataRow, 4) = currMnth And DataRow < DataRws<br>                tmp = tmp + (LastCost - aData(DataRow, 2)) * aData(DataRow, 3)<br>                LastCost = aData(DataRow, 2)<br>                DataRow = DataRow + 1<br>            <SPAN style="color:#00007F">Loop</SPAN><br>            aResults(ItemRow, currMnth) = tmp<br>            tmp = 0<br>            <SPAN style="color:#00007F">If</SPAN> currMnth = 12 <SPAN style="color:#00007F">Then</SPAN><br>                currMnth = 1<br>                bItemFound = <SPAN style="color:#00007F">False</SPAN><br>                ItemRow = ItemRow + 1<br>            <SPAN style="color:#00007F">Else</SPAN><br>                currMnth = currMnth + 1<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Else</SPAN><br>            ItemRow = 1<br>            <SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">Until</SPAN> bItemFound <SPAN style="color:#00007F">Or</SPAN> ItemRow > ItemRws<br>                <SPAN style="color:#00007F">If</SPAN> aItems(ItemRow, 1) = aData(DataRow, 1) <SPAN style="color:#00007F">Then</SPAN><br>                    bItemFound = <SPAN style="color:#00007F">True</SPAN><br>                    LastCost = aItems(ItemRow, 2)<br>                <SPAN style="color:#00007F">Else</SPAN><br>                    ItemRow = ItemRow + 1<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">Loop</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Loop</SPAN><br>    Range("E9").Resize(ItemRws, 12).Value = aResults<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>
 
Last edited:
Upvote 0
Peter,

This is beautiful!!! Not exactly sure how it's working, I will be going through this line by line to understand that, but thank you, thank you, and thank you, this is a great birthday present. YOU ARE THE MAN SIR!!!!!
 
Upvote 0
Glad it worked for you, and happy birthday!! (y)
 
Upvote 0
You may choose to stick with the previous solution, but having considered this some more, it could be done ..
a) with the following formula, copied across and down ..

Excel Workbook
CDEFGHIJKLMNOP
8Item ALast Cost123456789101112
932-99954-008810.000.00(5,484.00)2,304.000.00(2,090.00)0.000.000.000.00216.000.00
1032-99954-01190.000.000.000.000.00(15.00)75.00(13.00)0.000.00(17.00)0.00
1132-99954-02310.000.000.00(14.00)0.00(159.00)0.000.000.000.000.000.00
12
13
14
15
16
17Data
18ItemNew CostQtyMonth
1932-99954-00994603
2032-99954-001,015723
2132-99954-00976723
2232-99954-00944724
2332-99954-00999386
2432-99954-009877211
2532-99954-009967211
2632-99954-009964811
2732-99954-012436
2832-99954-0119157
2932-99954-0119107
3032-99954-0120138
3132-99954-012079
3232-99954-01211711
3332-99954-0231181
3432-99954-0231202
3532-99954-0231164
3632-99954-0232144
3732-99954-0233166
3832-99954-0246116
39
VBBlindman (2)



..or
b) using vba with this formula to establish the values.

Note that although this code is shorter, it may not be quicker given the size of your data & the formula calculation time required. If you feel like it, test & see but I would suggest testing on considerably less than "79,000 items and 750,000 lines of cost data" to start with.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> VBBlindman_2()<br>    <SPAN style="color:#00007F">Dim</SPAN> lrItems <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lrData <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">Const</SPAN> frmlaBase <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "=SUMPRODUCT(--($C$20:$C$^=$C9),--($C$19:$C$#=$C9),--($F$20:$F$^=E$8)," _<br>                                & "($E$20:$E$^)*($D$19:$D$#-$D$20:$D$^))+" _<br>                                & "IF(VLOOKUP($C9,$C$19:$F$^,4,0)=E$8," _<br>                                & "($D9-VLOOKUP($C9,$C$19:$F$^,2,0))*VLOOKUP($C9,$C$19:$F$^,3,0))"<br>    <br>    lrItems = Range("C8").End(xlDown).Row<br>    lrData = Range("C18").End(xlDown).Row<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range("E9:P" & lrItems)<br>        .Formula = Replace(Replace(frmlaBase, "^", lrData, 1, -1, 1), "#", lrData - 1, 1, -1, 1)<br>        .Value = .Value<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Excel Pivot table Time Values is not populating same as Source Sheet
Replies
1
Views
379
awoohaw
awoohaw
K
  • Question
conditional formatting question
Replies
2
Views
290
kerm007
K
J
  • Question
Sumproduct with different array sizes
2
Replies
19
Views
540
jkj115
J
S
  • Question
Date and Time difference not calculating over 30 days
Replies
5
Views
409
Stef9910
S
M
  • Solved
VBA Copy & Paste Values onto itself based on Criteria
Replies
4
Views
306
mst3kr
M

Forum statistics

Threads
1,216,743
Messages
6,132,466
Members
449,729
Latest member
davelevnt

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top