VLOOKUP problem because data not always in order

[nmualum]

So we have a pricing spreadsheet that has a range of rows for each fiscal year (FY13 thru FY20). When we price a part, there is a lead time associated with that part; i.e. how long will it take us to receive the part from our supplier to send out to the end customer. Sometimes the lead time may be 10 days, some times it is over a year.

A quote to the customer is good for 90 days from the date it is created. And there is a price expiration date (when will we no longer be able to guarantee the quoted price). We use this information to calculate what we call a Quote Half Life date. We take todays date, the lead time and 1/2 of the quote life (90 days, so 45 days is used). Add all that together to get a date. If the quote validity expires prior to that date, we can still quote the part, but we increase the price by an escalation percentage to cover the risk that we won't be able to get the part for exactly the same price.
Our pricing spreadsheet uses a block of cells like this:

						FY16	FY17	FY18	FY19	FY20
Escalation Type	Date Range	Escalation %	Escalation %	Escalation %	Escalation %	Escalation %
FY13	9/30/2012	9/30/1013	1.1350	1.1690	1.2010	1.2330	1.2650
FY14	9/30/2013	9/30/2014	1.1030	1.1360	1.1670	1.1980	1.2290
FY15	9/30/2014	9/30/2015	1.0610	1.0930	1.1220	1.1520	1.1820
FY16	9/30/2015	9/30/2016	1.0300	1.0610	1.0890	1.1180	1.1480
FY17	9/30/2016	9/30/2017		1.0300	1.0580	1.0860	1.1140
FY18	9/30/2017	9/30/2018			1.0269	1.0538	1.0820
Quote Half Life	11/7/2018		1.0400	1.0300	1.0300	1.0300
Current	11/8/2018		1.0000	1.0000	1.0000	1.0000	1.0260
FY19	9/30/2018	9/30/2019			1.0000	1.0262	1.0530
FY20	9/30/2019	9/30/2020					1.0000	1.0260

<tbody>
</tbody>

The row with the Quote Half Life date is populated with the calculated date. The row for 'Current' is the Quote Half Life date + 1 day.
If the lead time is short, say 10 days, the quote half life ends up as a date in May, and the vlookup to figure out what row to use works fine. But in this case, I put in a lead time of 200 days, and now the column with the dates is not sequential. So the vlookups return the wrong escalation value.

My first thought was that the Quote Half Life and Current Rows should logically be at the end of the table. But when I tried that with a long lead time, it worked fine. But if I tested it with a short lead time, it would fail because the dates wouldn't be in sequence.

My two thoughts on possible solutions are using vba to code this, loop thru the dates to find the proper match for the expiration date; if I find multiple date ranges that would work, somehow programmatically determine which one was actually the right date. My second idea was to make this block of rows/columns an actual table, so I could populate it with the calculated date, then resort it by that column, then apply the vlookup against it. Complicating that solution is the fact that this block of cells has a ****-ton of merged cells; the results of this spreadsheet are printed and sent to the customer, so whoever created this beast (8 years ago, long gone) has it formatted to fit on a page. When I tried to insert a table to contain this data, it 'unmerged' all the cells, causing havoc.

Does anyone have any thoughts on this? I've been banging my head against the wall for a day...

Thanks
Steve

 

[Drrellik]

Steve,

Welcome to the board and thank you for such a great first explanation of what you are trying to accomplish, so many times people have a hard time getting their point across, and I am sometimes one of them...so I can complain, but again lots of good information.

That being said could you add your current formula(s) and the cell location(s) you are working with

 

[nmualum]

Drrellik,
Thanks for spending the time looking at this. I'll try to break down the convoluted logic as best I can. Having issues pasting screen shots, so doing this the hard way.

This section is where the end user does the pricing. They enter in the quantity, Unit of Measure (Each), the base cost, the lead time for this quantity, and the expiration date for the last quote of these parts we received from the supplier.

	A	B	C	D	E/F	G
41	QTY	UOM	UnitCost	LeadTime	ExpiryDate	EscalCost
42	PRICE LIST
43
44	1	EA	150.0000	200	7/10/2018	158.07

