Summarize (multiplying and adding) data according to three conditions

michavon

New Member
Joined
Jun 20, 2018
Messages
11
Hello,
I would be really grateful if somebody could help me with a formula which I believe should be a combination of INDEX MATCH and SUMPRODUCT but I may be wrong
I have three spreadsheets:
The first spreadsheet is Bill of Material (BOM) - products are in row and semi-products in column.
The second spreadsheet shows how many pallets of products I will dispatch on a particular day. Products are in row again, dates in column.
The third spreadsheet should be the outcome. Semi-products are in row, dates are in column. And I need to combine both spreadsheets into this one.
I need to know how many semi-products I will need to prepare for a dispatch day of a product
For example 18.1. I will dispatch 3 pallets of 12117. It means I will need to prepare for that day 102000 2712 pcs (=904*3) and 103000 2682 pcs (=894*3).
18.1. I will also dispatch 5 pallets of 12076. It means I will also need to prepare 101000 4245 pcs (=849*5) and 102000 4520 pcs (=904*5)
=> row in the third spreadsheet with date 18.1. will have these values: the cell F39 is 4245, the cell G39 is 7232 pcs (= 2712+4520) and the cell H39 is 2682
In other words, I need to return a value to the third spreadsheet according to semi-products, how many of them I have to prepare (to complete requested products) and what day.
I'm totally clueless here.

Thanks for any help
Michaela

Capture.PNG
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Hello,
I would be really grateful if somebody could help me with a formula which I believe should be a combination of INDEX MATCH and SUMPRODUCT but I may be wrong
I have three spreadsheets:
The first spreadsheet is Bill of Material (BOM) - products are in row and semi-products in column.
The second spreadsheet shows how many pallets of products I will dispatch on a particular day. Products are in row again, dates in column.
The third spreadsheet should be the outcome. Semi-products are in row, dates are in column. And I need to combine both spreadsheets into this one.
I need to know how many semi-products I will need to prepare for a dispatch day of a product
For example 18.1. I will dispatch 3 pallets of 12117. It means I will need to prepare for that day 102000 2712 pcs (=904*3) and 103000 2682 pcs (=894*3).
18.1. I will also dispatch 5 pallets of 12076. It means I will also need to prepare 101000 4245 pcs (=849*5) and 102000 4520 pcs (=904*5)
=> row in the third spreadsheet with date 18.1. will have these values: the cell F39 is 4245, the cell G39 is 7232 pcs (= 2712+4520) and the cell H39 is 2682
In other words, I need to return a value to the third spreadsheet according to semi-products, how many of them I have to prepare (to complete requested products) and what day.
I'm totally clueless here.

Thanks for any help
Michaela

View attachment 83083
Hi Michaela - I try my level best to assist you as much as possible. Would like to clarify before i proceed it further.

In your example above, I can see there is no connection between the Dates & BOM. Spreadsheet1 has BOM Vs. semi-products and Spreadsheet2 has Dates vs. semi-products in relation. Both SP1 & SP2 has semi-products in common but no relation with each other. We would need semi-products in spreadsheet3 to perform the matching right ?
 
Upvote 0
Hi Michaela - I try my level best to assist you as much as possible. Would like to clarify before i proceed it further.

In your example above, I can see there is no connection between the Dates & BOM. Spreadsheet1 has BOM Vs. semi-products and Spreadsheet2 has Dates vs. semi-products in relation. Both SP1 & SP2 has semi-products in common but no relation with each other. We would need semi-products in spreadsheet3 to perform the matching right ?
Yes, exactly what you said.
I need the quantity outcome on the semi-product level using BOM in SP1 and considering dates from SP2. The connection between spreadsheets is - SP1 to SP2 in products only, SP2 to SP3 in dates and SP3 to SP1 in semi-products.
The products are in the same columns in both spreadsheets if it helps (but the dates are not in the same rows).
Thank you so much for your help I've been struggling with that for three days.
M
 
Upvote 0
Hi Michaela - I try my level best to assist you as much as possible. Would like to clarify before i proceed it further.

In your example above, I can see there is no connection between the Dates & BOM. Spreadsheet1 has BOM Vs. semi-products and Spreadsheet2 has Dates vs. semi-products in relation. Both SP1 & SP2 has semi-products in common but no relation with each other. We would need semi-products in spreadsheet3 to perform the matching right ?
Hello xlhelp, any luck with the formula?
Michaela
 
Upvote 0
Hi Michaela,

If you had one or two semi-products for your BOM, a couple of formulas could have been designed.

But, quite normally, you can have several semi-products, which, in my opinion, to simplify things, would lead to designing an UDF.

Would a macro-based solution be fine with you ?
 
Upvote 0
Hi Michaela,

Just in case, below is an UDF to be tested

VBA Code:
Option Explicit

Function mypcs(rdate As Range, rprod As Range) As Long
'''  In Cell G39   you need to type in  =mypcs($A39,G$1)     '''''''''''''21/01/2023''''''
'https://www.mrexcel.com/board/threads/summarize-multiplying-and-adding-data-according-to-three-conditions.1227329/#post-6006624
Dim i As Long, j As Long, k As Long, temp As Long
Dim c As Range
Dim wsh1 As Worksheet, wsh2 As Worksheet, wsh3 As Worksheet
Application.Volatile
' To be adapted to your own situation for the 3 Spreadsheets
Set wsh1 = Sheet1: Set wsh2 = Sheet2: Set wsh3 = Sheet3
i = Application.Match(rdate, wsh2.Range("A1:A100"), 0)
    For Each c In wsh2.Range("B" & i & ":" & "S" & i)
        If c > 0 Then
            k = Application.Match(rprod, wsh1.Range("A1:A100"), 0)
            j = Application.Match(wsh2.Cells(1, c.Column), wsh1.Range("A1:S1"), 0)
            temp = temp + (c.Value * wsh1.Cells(k, j).Value)
        End If
    Next c
mypcs = temp
End Function

Hope this will help
 
Upvote 0
Hi Michaela,

Have you had a moment to test the UDF ?

If yes, thanks for sharing your comments.
 
Upvote 0

Forum statistics

Threads
1,223,909
Messages
6,175,312
Members
452,634
Latest member
cpostell

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