Feb 16 2021 10:44 AM
Hello all,
I need help with a formula and the subsequent calculation. Please see the attached workbook. This is a sample work book illustrating my dilemma.
Column B is the date of purchase
Column C is the vendor
Column D is the item
Column F returns a "1" each time a vendor has a new order date, and begins fresh for each new Company.
Column G returns a "1" only if the "Item 5" was purchased by the company. Item 5 is our company's best product offering.
Now, Column E returns the calculation of days since today's date and each date they purchased "Item 5"
What I desire is a new column that produces only the days since their most recent purchase of "Item 5", which for Company a would be 600. However as we move down the column, I would like it to find and calculate the number of days (or the difference) between the each time they purchased "Item 5".
So for Company A, the results would be: 600, 108
For Company B, the results would be: 928
For Company C, the results would be: 937, 70
For Company D, the results would be 250, 174, 238
I would like it to produce blank cells in between each values.
Thank you ahead of time!
My ultimate goal is to extrapolate data that calculates the average reorder rate for our vendors, and then checking to see if this number is decreasing (they are ordering more often) as time goes on.
Feb 16 2021 11:39 AM
Assuming that you have Excel in Office 2019 or Microsoft 365, enter the following array formula in H2, comfirmed with Ctrl+Shift+Enter:
=IF(G2=1,TEXTJOIN(", ",TRUE,IF($C$2:$C2=$C2,$E$2:$E2,"")),"")
Fill down.
Feb 16 2021 11:54 AM
Feb 16 2021 12:03 PM
Shouldn't H25 be 108?
Feb 16 2021 12:57 PM
Feb 16 2021 01:16 PM - edited Feb 16 2021 03:03 PM
Feb 16 2021 02:46 PM
Mar 25 2021 08:54 AM
You helped me so beautifully last time, I wondered if you might be able to please help again.
On this thread, you gave the formula for Column H to calculate the running difference in the values.
Now I would like a new Column "I" and a formula to return only the second value found for each grouping of rows, and otherwise return blank, essentially returning the second smallest number in each sequence and starting over for each new company.
So I25 would return 108
I43 would return 70
I53 would return 174
Thank you ahead of time!
Mar 25 2021 09:10 AM
Does this do what you want? In I2:
=IF(AND(G2=1,COUNTIFS(C$2:C2,C2,H$2:H2,">0")=2),H2,"")
Fill down.
Mar 25 2021 09:25 AM