Here's the main section, with row and column headers:

Row	M/N	O/P	Q/R	S/T	U/V	W/X	Y/Z	AA/AB
54				fy16	fy17	fy18	fy19	fy20
55	Escalation Type	Date	Range	Esc%	Esc%	Esc%	Esc%	Esc%
56	FY13	9/30/2012	9/30/2013	1.1030	1.1360	1.1670	1.1980	1.2290
57	FY14	09/30/2013	09/30/2014	1.0610	1.0930	1.1220	1.1520	1.1820
58	FY15	09/30/2014	09/30/2015	1.0300	1.0610	1.0890	1.1180	1.1480
59	FY16	09/30/2015	09/30/2016	1.0300	1.0610	1.0890	1.1180	1.1480
60	FY17	09/30/2016	09/30/2017		1.0300	1.0580	1.0860	1.1140
61	FY18	09/30/2017	09/30/2018			1.0269	1.0300
62	QuoteHalfLife	11/7/2018		1.0400	1.0300	1.0300	1.0300
63	Current	11/8/2018		1.0000	1.0000	1.0000	1.0000	1.0260
64	FY19	09/30/2018	09/30/2019			1.0000	1.0262	1.0530
65	FY20	09/30/2019	09/30/2020				1.0000	1.0260

This is another section that is used:

55	J/K	L
56	10/1/14	FY15
57	10/1/15	FY16
58	10/1/16	FY17
59	10/1/17	FY18
60	10/1/18	FY19
61	10/1/19	FY20
62	10/1/20	FY21
63	10/1/21	FY22
64	10/1/22	FY23

Now that I've bored you with tables, here's the crucial flow of data:
(1) There's a section at the top of the worksheet that allows the user to enter in detailed information about what part is being priced, all kinds of info. All we care about is a field with the quantity they are quoting, and a default lead time.
(2) I'm going to simplify some of this logic. When we quote a part, a supplier has provided us with their estimated lead time to get us the part. This lead time can vary based on the quantity; i.e. 1 part might take 20 days, but 1000 parts might take 100 days. For purposes here, we are going to assume that the lead time is the same and the price is the same for any quantity.
(3) The end user starts filling out the first table I showed you, with the quantity, UOM, base cost, lead time, and expiration date. When they put in the lead time, that changes the value for the Quote Half Life date. This is where the main problem is. If the lead time is short, the dates in the table in the range $O$56:$Q$65 are in a nice sorted order. But if the lead time is longer, as in this example, the dates are not in a nice sorted order.
(4) Here is the code in the G44 cell:

=IF(C44=0,0,IF(ISBLANK($C$42),IF(OR(AND(C$43<100,E44>=TODAY()-365-366),AND(C$43>=100,E44>=TODAY())),
C44*(
IF(VLOOKUP($O$62,$J$56:$L$64,3,1)="FY16",VLOOKUP(E44,$O$56:$T$65,5,1),
IF(VLOOKUP($O$62,$J$56:$L$64,3,1)="FY17",VLOOKUP(E44,$O$56:$V$65,7,1),
IF(VLOOKUP($O$62,$J$56:$L$64,3,1)="FY18",VLOOKUP(E44,$O$56:$X$65,9,1),
IF(VLOOKUP($O$62,$J$56:$L$64,3,1)="FY19",VLOOKUP(E44,$O$56:$Z$65,11,1),
IF(VLOOKUP($O$62,$J$56:$L$64,3,1)="FY20",VLOOKUP(E44,$O$56:$AA$65,13,1))))))),
"Needs Quote"),
C44*(
IF(VLOOKUP($O$62,$J$56:$L$64,3,1)="FY16",VLOOKUP(E44,$O$56:$T$65,5,1),
IF(VLOOKUP($O$62,$J$56:$L$64,3,1)="FY17",VLOOKUP(E44,$O$56:$V$65,7,1),
IF(VLOOKUP($O$62,$J$56:$L$64,3,1)="FY18",VLOOKUP(E44,$O$56:$X$65,9,1),
IF(VLOOKUP($O$62,$J$56:$L$64,3,1)="FY19",VLOOKUP(E44,$O$56:$Z$65,11,1),
IF(VLOOKUP($O$62,$J$56:$L$64,3,1)="FY20",VLOOKUP(E44,$O$56:$AA$65,13,1)))))))))

