Summarize (multiplying and adding) data according to three conditions

[michavon]

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

 

[James006]

@michavon

Hopefully ... some day in the future ... you will reappear on the Forum ... and share your comments ...

 

[xlhelp15]

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 ?

 

[michavon]

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

 

[michavon]

Hello, any luck with the formula?
Michaela

 

[michavon]

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

 

[James006]

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 ?

 

[James006]

Hi Michaela,

Just in case, below is an UDF to be tested

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

 

[James006]

Hi Michaela,

Have you had a moment to test the UDF ?

If yes, thanks for sharing your comments.

 

[James006]

@xlhelp15

Thanks for your Thanks