Flexible lookup formula

Posted on 2003-02-19
Medium Priority
Last Modified: 2006-11-17
Ok, let´s see if I can make this simple..

I got two files. The first one contains orders for one week and there are three columns of interest: Reciever, Booking date and Recieving date.

The second file is a calender with weeks (and Mon-Sun) on the horizontal axis and the recievers on the vertical axis. Each sheet contains 26 weeks, week 1-26 or 27-52 (Sheets named W 27-52 2002, W 1-26 2003…). That is, the first "day" isn´t automatically 1/1. For each reciever there are 6 rows (the recievers are then separated by an empty row) where the top row indicates booking date and the bottom row means receiving date. An order is marked by a index number. See example below.

Week                        1
                            Mon Tue Wed Thu Fri Sat Sun
Booking date       2      3        4
Receiving date                 2       3    4

This Calendar shows when the order is SUPPOSED to be recieved. In the first file, let´s name it LT (Leadtime), I have got the actual receiving dates. This file contains as of today about 700 orderlines but will possibly increase to 3000. A new list is received every week.

Basically what I want to do is to check if the orders have arrived in time, that is on or before the scheduled receiving date, and mark those who have with a 1.

I can easily do this with a For each ..next. But already with these 700 orderlines it takes quite a while, more than a minute and a half, and the users computers are slower than mine. What I need is a formula (or maybe more than one..)

So, I´ve got a constantly growing Calendar (not only number of sheets, recivers may added or deleted so anytime) and a weekly Leadtime workbook.

This not so smooth solution is the closest I´ve reached:
(Column F is reciever, column J is booking date and column M is receiving date)

With Sheets("Orders")
    Rows = Application.CountA(.Range("A:A"))
        .Range("N2:N" & Rows).Formula = "=(WEEKNUM(J2)=52)*WEEKNUM(J2)+(WEEKNUM(J2)>52)"
        .Range("O2:O" & Rows).Formula = "=(N2-(N2>26)*26)*7 + 2 - 7 +(WEEKDAY(J2)-1)"
        .Range("P2:P" & Rows).Formula = "=(WEEKNUM(M2)<=52)*WEEKNUM(M2)+(WEEKNUM(M2)>52)"
        .Range("Q2:Q" & Rows).Formula = "=(P2-(P2>26)*26)*7 + 2 - 7 +(WEEKday(M2)-1)"
        'Check errorvalues. No sum means error.
        .Range("BX1").Formula = "=SUM(R2:R" & Rows & ")"
        If IsError(.Range("BX1").Value) = True Then GoTo errorhandler
        .Range("R2:R" & Rows).Formula = "=(YEAR(J2)-2002) * 2 + (N2>=27)"
        'EndSheet (might differ from Startsheet!)
        .Range("S2:S" & Rows).Formula = "=R2+(P2<N2)*1"
        Rad = 2
        CalFile = "[Calendar.xls]"
        'Writes all sheets in Calendar to column AH
        For Each wsSheet In Workbooks(CalFile).Sheets
            .Range("AH" & Rad) = wsSheet.Name
            Rad = Rad + 1
        Next wsSheet
        'Names the area
        .Range("AH2:AH" & Rad - 1).Name = "SheetName"
        'Gives Startsheetname in text
        .Range("T2:T" & Rows).Formula = "=INDEX(SheetName, R2)"
        'Gives Endsheetname in text
        .Range("U2:U" & Rows).Formula = "=INDEX(SheetName, S2)"
         Range("AH1") = CalFile
        'Checkarea for row calculation sheet 1
        .Range("V2:V" & Rows).Formula = "=CONCATENATE(""'"",$AH$1, T2,""'!$A$1:$A$250"")"
        'Checkarea for row calculation sheet 2
        .Range("W2:W" & Rows).Formula = "=CONCATENATE(""'"",$AH$1,U2,""'!$A$1:$A$250"")"
        ’Row booking date
        .Range("X2:X" & Rows).Formula = "=MATCH(F2," & .Range("V2").Value & ",0)"
        'Row recieving date
        .Range("Y2:Y" & Rows).Formula = "=MATCH(F2," & .Range("W2").Value & ",0)"
    End With
    'Cell for startorderindex
    Range("Z2:Z" & Rows).Formula = "=ADDRESS(X2, O2)"
    ’Cells receiving periods
    Range("AA2:AA" & Rows).Formula = "=ADDRESS(X2+5, O2)"
    Range("AB2:AB" & Rows).Formula = "=ADDRESS(X2+5, Q2)"
    Range("AC2:AC" & Rows).Formula = "=CONCATENATE(""'"",$AH$1, T2,""'!"",Z2)"
    Range("AD2:AD" & Rows).Formula = "=CONCATENATE(""'"",$AH$1, T2,""'!"",AA2,"":"",AB2)"
    'Range("AD2:AD" & Rows).Formula = "=CONCATENATE(""'"",$AH$1, T2,""'!C""" & Range("X2").Value + 5 & """: GB """ & Range("X2").Value + 5 & ")"
    Range("AE2:AE" & Rows).Formula = "=INDIRECT(AC2)"
    Range("AF2:AF" & Rows).Formula = "=(O2<=(MATCH(AE2,AD2,0)))*1"
