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

**Prodschdler** · 11-04-2013, 02:22 PM

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.

**daffodil11** · 11-04-2013, 03:36 PM

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

**Prodschdler** · 11-04-2013, 04:21 PM

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.

**Prodschdler** · 11-04-2013, 04:24 PM

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.

**daffodil11** · 11-04-2013, 04:41 PM

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

**Prodschdler** · 11-04-2013, 05:06 PM

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.

**Prodschdler** · 11-04-2013, 05:21 PM

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?

**daffodil11** · 11-04-2013, 05:57 PM

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

**daffodil11** · 11-04-2013, 05:46 PM

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

**Prodschdler** · 11-04-2013, 07:49 PM

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

**daffodil11** · 11-04-2013, 08:59 PM

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.

**Prodschdler** · 11-05-2013, 09:41 AM

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.

**daffodil11** · 11-05-2013, 10:43 AM

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

Sub Filter()

    Sheets("FILTER").Range("A6:N500").Clear
    
    Sheets("OPEN ORDERS").Range("A1:N500").AdvancedFilter _
        Action:=xlFilterCopy, _
        CriteriaRange:=Sheets("FILTER").Range("A1:N3"), _
        CopyToRange:=Sheets("FILTER").Range("A6"), _
        Unique:=False
               
End Sub

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.

**Prodschdler** · 11-05-2013, 05:16 PM

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!

**daffodil11** · 11-05-2013, 05:19 PM

No problem, glad I could help out.

Woo woo!

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

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Similar Threads

How to lookup value based on Date time closest to criteria

[SOLVED] Sum like orders, then create multiple rows (prod records) based on sum...

Lookup values in multiple columns based on a date range, return single value per criteria

[SOLVED] Create a lookup table to award point values based on run time

Multiple lookup based on date range pricing

Bookmarks

Bookmarks

Posting Permissions