INDEX/MATCH to return row data if between two dates

Good morning,

I have a big workbook of data - signups to a database - and need to be able to output rows of data created between two dates, to another IMPORT sheet to a database.

In my source sheet, the date is in column C, and it would need to stay there (and all columns in same order) on the IMPORT sheet.

I need to pull in info from the source sheet and populate each row, and all columns of data on that row, but only if the date falls between two dates;

Examples, 1-Nov-16 to 30-Nov-16

Assuming I'd need an index/match which I could drag along (and down), which would anchor to the data criteria in column C and only populate columns D onwards if that data is between the two criteria.

Hoping that makes sense?

Any help appreciated please as multiple coffees are not cutting it.

Cheers,

Ian

Hi Ian,

Can I ask why you can't sort/filter by date and then copy the data over ?

If it's needs to be in a certain order you can use MATCH to put the copied rows into numerical order and resort after.

Of course you can ask
I'd like the functionality to do this via a form entry, so that the end user just types in two dates and gets the desired output. Also, as the structure will be used in other sorting applications I am building.
Thanks,
Ian

OK so this can be done with INDEX and MATCH

Do you have a sample workbook you could upload for me to apply it in there? or can you please upload one.

Sure -give me a few minutes to create a clean one and upload

Ta, I'll be otherwise engaged from 11:30 until about 2pm but if I get time in-between meeting a lunch I'll jump on it.

Okay here we go.

Sheet attached - input data in first tab, second tab is criteria and third is where I need that to appear.

Thanks in advance,

Ian

Hey, this might not be the most elegant solution, but it seems to be working and was quick to do. I used tables, so you can resize the table in the output worksheet to the "end" of the data. In the Criteria sheet there is a cell indicating up to where you need to resize the output table.

Please let me know if I can be of more help (for instance, to make the output table resize itself automatically).

Best regards,

Francisco

Hi Ian,

Let me know if this is on the right lines of what you'd like to achieve?

I've opted for a VBA solution as opposed to Formula/Function.

Thanks

Dave

Originally Posted by franciscofp

Hey, this might not be the most elegant solution, but it seems to be working and was quick to do. I used tables, so you can resize the table in the output worksheet to the "end" of the data. In the Criteria sheet there is a cell indicating up to where you need to resize the output table.

Please let me know if I can be of more help (for instance, to make the output table resize itself automatically).

Best regards,

Francisco

That's an interesting way of approaching it - I do like it - let me try adding that and see if it works over the (rather large) input data set.

would I need to add any functionality to CRITERIA or OUTPUT tab in order to add more columns than in my example (I slimmed the INPUT and OUTPUT down for this to anonymise the dataset).

Originally Posted by PFDave

Hi Ian,

Let me know if this is on the right lines of what you'd like to achieve?

I've opted for a VBA solution as opposed to Formula/Function.

Thanks

Dave

Thanks Dave,

I get an error message when I click SELECT OUTPUT - 'could not load some objects because they are not available on this machine'

(note - macros are enabled)

The box for select start and from date has no 'fields' to enter data into, and the CRITERIA tab doesn't seem to have any functionality (it would need to as there are other inputs to the OUTPUT I have already programmed - it's just the date range I'm grappling with).

Any clues please sir?

Additionally, is there an INDEX/MATCH solution - just need to learn if that function can/will work in terms of being able to adapt & edit (which in VBA I find harder to do - can often reverse engineer formula to retask and learn them).

Cheers,

Ian

Thanks!

I am not sure if I got the question right, but if you need to have more columns in the Input and Output tables, you should only need to expand the tables to the right and then in the Output table, fill with the formula to the right.

I am attaching a new version, using MATCH to avoid having misfits in the columns in the input and output sheets. I also turned on a data validation in the output sheet in order to "force" you to only choose columns that exist in the input sheet - but please note that if you choose twice the same column, Excel will add a 2 to the end of the column name and it won't find a match. It also fixes a bug that happened when you expanded the table vertically.

I also put a hyperlink to the last filled row in the output table (might be useful if you have a large number of records), but you still need to expand the table manually.

I hope this works for you.

Hi Ian,

