+ Reply to Thread
Results 1 to 15 of 15

Collate (lookup) Multiple orders at the same time, based on DATE range in a table

  1. #1
    Registered User
    Join Date
    10-09-2013
    Location
    Dike, IOWA
    MS-Off Ver
    Excel 2010
    Posts
    19

    Collate (lookup) Multiple orders at the same time, based on DATE range in a table

    Trying to index, match, copy over, all the Sales order information based on the SCHD DATE of my Table(for current week), for each PRODUCT AREA.

    Bottom line, I want to be able to list all the current orders for the week 4-10 NOV 2013, by product area. I can't mess with the table because it is giving other VLOOKUP info to another table in it's current form.

    So, I believe there should be 2 criteria for each item I want to look for. #1 being the date/s I need to filter for, and then the product area I want to find this week's orders for, with the third piece being the info (cell) I am looking for.
    Formula is in Cell A4 of ORDER STATUS TAB.

    INDEX('Open Orders'!$G$2:$G$1389, SMALL(IF($B$1='Open Orders'!F2:F500, ROW('Open Orders'!$G$2:$G$1389)-MIN(ROW('Open Orders'!$G$2:$G$1389))+1, ""), ROW(A1)))

    I can't get this formula to give me all the orders that are open this week, by product area?? Please help, will greatly improve my daily efficiency in tracking open orders in one spot instead of using unreliable PIVOT TABLES.
    Thanks.
    Attached Files Attached Files

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Collate (lookup) Multiple orders at the same time, based on DATE range in a table

    I used 2 helper columns on the OPEN ORDERS ws.

    1st column: If SCHD DATE >= parameter 1, if SCHD DATE <= parameter 2, rank N.
    2nd column: rank 1st columm

    Then it's just a matter of the index, where Q2:Q28 was my 2nd helper column.

    =INDEX('OPEN ORDERS'!$A$2:$N$28, MATCH(ROW(A1),'OPEN ORDERS'!$Q$2:$Q$28,0),MATCH('ORDER STATUS'!A$3,'OPEN ORDERS'!$A$1:$N$1,0))

  3. #3
    Registered User
    Join Date
    10-09-2013
    Location
    Dike, IOWA
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Collate (lookup) Multiple orders at the same time, based on DATE range in a table

    Unsure why we are using cell A3 as a matching criteria. Cell A3 is just a column Header, but doesn't match same column header on the other sheet, unless you want me to name them the same?

    Not sure what you mean by "rank N", on the 2nd parameter.
    Please advise, thanks.

  4. #4
    Registered User
    Join Date
    10-09-2013
    Location
    Dike, IOWA
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Collate (lookup) Multiple orders at the same time, based on DATE range in a table

    Is there another option, instead of adding two more columns? Original table is much larger than my example table I posted....I just used pertinent data to show my issue.

  5. #5
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Collate (lookup) Multiple orders at the same time, based on DATE range in a table

    One way to transfer data is to assign ranks to each line; in your case you wanted to preserve the order of the PROD AREA.

    INDEX(..MATCH(.. works wonderfully in conjunction with rank+countif which creates non-duplicate values, which are then easily referenced.


    Let me search the forums for a single point transfer.

    I'm pretty sure I saw someone write a complicated array that might be able to accomplish this.

    Edit: Added the attachment I somehow missed on my first post

    orders filter for Prodschdler.xlsx
    Last edited by daffodil11; 11-04-2013 at 04:52 PM. Reason: added attachment

  6. #6
    Registered User
    Join Date
    10-09-2013
    Location
    Dike, IOWA
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Collate (lookup) Multiple orders at the same time, based on DATE range in a table

    Hope I am saying this correctly...Not really concerned by rank of the PROD AREA, would just like to pull all the orders (& their info) from oldest to newest between the two dates I select, when I tell the formula to only bring in a certain PROD AREA.

    Make any sense?

    I manually inserted the correct answers in the table if that may shed some light??
    Thanks.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    10-09-2013
    Location
    Dike, IOWA
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Collate (lookup) Multiple orders at the same time, based on DATE range in a table

    Daffodill11,
    Please excuse my ignorance, I did not see your link on the thread, just found it. Makes much more sense now. If you can't find 2 step formula, I think I can make this work. Would just like to avoid pivot table, is that rational, or should I use Pivot Table?

  8. #8
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Collate (lookup) Multiple orders at the same time, based on DATE range in a table

    I spent the last hour trying to adapt your information into a pivot table; it's my Achille's Heel of Excel.

    I can write arrays that do the same thing from now until the cows come home, but actually using the Pivot table function is my bane.

    I'm using Melvinrobb's post in this thread as a point of reference. https://www.excelforum.com/showthread.php?t=960736

  9. #9
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Collate (lookup) Multiple orders at the same time, based on DATE range in a table

    Here you go.

    I built you an advanced filter machine.

    Type in your start date in B1, End Date in B2, and the PROD AREA in B3 and press the button.

    If you need any help adapting this to your set of data, I'll be glad to walk you through the process. It's pretty painless.

    adv filter macro for Prodschdler.xlsm

    It's using an Advanced Filter macro which is tied to the button. The data is being filtered to the FILTER tab, using the parameters set at the top which are in being automatically fed from cells B1:B3 of the first page. The front page is then selectively pulling the data you care about from the FILTER tab and populating. (It's set up so you'll never need to change anything on the FILTER tab.)
    Last edited by daffodil11; 11-04-2013 at 06:29 PM.

  10. #10
    Registered User
    Join Date
    10-09-2013
    Location
    Dike, IOWA
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Collate (lookup) Multiple orders at the same time, based on DATE range in a table

    I'm speechless, that's absolutely the coolest thing I have seen, just wish I knew how it work?
    I couldn't find a MACRO attached to the button?

    Thanks so much

  11. #11
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Collate (lookup) Multiple orders at the same time, based on DATE range in a table

    I'm home now and typing from my cell phone, but I'll be sure to check the document and give you a full debrief in the morning.

    The Visual Basic code I wrote applies an advanced filter using the criteria from a1 to n3 of the filter tab. Normally this kind of thing is limited to pasting only to the same tab you filter from, but with a little code we can bend those rules.

  12. #12
    Registered User
    Join Date
    10-09-2013
    Location
    Dike, IOWA
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Collate (lookup) Multiple orders at the same time, based on DATE range in a table

    Will VBA code bring a lot of unneeded weight to the file's size? The entire file is already 10MB. Problem is, if I don't understand it, I can't fix it if someone else in my company messes with it. I can only "record" simple MACROs, not actually write them. Sorry.

  13. #13
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Collate (lookup) Multiple orders at the same time, based on DATE range in a table

    I'm pretty terrible at VB myself. I just used the recorder to write this, and then manually adjusted the range.

    Please Login or Register  to view this content.
    The code is in the workbook I did attach, you just have to enable macros when you open it. (Your machine may be disabling them by default, but you should still be able to see the code.)

    The first part clears the filtered data on the FILTER tab. The second part pulls the data. It looks at A1:N500 of OPEN ORDERS and then filters the data according to the first 3 lines of the FILTER tab, and dumps it in A6. It's that simple.

    The macro should have a negligible impact on file size. The total code is less than 15 KB.
    Last edited by daffodil11; 11-05-2013 at 10:46 AM.
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  14. #14
    Registered User
    Join Date
    10-09-2013
    Location
    Dike, IOWA
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Collate (lookup) Multiple orders at the same time, based on DATE range in a table

    Great, thanks. I will try to adjust it to the real WORKBOOK, and see what happens. Appreciate the extra effort you devoted to this, wasn't warranted, but I do appreciate it

    Thanks AGAIN!
    BTW, Love the Unicorn Icon image!

  15. #15
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Collate (lookup) Multiple orders at the same time, based on DATE range in a table

    No problem, glad I could help out.

    Woo woo!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to lookup value based on Date time closest to criteria
    By ilionel1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-08-2013, 02:01 PM
  2. [SOLVED] Sum like orders, then create multiple rows (prod records) based on sum...
    By mvgoggans in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-12-2013, 03:56 PM
  3. Replies: 10
    Last Post: 02-19-2013, 12:05 PM
  4. [SOLVED] Create a lookup table to award point values based on run time
    By coach touch in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-15-2012, 12:35 PM
  5. Multiple lookup based on date range pricing
    By posttoamit in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-18-2011, 05:52 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1