End Sub

For some %¤&%# reason the MATCH formula doesn´t work. I´ve tried it manually, but a reference (Here: AD2) as search area doesn´t work! Why? Just solving this last bit would do as a solution. A smoother one would be even better.
Question by:GoBanana
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
LVL 43

Expert Comment

by:Steve Knight
ID: 7982383
Haven't got time now to try it all out (sorry) but the MATCH command looks rather odd.  What are you trying to do with it since:


MATCH(AE2,AD2,0)  is 1 if AE2 and AD2 are the same otherwise N/A

so if O2<=1 then it returns 1 multipled by 1 =1
or if O2>1 then it returns 0 multiplied by 1 =0

LVL 43

Accepted Solution

Steve Knight earned 300 total points
ID: 7982431
Looking a little more I can see you are storing a range as text in that cell so you probably want:


OFFSET might be worth a look for creating ranges, e.g.


returns range as the current row, column 6 for one row high and 5 columns wide.

Got to go now though, sorry.  Someone else will no doubt be along in a minute!


Author Comment

ID: 7986024

"=(O2<=(MATCH(AE2,INDIRECT(AD2),0)))" works fine (or rather with Q2 instead of O2, my mistake). I never thought of using INDIRECT on a reference to a range. Great!

The OFFSET formula might work. But there is one problem (the very reason I invented all these columns with text values of the workbook, sheets and ranges); the lookup range is in another workbook, in a sheet that depends on the value on every orderline. How do I incorporate that into the formula? Is it possible? You see I think quite a few of the columns could be eliminated in that case, making the macro even faster.


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

LVL 43

Expert Comment

by:Steve Knight
ID: 7993193
It is not normally good practice to do so here but as no-one else has looked in on this question would you be prepared to either publish on the web or email me the sheet (perhaps without data if it is sensitive) an I'll see what could be simplified...  Email address in in my profile (click on dragon-it link above)

Otherwise we'll see what we can work out here for you (just rather difficult at the moment since email notifications are down)


Expert Comment

ID: 8031243

If I understand your problem correctly you should be able to accomplish the task with vlookup formulas, after a little setup on your files, which will be significantly quicker than your macro.

So, let me try to explain it well.  On the first sheet you have three columns of concern: Receiver, BookingDate and ReceivingDate. Hopefully, in that order from left to right, no matter if there are any other columns in between them as long as the ReceivingDate column is to the right of the Receiver column.  If not, you will need to reorder them that way for my example/solution.  These house the orders for the week.  Since I didn't have any index numbers for the orders I inserted a column after the Receiver column and placed some numbers within it.  Then insert a new column A (I labeled Check) and in it place the formula =B2&C2, where B represents the column with the Receiver names and C represents the column of Index numbers.  Copy that formula down the entire column and then edit, copy, edit, paste special values the column to eliminate the formulas (don't need any unnecessary ones lying around).  My resulting range was A2:E27.

