After Several Attempts I Know One Thing: I'm Stuck

[justtryingtolearn]

Hi Everyone,

If you’re reading this, thank you.
BACKGROUND
My role at my company requires of me to report the most currentprices stored in our databases. Sometimes I get requests to pull prices forproducts that first billed several quarters ago. The challenge with this is thefact that the price for a given product often changes from quarter to quarter.
So say for example I got asked to report the price onProduct X. When these requests come in, Product X is all that is known. So I’llgo to our databases, look up Product X, and I’ll see multiple prices spread outover multiple quarters like so (columns are named A-D to make referencingeasier later on):
[TABLE="width: 472"]
<tbody>[TR]
[TD="width: 156, bgcolor: transparent"]

Product (A)​

[/TD]
[TD="width: 156, bgcolor: transparent"]

Price (B)​

[/TD]
[TD="width: 162, bgcolor: transparent"]

The Quarter the Price was Originally Billed (C)​

[/TD]
[TD="width: 156, bgcolor: transparent"]

The Quarter the Price was Updated (D)​

[/TD]
[/TR]
[TR]
[TD="width: 156, bgcolor: transparent"] Product X
[/TD]
[TD="width: 156, bgcolor: transparent"] 5.50
[/TD]
[TD="width: 162, bgcolor: transparent"] 20182
[/TD]
[TD="width: 156, bgcolor: transparent"] 20184
[/TD]
[/TR]
[TR]
[TD="width: 156, bgcolor: transparent"] Product X
[/TD]
[TD="width: 156, bgcolor: transparent"] 6.00
[/TD]
[TD="width: 162, bgcolor: transparent"] 20182
[/TD]
[TD="width: 156, bgcolor: transparent"] 20183
[/TD]
[/TR]
[TR]
[TD="width: 156, bgcolor: transparent"] Product X
[/TD]
[TD="width: 156, bgcolor: transparent"] 5.00
[/TD]
[TD="width: 162, bgcolor: transparent"] 20182
[/TD]
[TD="width: 156, bgcolor: transparent"] 20182
[/TD]
[/TR]
</tbody>[/TABLE]

These results say that Product X was first billed in 2^ndquarter of 2018 at $5.00. In 3^rd quarter of 2018, the price changedto $6.00. Then it changed again in 4^th quarter of 2018 to $5.50.
When I go to report the price on Product X, I’m only interestedin reporting whatever the price was the last time it was updated. So for thatmatter, I only want to include the top row of these results in my report.
However, requests come in for sometimes tens or hundreds ofproducts. And, as you can imagine, the amount of prices associated with each ofthose products can balloon fast.
So far I’ve been using a manual-heavy process where I exportall my results into excel and start manually deleting all the rows that have amore recent, updated price. I repeat until I’m left with one price for everyproduct. It may get the job done, but it exposes me to too much risk for humanerror than I’m comfortable with, not to mention all the time it takes tocomplete. I need to automate.
WHAT I NEED
For a given product, I need to get Excel to notice all itsquarters in Column D that share the same quarter in Column C, then determinethe greatest value in Column D, and finally report that entire row of data.
WHAT I’VE TRIED
My attempts to automate this process via Excel formulae havethus far been discouraging. I seem to be stuck in limbo where I’m not surewhich formulae to use or in what combination.
I’ve tried a few combinations of CONCATENATE with VLOOKUP—thinkingmaybe I could concatenate C and D, and then VLOOKUP D and return B. I’ve alsotried using combinations of IF with MAX—thinking maybe I could nest IF in MAX todetermine what the max value in Column D was for every row that shared the samevalue in Column C. I’ve also looked online and have read about but don’tunderstand INDEX or MATCH, so not sure if those would help me either.
Basically, I’m lost. I feel like I’m circling something, butam just not able to put it all together without someone saying, “Hey dummy,this is what you need to do.” So I’m really hoping the good people on thisforum can help me out and teach me some valuable Excel lessons along the way.
Thanks everybody! It’s hard to believe a forum like thisexists J

 

[Mentor82]

Hi,
Try this.
I prepare code using two worksheets having headers in both of them in row 1 and product id in column a both. Makro picks product from sheet Result, checks the product in Data Prices worksheet, checks the highest value in column D and copy the data to the result worksheet. Check if that's ok for you.ps I've developed the code on my mobile so hopefully I havent made any bug while writing - let me know the result.

Sub CopyProductData()
Dim ws_result ad worksheet'result worksheet
Dim ws_data as worksheet'worksheet with prices
Dim, i&, j&, lrr&, lrd&, lcs&
Dim maxqty&, maxqtyrow&

Set ws_result =worksheets("Result")
Set ws_data =worksheets("PricesData")
lrr=ws_result.range("a1").Currentregion.rows.count ' calc of rows in result worksheet
lrd=ws_data.range("a1").currentregion.rows.count
lcd=ws_data.range("a1").currentregion.columns.count
If lrr>1 and lrd>1 then
    For i=2 to lrd
         maxqty=0
         maxqtyrow=0
         For j=2 to lrd
              If ws_data.cells(j,"a") = ws_result.cells(i,"a") then
                    If clng(ws_data.cells(j,"d").value)>maxqty then
                      
         maxqty=ws_data.cells(j,"d").value
         maxqtyrow=j
                    End if
              End if
         Next j
         If maxqtyrow>0 then
             ws_data.range(ws_data.cells(j,"b"),ws_data.cells(j,lcd)).copy
