Formula for Calculation of Covered Inv No & Covered Inv Date

[RAMU]

Dear Friends,

I want a help for a formula which will calculate covered invoice Nos & Inv dates based on Fruit & that particular Fruit's stock.

Giving an example below:

Sheet1

	A	B	C	D	E	F	G	H	I	J
1	FRUIT	INV DATE	DATE	QTY			FRUIT	STOCK	COVERING INVOICES	COVERING DATES
2	BANANA	30	17-09-17	200			BANANA	250	30, 28	17-09-17, 16-09-17
3	BANANA	28	16-09-17	300
4	BANANA	25	10-09-17	100
5	BANANA	20	08-09-17	500
6	BANANA	16	06-09-17	600
7	BANANA	12	04-09-17	400
8	BANANA	8	03-09-17	300
9	BANANA	5	02-09-17	150
10

<colgroup><col style="WIDTH: 30px; FONT-WEIGHT: bold"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 64px"><col style="WIDTH: 136px"><col style="WIDTH: 119px"></colgroup><tbody>
</tbody>

I want the formula for cell I2 & J2.

Similarly there are lot of data for other fruits also. And in main data sheet Fruits are sorted "A to Z" & dates are sorted " Newest to oldest" form.

Pls help.

Regards
RAMU

 

[Eric W]

I'm a little unsure what you mean. I believe that when you enter a fruit and a stock count, you want a list of which invoices, going top to bottom, are needed to cover that amount. First of all, depending on which version of Excel you have, concatenating a variable number of items is pretty tough. With Excel 2016 they added the TEXTJOIN function, which could work here. I tried building a formula using it, but got deeply involved in complicated calculations and multi-dimensional arrays. Without even knowing if you have TEXTJOIN, I decided to table that for now.

Instead, I built a UDF (user-defined function) that should do what you want. To install it, open a copy of your workbook. Press Alt-F11 to open the VBA editor. From the menu, click Insert > Module. On the sheet that opens, paste this code:

Public Function GetCovering(ByVal fruit As String, ByVal stock As Long, ByVal MyTable As Range, ByVal MyCol As Long) As String
Dim MyData As Variant, RunningTotal As Long, i As Long

    GetCovering = ""
    MyData = MyTable.Value
    RunningTotal = 0
    For i = 1 To UBound(MyData)
        If MyData(i, 1) = fruit Then
            If RunningTotal <= stock Then
                GetCovering = GetCovering & ", " & MyData(i, MyCol)
            Else
                Exit For
            End If
            RunningTotal = RunningTotal + MyData(i, 4)
        End If
    Next i
    GetCovering = Mid(GetCovering, 3)
    
End Function

Press Alt-Q to close the editor. Now on your Excel sheet, enter the formula as shown:

	A	B	C	D	E	F	G	H	I	J
1	FRUIT	INV DATE	DATE	QTY			FRUIT	STOCK	COVERING INVOICES	COVERING DATES
2	BANANA	30	9/17/2017	200			BANANA	250	30, 28	9/17/2017, 9/16/2017
3	BANANA	28	9/16/2017	300			Cherry	130	29, 27, 25	9/17/2017, 9/10/2017, 9/9/2017
4	BANANA	25	9/10/2017	100
5	BANANA	20	9/8/2017	500
6	BANANA	16	9/6/2017	600
7	BANANA	12	9/4/2017	400
8	BANANA	8	9/3/2017	300
9	BANANA	5	9/2/2017	150
10	Cherry	29	9/17/2017	75
11	Cherry	27	9/10/2017	50
12	Cherry	25	9/9/2017	45
13
14

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula I2 =getcovering(G2,H2,$A$2:$D$12,2) J2 =getcovering(G2,H2,$A$2:$D$12,3) I3 =getcovering(G3,H3,$A$2:$D$12,2) J3 =getcovering(G3,H3,$A$2:$D$12,3) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

For example, in the I2 formula, the first parameter is the fruit, the second is the stock amount you want, the third is the range of your table, and the fourth is what column you want returned, column 2 here is the INV DATE.

Let me know if this works for you.

 

[RAMU]

Dear Eric,

Yes, am not using 2016, & your thought is exactly what I want.

It's a gr8 attempt, but in my original workbook, there are few products which stock is 0, in that case it is also showing the newest doc no. as well date.

There the main data is in Sheet2, & the columns are as follows:

Sheet2

	A	B	C	D	E	F	G	H	I	J
1	PRODUCT	VENDOR	DATE	DOC.#	FULL DOC. NO.	REMARKS	2ND QTY2	DOC.QTY	RATE	AMOUNT

<tbody>
</tbody>

Pls note that from this sheet FULL DOC. NO. column will be counted for Covering Inv based on DOC.QTY, and DATE column will be counted for Covering Dates.


And in the "STOCK" sheet I want the formulas in F6 & G6. The columns are as follows:

	A	B	C	D	E	F	G
5	PRODUCT	OPENING	RECEIVED	ISSUED	STOCK	COVERING INV	COVERING DATES

<tbody>
</tbody>

Pls help.

Regards
RAMU

 

[Eric W]

If I understand correctly, this slight modification of the UDF should work:

Public Function GetCovering(ByVal fruit As String, ByVal stock As Long, ByVal MyTable As Range, ByVal MyCol As Long) As String
Dim MyData As Variant, RunningTotal As Long, i As Long

    GetCovering = ""
    MyData = MyTable.Value
    RunningTotal = 0
    For i = 1 To UBound(MyData)
        If MyData(i, 1) = fruit Then
            If RunningTotal < stock Then
                GetCovering = GetCovering & ", " & MyData(i, MyCol)
            Else
                Exit For
            End If
            RunningTotal = RunningTotal + MyData(i, 5)
        End If
    Next i
    GetCovering = Mid(GetCovering, 3)
    
End Function

The highlighted red 1 is the column where your fruit (Part #) is. The highlighted red 5 is where the quantity is. Change to match your sheet. The red < was <= and should fix your 0 issue.

Call it on your STOCK sheet like:

=GetCovering(A2,E2,Sheet2!$A$2:$J$12,3)

where the fields are defined as before. Note the larger range for the table. See how this works.

 

[RAMU]

WooooWWW!!!!!

Perfect, Hats off !!!
Let me know two issues.
First, why did you use "= Mid(GetCovering, 3)", what stands for 3 here ?
Second, what could be the formula for this matter if once I use 2016 ?

However, It's really a gr8 job done by you.

Regards
RAMU

 

[Eric W]

Every time we find a row we need to add, this line adds a comma then the value to concatenate:

GetCovering = GetCovering & ", " & MyData(i, MyCol)

And if you think about it, it will add a comma at the very start, before the first matching value. So the Mid(GetCovering, 3) skips past the first " ,". There are other ways, but that works pretty well. It doesn't cause an error if there are no matches.

And if you have TEXTJOIN? I'll play around a bit more to see if I can figure it out, but don't hold your breath! It would be pretty complicated.

Glad I could help.