if the prices keep changing, how do i add the different prices for different days is there any other way to fix this?

acarwreck

New Member
Joined
Mar 13, 2024
Messages
18
Office Version
  1. 2019
Platform
  1. Windows
PRICEPRICEDate:12345
APPLE4525QTY1246
ORANGE3035QTY11
BRICK100125QTY2
MOON150140QTY472
FERRARI25500QTY35
DAY TOTAL8451951801080695
 
Those two red comment are contradictory. You are either using Microsoft 365 or you are using Excel 2019. They are different products.

The final red statement above also contradicts your earlier assertion..

It also contradicts your user profile
View attachment 109513

What are you actually using? If you click File -> Account this is what I get.

View attachment 109512

What do you get?
this is what i get


EDIT:

after googling further
"The FILTER function has never been available in Excel in Office 2019. It was introduced in the subscription version Microsoft 365, and the first 'permanent license' version in which it is available is Office 2021."

Is there an alternative filter function for 2019 version?
 

Attachments

  • Screenshot (8).png
    Screenshot (8).png
    135.1 KB · Views: 8
Last edited:
Upvote 0

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
"The FILTER function has never been available in Excel in Office 2019.
Exactly! I used the FILTER function because your profile showed Microsoft 365 and when I specifically asked, you confirmed that you were using 365. .. but you are not! :(
 
Upvote 0
Exactly! I used the FILTER function because your profile showed Microsoft 365 and when I specifically asked, you confirmed that you were using 365. .. but you are not! :(
well **** myself and microsoft for this blunder, im sorry about i this. could you suggest an alternative?
 
Upvote 0
Is there an alternative filter function for 2019 version?
2019 does not have a ready-made alternative to the later version FILTER function.

could you suggest an alternative?
You could try a user-defined vba function like below. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy across. Make the range for the price change data ($BA2:$BC100 in my formulas) plenty big enough to allow for any price changes that might get entered in the future.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

VBA Code:
Function DayTot(rItems As Range, rDayQty As Range, dDayDate As Date, rPriceChanges As Range) As Double
  Dim vItems As Variant, vQty As Variant, vPrices As Variant
  Dim i As Long, j As Long, k As Long
  
  vItems = rItems.Value
  vQty = rDayQty.Value
  vPrices = rPriceChanges.Value
  For i = 1 To UBound(vItems)
    j = 1
    Do
      If vPrices(j, 1) = vItems(i, 1) And vPrices(j, 2) <= dDayDate Then k = j
      j = j + 1
    Loop Until vPrices(j - 1, 2) > dDayDate Or j > UBound(vPrices)
    DayTot = DayTot + vQty(i, 1) * vPrices(k, 3)
  Next i
End Function

acarwreck.xlsm
EFGHIJKLMAZBABBBC
1ItemDatePrice
2APPLE1/01/202450
3ORANGE1/01/202430
4POTATOES1/01/202454
5SPINACH1/01/202432
6EGGPLANT1/01/202412
7SPINACH2/01/202433
8APPLE3/01/202455
9APPLE5/01/202457
10
11
12
13ITEMDATE :12345
14APPLEQTY 5551
15ORANGEQTY 3231
16POTATOESQTY 41
17SPINACHQTY 5241
18EGGPLANTQTY 521
19DAY TOTAL2934306431275186
20
Changing price (2)
Cell Formulas
RangeFormula
H19:L19H19=DayTot($E14:$E18,H14:H18,H13,$BA2:$BC100)
 
Upvote 0
2019 does not have a ready-made alternative to the later version FILTER function.


You could try a user-defined vba function like below. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy across. Make the range for the price change data ($BA2:$BC100 in my formulas) plenty big enough to allow for any price changes that might get entered in the future.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

VBA Code:
Function DayTot(rItems As Range, rDayQty As Range, dDayDate As Date, rPriceChanges As Range) As Double
  Dim vItems As Variant, vQty As Variant, vPrices As Variant
  Dim i As Long, j As Long, k As Long
 
  vItems = rItems.Value
  vQty = rDayQty.Value
  vPrices = rPriceChanges.Value
  For i = 1 To UBound(vItems)
    j = 1
    Do
      If vPrices(j, 1) = vItems(i, 1) And vPrices(j, 2) <= dDayDate Then k = j
      j = j + 1
    Loop Until vPrices(j - 1, 2) > dDayDate Or j > UBound(vPrices)
    DayTot = DayTot + vQty(i, 1) * vPrices(k, 3)
  Next i
End Function

acarwreck.xlsm
EFGHIJKLMAZBABBBC
1ItemDatePrice
2APPLE1/01/202450
3ORANGE1/01/202430
4POTATOES1/01/202454
5SPINACH1/01/202432
6EGGPLANT1/01/202412
7SPINACH2/01/202433
8APPLE3/01/202455
9APPLE5/01/202457
10
11
12
13ITEMDATE :12345
14APPLEQTY 5551
15ORANGEQTY 3231
16POTATOESQTY 41
17SPINACHQTY 5241
18EGGPLANTQTY 521
19DAY TOTAL2934306431275186
20
Changing price (2)
Cell Formulas
RangeFormula
H19:L19H19=DayTot($E14:$E18,H14:H18,H13,$BA2:$BC100)
Hey this worked. thank you so much for your help. Have a great day :)
 
Upvote 0
You're welcome. Thanks for letting us know the result.
 
Upvote 0
2019 does not have a ready-made alternative to the later version FILTER function.


You could try a user-defined vba function like below. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy across. Make the range for the price change data ($BA2:$BC100 in my formulas) plenty big enough to allow for any price changes that might get entered in the future.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

VBA Code:
Function DayTot(rItems As Range, rDayQty As Range, dDayDate As Date, rPriceChanges As Range) As Double
  Dim vItems As Variant, vQty As Variant, vPrices As Variant
  Dim i As Long, j As Long, k As Long
 
  vItems = rItems.Value
  vQty = rDayQty.Value
  vPrices = rPriceChanges.Value
  For i = 1 To UBound(vItems)
    j = 1
    Do
      If vPrices(j, 1) = vItems(i, 1) And vPrices(j, 2) <= dDayDate Then k = j
      j = j + 1
    Loop Until vPrices(j - 1, 2) > dDayDate Or j > UBound(vPrices)
    DayTot = DayTot + vQty(i, 1) * vPrices(k, 3)
  Next i
End Function

acarwreck.xlsm
EFGHIJKLMAZBABBBC
1ItemDatePrice
2APPLE1/01/202450
3ORANGE1/01/202430
4POTATOES1/01/202454
5SPINACH1/01/202432
6EGGPLANT1/01/202412
7SPINACH2/01/202433
8APPLE3/01/202455
9APPLE5/01/202457
10
11
12
13ITEMDATE :12345
14APPLEQTY 5551
15ORANGEQTY 3231
16POTATOESQTY 41
17SPINACHQTY 5241
18EGGPLANTQTY 521
19DAY TOTAL2934306431275186
20
Changing price (2)
Cell Formulas
RangeFormula
H19:L19H19=DayTot($E14:$E18,H14:H18,H13,$BA2:$BC100)
Register1
DEFGHIJKLAYAZBABBBCBDBEBFBG
1PRICES
2ItemDate4529245293452944529545296
3APPLE505557
4ORANGE30
5POTATOES54
6SPINACH3233
7EGGPLANT12
8
9
10
11
12
13SALES
14ITEMDATE :4529245293452944529545296
15APPLEQTY 5551
16ORANGEQTY 3231
17POTATOESQTY 41
18SPINACHQTY 5241
19EGGPLANTQTY 521
20DAY TOTAL2934306402250178
21
22
23
24
25
Sheet1
Cell Formulas
RangeFormula
H20:L20H20=DayTot($E15:$E19,H15:H19,H14,$AZ3:$BB101)


Hey i have made a small change here previously you had put the change of prices column wise, i want to put it row-wise as shown above, so could you make necessary changes to the formula that you have given?
 
Upvote 0
Hey i have made a small change
Is this the last one?

Assuming the values in col AZ are in the identical order to the values in col E then try this.
Again make sure the range is big enough (mine goes to col CZ)

VBA Code:
Function DayTot(rItems As Range, rDayQty As Range, dDayDate As Date, rPriceChanges As Range) As Double
  Dim vItems As Variant, vQty As Variant, vPrices As Variant
  Dim i As Long, j As Long, k As Long
  
  vItems = rItems.Value
  vQty = rDayQty.Value
  vPrices = rPriceChanges.Value
  For i = 1 To UBound(vItems)
    j = 3
    Do
      If vPrices(1, j) <= dDayDate And vPrices(i + 1, j) <> "" Then k = j
      j = j + 1
    Loop Until vPrices(1, j - 1) > dDayDate Or j > UBound(vPrices, 2)
    DayTot = DayTot + vQty(i, 1) * vPrices(i + 1, k)
  Next i
End Function

acarwreck.xlsm
EFGHIJKLMAZBABBBCBDBEBFBGBH
1PRICES
2ItemDate4529245293452944529545296
3APPLE505557
4ORANGE30
5POTATOES54
6SPINACH3233
7EGGPLANT12
8
9
10
11
12
13SALES
14ITEMDATE :4529245293452944529545296
15APPLEQTY 5551
16ORANGEQTY 3231
17POTATOESQTY 41
18SPINACHQTY 5241
19EGGPLANTQTY 521
20DAY TOTAL2934306431275186
Changing price (3)
Cell Formulas
RangeFormula
H20:L20H20=DayTot($E15:$E19,H15:H19,H14,$AZ2:$CZ7)
 
Upvote 0
Is this the last one?

Assuming the values in col AZ are in the identical order to the values in col E then try this.
Again make sure the range is big enough (mine goes to col CZ)

VBA Code:
Function DayTot(rItems As Range, rDayQty As Range, dDayDate As Date, rPriceChanges As Range) As Double
  Dim vItems As Variant, vQty As Variant, vPrices As Variant
  Dim i As Long, j As Long, k As Long
 
  vItems = rItems.Value
  vQty = rDayQty.Value
  vPrices = rPriceChanges.Value
  For i = 1 To UBound(vItems)
    j = 3
    Do
      If vPrices(1, j) <= dDayDate And vPrices(i + 1, j) <> "" Then k = j
      j = j + 1
    Loop Until vPrices(1, j - 1) > dDayDate Or j > UBound(vPrices, 2)
    DayTot = DayTot + vQty(i, 1) * vPrices(i + 1, k)
  Next i
End Function

acarwreck.xlsm
EFGHIJKLMAZBABBBCBDBEBFBGBH
1PRICES
2ItemDate4529245293452944529545296
3APPLE505557
4ORANGE30
5POTATOES54
6SPINACH3233
7EGGPLANT12
8
9
10
11
12
13SALES
14ITEMDATE :4529245293452944529545296
15APPLEQTY 5551
16ORANGEQTY 3231
17POTATOESQTY 41
18SPINACHQTY 5241
19EGGPLANTQTY 521
20DAY TOTAL2934306431275186
Changing price (3)
Cell Formulas
RangeFormula
H20:L20H20=DayTot($E15:$E19,H15:H19,H14,$AZ2:$CZ7)
HEY, thank you so much, i believe this has solved all my problems.
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,204
Members
453,022
Latest member
RobertV1609

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