Try this instead then, I was adding in the use of Calendar controls for the user to select their date range (didn't notice you were 2010), I now just refer to your Criteria Sheet as you originally requested.

The Code for this is pretty straight forward which I'm happy to explain if you would consider using this method.

Using INDEX/MATCH could cause issues depending on the size of your data set.

Please let me know if this works for you.

Regards

Dave

Apologies - also aware there was a double post but couldn't see the delete option.
Thank you.

@iantix,

I've taken your file from Post #7 and combined the Criteria and Output sheets - it strikes me as being easier to enter the criteria and see the results immediately on the same sheet, rather than swapping between them.

I've used column L in the INPUT sheet as a helper, with this formula in L2:

=IF(OR(CRITERIA!$H$1="",CRITERIA!$H$2=""),"-",IF(AND(C2>=CRITERIA!$H$1,INT(C2)<=CRITERIA!$H$2),MAX(L$1:L1)+1,"-"))

This identifies each record which matches the criteria and allocates a unique sequential number to them. The formula can be copied down beyond your data in order to accommodate more records, as can be seen by the hyphens, which show where the formula is active.

I've also used column L on the criteria sheet as a helper (coloured blue) with this formula in L5:

=IF(ROWS($1:1)>MAX(INPUT!L:L),"",MATCH(ROWS($1:1),INPUT!L:L,0))

This returns the row number on the INPUT sheet where the appropriate record (given by the ROWS($1:1) term, which returns 1, 2, 3 etc. when the formula is copied down) occurs, and it is more efficient to have this in a separate column rather than include it within a combined INDEX/MATCH for each field. In A5 I have used this formula:

=IF($L5="","",INDEX(INPUT!A:A,$L5))

which brings the appropriate record from column A of the INPUT sheet. This can be copied across to K5, but I've made a slight adjustment to the formula in B5:

=IF($L5="","",INDEX(INPUT!B:B,$L5)&"")

as you have blank entries on the INPUT sheet, so the &"" at the end of the formula ensure that blanks are not displayed as zeros. I've also formatted C5 as a date/time, and then the row of formulae on row 5 can be copied down as far as you need them (I've just copied to row 16 on this sample file).

So, you can now change the criteria in H1 and H2 and the display will automatically adjust.

Hope this helps.

Pete

Thanks Pete,

Have been playing with this for past few hours and ultimately, the weight of process in excel is too much - my dataset is 25k rows and the criteria and results do need to be on separate tabs.

Dragging the formula down further just increases the hang time until I get to a point where excel hangs terminally, and I even split my input data and the output formula (with criteria) as per your post into a separate file, even that is just not happy.

Ideally, I just want an index/match but I suspect that even that will crash.

I'm simply going to have to use filters on the input tab (which also, in my full version, does a fair bit of data cleaning/validation) - drop that data as values only elsewhere and then do extra analysis on it.

Throwing in towel I think unless a brighter idea comes forth,

Thanks all,

Ian

Ian,

I'm not sure why my version isn't working for you, are you able to try again but this time open VBA using F11 choose module and Sub OUTPUT then run through the code step by step using F8 and see what happens?

Code with notes below, my only area I suspect may be an issue is the filtering.

Please Login or Register  to view this content.

Thanks - I don't know what to suggest - I have screen capped it - I get the same error messages and some form of debugger points to a part of the code.

Have attached those screen shots.

Ian

1. Open the database or application.

2. Open a module in Design view or press ALT+F11 to switch to the Visual Basic Editor.

3. On the Tools menu, click References.

4. Clear the check box for the type library or object library marked as "Missing:"

Thank you - appears to have fixed that issue.

Just checking i'm using it right - I do the dates on CRITERIA sheet, then click the SELECT OUTPUT button on the INPUT sheet, which changes results on OUTPUT sheet.

Is it a simple job please to;

A move button to the CRITERIA sheet? (managed to do this myself, appears to still work fine but B happens)
B not have the view go to INPUT sheet when I've pressed button, but to OUTPUT sheet instead?

Once I crack that I can try dropping in my larger dataset and seeing how stable we get.

Thanks again,

Ian

Thanks JohnTopley - appreciated - you're the expert sir, so that may explain the time diff.