Essentially, it looks at whether there is a price list price in $C$42. We are not concerned with that...
All of the VLOOKUPS are taking the QuoteHalfLife Date ($O$62), and checking to see what Fiscal Year that date falls in looking at the table $J$55:$L$64. Once it knows what Fiscal Year, it looks at the Expiration Date in column E, and does a VLOOKUP against the range $O$56:$AA$65 to figure out what escalation percentage to apply. C44 is the base cost, so H44 is going to be the base cost times the escalation percent.

When the dates in $o$56:$Q$65 are out of sequence, weird results can occur.

So I'm trying to figure out how to address this issue. Rewrite that huge formula in vba? Come up with a way to resort the values in the range $0$56:$AA$65? A third option is to create a second tab, and have two versions of the worksheet - one that handles shorter lead times, one that handles longer lead times... I'm also open to any other suggestions.

Thanks!
Steve

 

[Drrellik]

Steve,

Just saw this reply it is 1 am sorry for the slow response gonna take a look over the weekend and see if I can help at all....

Thanks Don

 

[Drrellik]

Steve,

There is no date range in QR:62, or 63 and it is beginning to look like someone build this in 2014, for date thru 2018 and someone else had added FY19 & FY20 and perhaps copied and pasted some data and now you are having issues with the formulas.

If it worked fine for 4 years and now has issues consider using INSERT to add the rows you need above the "Quoted halflife" and make sure your date ranges are still in order from 9/30 - 9/30 of the following year and you can use CTRL-X (cut) CTRL-V (Paste) with out harming the formula excel will modify the ranges for you in most cases.

I think you will be able to get it working again.

If that is not the case and you need to re-vamp the sheet all together we can look at that as well. and for old FY14,15,16 you can 'Hide' those columns or rows just be careful deleting them that can affect the formula ranges.

HTH ~DR

 

[nmualum]

Drrellik,
I spent several hours disassembling the code for this routine, and determined that the dates definitely have to be in the right sort order to work. So what I opted to do is:
' (1) turn of screen updating...
Application.ScreenUpdating = False

'(2) delete existing table whether it exists or not
Rows("100:111").ClearContents

'(3) copy original 'table' from B39:J50 and paste as values down at rows 100...
With Worksheets("PRICING")
.Range("B39:J50").Copy
.Range("B100:J111").PasteSpecial xlPasteValues
End With

'(4) convert to a table...
ActiveSheet.ListObjects.Add(xlSrcRange, Range("B100:J111"), , xlYes).Name = "Table1"

'(5) format two date columns as date
Worksheets("PRICING").Range("C100:C111").NumberFormat = "mm/dd/yyyy"
Worksheets("PRICING").Range("D100:D111").NumberFormat = "mm/dd/yyyy"


'(6) now sort by DateRange column C100......
Dim ws As Worksheet
Set ws = ActiveSheet
Dim tbl As ListObject
Set tbl = ws.ListObjects("Table1")
Dim sortcolumn As Range
Set sortcolumn = Range("Table1[Date Range]")
With tbl.Sort
.SortFields.Clear
.SortFields.Add Key:=sortcolumn, SortOn:=xlSortOnValues, Order:=xlAscending
.Header = xlYes
.Apply
End With

Application.ScreenUpdating = True


Then I edited the formula for the escalated cost, and pointed it at the temporary table I created down at rows 100-110+...

I also added some code so that if the Lead Time is changed, the logic up above would automagically update the hidden table...

It actually works pretty well - at least the end-users are happy. I'm sure there may be more efficient ways to do this, but I just sort of scavenged code from here and there to accomplish what I needed.

Thanks for your help and advice.

Steve

 

[Drrellik]

Glad you have a working product & thanks for the feedback