ws_result.range(ws_result.cells(i,"b"),ws_result.cells(i,lcd)).paste
         End if
    Next i
End if
Set ws_result=nothing
Set ws_data=nothing
End sub

 

[Nan3_mS]

Hello!

Try this:
G3 is the product you want to know about
G5 is the "Quarter the Price was Originally Billed" - assuming you have this info, if you dont, just use Filter and Remove Duplicates functions, it must be easy to narrow down the data.
G6 is Updated Price (what we are interested in):

=LARGE(IF((A:A=G3)*(C:C=G5);B:B);1)

G7 is The Quarter the Price was Updated:

=SUMIFS(D:D;A:A;G3;B:B;G7;C:C;G5)

Only works if for each A+B+C data there is only one D.
If that's not the case, let me know.

Was this helpful?

 

[GR00007]

For manual process to delete price history, you could sort on product (column A) and then on Quarter (column D) Descending putting the most recent price first for each product.
In column E2 enter the below and propagate to the end.

=IF(A2=A3,"*",IF(A1=A2,"**",""))

This will show a blank for unique products, a single asterisk for the first of multiple product rows and a double asterisk for those rows you want to delete.

 

[TonyUK72]

In the past when I was managing stock (with a high turn over) I came up with a simple solution for the purpose of stock valuation.

Lets say you order 10 items at a cost 3.00 ea giving a total cost 30.00, you sell 5 leaving you stock value at 15.00
Your next purchase might be for 20 at a cost of 2.85 ea giving a total cost of 57.00, making your current stock of 25 valued at 72.00 or 2.88 ea.

It's very simple to implement and means each stock item has a realistic actual cost, enabling you to mitigate historical pricing.

 

[justtryingtolearn]

One aspect of this problem I don't think I bore out clearly in my original post is the fact that Column C, The Quarter the Price was Originally Billed, is actually quite important. Column D, The Quarter the Price was Updated is the Quarter that we adjusted the original price, which was originally billed in a previous quarter. Yes, we are retroactively changing the price of the product, which then spurs our invoicing team to either send an invoice for more money, or issue a refund.

The reason this happens is due to the fact that the price is generated via a formula that's fed by ever-changing variables. We rely on a few data reporting agencies to supply us with the latest values for these variables on a quarterly basis. Around here, it is common to say something like, "In 20184, the [Data Reporting Agency X] updated what the [Price Variable Y] should have been for 20182 based on the latest data they've collected... Invoicing will have to send an invoice to solicit payment/issue a refund to resolve this discrepancy."

I've attempted to illustrate what this could look like by introducing another quarter in Column C below:

[TABLE="width: 354"]
<tbody>[TR]
[TD="width: 117, bgcolor: transparent"]

Product (A)​

[/TD]
[TD="width: 110, bgcolor: transparent"]

Price (B)​

[/TD]
[TD="width: 125, bgcolor: transparent"]

The Quarter the Price was Originally Billed (C)​

[/TD]
[TD="width: 119, bgcolor: transparent"]

The Quarter the Price was Updated (D)​

[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"] Product X
[/TD]
[TD="width: 110, bgcolor: transparent"] 5.50
[/TD]
[TD="width: 125, bgcolor: transparent"] 20182
[/TD]
[TD="width: 119, bgcolor: transparent"] 20184
[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"] Product X
[/TD]
[TD="width: 110, bgcolor: transparent"] 6.00
[/TD]
[TD="width: 125, bgcolor: transparent"] 20182
[/TD]
[TD="width: 119, bgcolor: transparent"] 20183
[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"] Product X
[/TD]
[TD="width: 110, bgcolor: transparent"] 5.00
[/TD]
[TD="width: 125, bgcolor: transparent"] 20182
[/TD]
[TD="width: 119, bgcolor: transparent"] 20182
[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"] Product X
[/TD]
[TD="width: 110, bgcolor: transparent"] 4.50
[/TD]
[TD="width: 125, bgcolor: transparent"] 20181
[/TD]
[TD="width: 119, bgcolor: transparent"] 20184
[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"] Product X
[/TD]
[TD="width: 110, bgcolor: transparent"] 5.00
[/TD]
[TD="width: 125, bgcolor: transparent"] 20181
[/TD]
[TD="width: 119, bgcolor: transparent"] 20183
[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"] Product X
[/TD]
[TD="width: 110, bgcolor: transparent"] 4.50
[/TD]
[TD="width: 125, bgcolor: transparent"] 20181
[/TD]
[TD="width: 119, bgcolor: transparent"] 20182
[/TD]
[/TR]
[TR]
[TD="width: 117, bgcolor: transparent"] Product X
[/TD]
[TD="width: 110, bgcolor: transparent"] 4.00
[/TD]
[TD="width: 125, bgcolor: transparent"] 20181
[/TD]
[TD="width: 119, bgcolor: transparent"] 20181
[/TD]
[/TR]
</tbody>[/TABLE]

When I'm asked to report the price on Product X, I'll actually have to break it down by Column C, The Quarter the Price was Originally Billed, because that represents the original invoice we are adjusting.

In other words, if I were asked to report the price on Product X, I'd say, "Based on the latest data, the price for Product X for 20181 was $4.50 and the price for Product X for 20182 was $5.50."

To everyone who's replied so far in this thread: THANK YOU! I've really enjoyed reading all the different approaches people are taking to this problem.