When merging sheets, I'm having to keep existing functionality on my input tab (which has several processes of data clean happening) - that takes a fair bit of processing and accounts for a large amount of filesize.

I'm being careful to adapt but am also having to try and understand the solutions, as best I can. The hangs and crashes slowed me down substantially.

Once issue I have sorted was solved by this; http://xsformatcleaner.codeplex.com/

I could share the other data clean processes - mainly postcode validation via a VBA, plus a fair few IF's to ensure no zero fields - but I can't share the sheet as is due to data compliance.

With VBA if you are processing cells in ranges directly i.e. read/writing to worksheets, this is quite inefficient.

The better solution is read the data into an "in-memory array", process all the "records" (array) and then output the updated array.

It is possible to read 25K records into memory, process them all, and then output them.

I agree with John, though. I've used my formula approach many times on files that are larger than 25k records, and they are still workable once the initial copying down of the formulae has been done (and don't forget that as you will have fewer records in the OUTPUT sheet, you don't need to copy the formulae down the same number of rows). Your other processes must be slowing down the execution, but perhaps you can fix the formulae that you use once you are satisfied that the data is valid.

Hope this helps.

Pete

Thanks - again you're level of expertise exceeds mine and so I can follow what you're saying to a point but cannot action this as I simply don't know how.
All I know is I wanted to just use a formula to find records between two dates and have them populate another tab.
Would you suggest I share my formula on my input sheet, with one row of anonymised data, and optimise that before implementing any of the solutions offered above?

Thanks Pete - your solution seems most elegant and would probably be the one I go with - just need to tweak the button notes as above and I will attempt (tomorrow now) to apply it to the 21mb sheet with the source data and INPUT sheet within.
Sidenote - surely there's a quicker key-stroke way of copying down/across formula than dragging the cell range and watching excel slowly range down as fast as the mouse can pull it?

Thanks Pete - your solution seems most elegant and would probably be the one I go with - just need to tweak the button notes as above and I will attempt (tomorrow now) to apply it to the 21mb sheet with the source data and INPUT sheet within.
Sidenote - surely there's a quicker key-stroke way of copying down/across formula than dragging the cell range and watching excel slowly range down as fast as the mouse can pull it?

If the formula is next to a column that is full of data, you can double-click the fill handle, which is the small black square in the bottom right corner of the cursor, when the cell with the formula is selected. This will automatically copy the formula down the column to the end of the (contiguous) set of data in the adjacent column.

Hope this helps.

Pete

Thanks Pete - yes, it's that first column of data, that first 'drag down' I was looking for a shortcut for, but I guess the 'long scroll' is still needed

I just lost the reply to you as I had used angle brackets around the keys to use in the description and the firewall blocked the reply - I'll use bold in this reply instead.

Suppose you have a formula in A2 that you want to copy down to cell A25000.

Press the F5 key (GoTo) and enter A25000 in the Reference box and press Enter, and that will take you directly to the cell.
You should type a character into that cell (I usually use a full-stop, but you can put anything in), and instead of pressing Enter, you should press the End key once followed by Up-arrow, and that should take you back to cell A2.
Click on Copy then move the cursor into cell A3 and hold down the Shift key.
Press the End key once, followed by Down-arrow, and then you can release the Shift key. This should have highlighted all the cells in the range A3:A25000.
Then you can just press Enter and the formula will be copied down.

This takes a few seconds (it's certainly longer to describe than to carry out).

Then if you have a formula in B2 you can just double-click the fill handle to copy that down.

Hope this helps.

Pete

Thanks Pete - I shall try this tomorrow - just finished adding my dataset to your example and all is working well.

Part of the time problem I was having was the file being stored on network, and for editing, I have sped things up massively by it being on my desktop.

I also found some code to sort the sheet that the VBA ended on when pressing the button; Application.Goto Sheets("OUTPUT").Range("A1"), True

Added to the end of the code, works a treat.

THANK YOU for your help - will do final tweaks tomorrow, confirm all works, and move on.

PS filesize is 21mb, but much of that is the source data itself (before the IMPUT cleans it) which can't be helped for various reasons at this end