Now on sheet 2, you have a row 1 with the week numbers from cells B1 thru GA1 with the numbers 1 thru 26 (7-1's, 7-2's, ... thru 7-26's).  Row 3 has the days of the week from Mon (cell B3), Tue (cell C3), ...thru Sun (cell GA3).  In cell B2 is your starting date, increased by one in cell C2 and so on until GA2.  Also you have column A with the Receiver names beginning in cell A4 repeated thru A9 for the first, a blank row 10, then A11 thru A16 for the next Receiver, blank A17 and so on down the entire spreadsheet.

In row 4 are the Index numbers for the orders for the week under the Dates, corresponding to the Index numbers and dates for the BookingDate from the first sheet.  In row 9 are the Index numbers for those same orders for the week under the Dates, corresponding to the Index numbers and dates for the ReceivingDate from the first sheet.

Now for the formula, which I assume you are placing in row 10, place the following formula:
where Sheet1!$A$2:$E$37 represents your range from sheet 1, and 5 represents the column with the Receiving dates.  Copy this formula across the remainder of row 10 and subsequently copy that same formula into row 17, 24, 31, ... as far as needed.  It will update accordingly.

Since you stated that the items may grow and shrink occasionally, then I suggest that you use a named range on sheet 1 instead of hard-coded cell references which will update in your formulas directly when computed.

This will result in a row of 1's and blanks corresponding to if the scheduled receiving date was met or not.  I've setup a sample spreadsheet demonstrating this if needed.

Hope this helps.

Author Comment

ID: 8032316

I´m impressed with your effort! For a number of reasons (some of them not of your knowledge since I didn´t give a complete description a my model (I isolated the problem and didn´t think that was necessary)) it is however not the final solution.

First of all, I actually made the model work with the little help from dragon-it. And it actually isn´t that slow you might think looking at the extensive code (less than 10 sec including opening two files and closing them). But I still think there must be a smoother and faster way to carry it out (necessary as I mentioned the number of rows will increase by time and thereby slow down the process).

The whole procedure will be controlled from a third file, a user interface. The user shall never be aware of the two files I´ve mentioned (the calendar and the weekly orders). Because of that what I need is a macro writing the formulas. And by the way, note that these are separate files since they are updated independantly. (The weekly update is a new file with a new file name every week.) But I guess I could copy the weekly orders into a new sheet in the calendar file just for the calculation matter and then close down without saving, if that makes thing easier..

I don´t get how I will get the index values into column C. The index numbers are in the calendar sheets. What´s your ides of getting these figures into the weekly orders sheet?

Cool VLOOKUP formula, but 26(weeks)*7(days)*20(suppliers, might be as many as 30)*7(sheets/half years, might of course be more) makes a hell of a lot of formulas increasing the file size. Possible solutions could perhaps be writing the formula every time but not saving them, or in some way automatically letting just the 2 last pages contain formulas and the rest just values...hmm..

What I want to do with all the Ones and Zeros after I checked wich orders arrived in time, is making a pivot tabel to eventually find out in percent how many orders that where delivered in time on a supplier - reciever level. This will decrease my 700 (or so) orderlines to about a tenth. The result I then put into a database in the user file.

So, a little further elaboration is needed. But I think you will come up with something good in the end!


Author Comment

ID: 8203556

You said you would have a closer look at the model to possibly come up with a smoother solution. That´s why I waited this long to accept your comment as an answer.

Anyway, I figured I cannot let the question be unclosed for any amount of time. And even if your extra help didn´t show up, the accepted formula really DID help me!

LVL 43

Expert Comment

by:Steve Knight
ID: 8205913
I'm sorry GoBanana for not helping further before as promised.  I have only been dipping in now and again here since then as I've been very busy in work and personal lifes (expecting first baby couple of weeks, lots to do!)

Thanks for accepting the answer anyway, glad it helped in part.  I'm hibernating from E.E. pretty well for a couple of months now but will be back at some point so if the situation arises I will try to help you better another time!


Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question