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

1. ## 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.

2. ## 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. ## 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.

4. ## 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. ## 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

6. ## 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.

7. ## 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. ## 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. ## 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.

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.)

10. ## 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. ## 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. ## 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. ## 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.

14. ## 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. ## 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!

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

#### 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