Extract reference numbers in given month with date?

[domgilberto]

Hoping someone can help me with this one. I have attached an example spreadsheet which should be relatively intuitive over me trying to explain it.

I am wanting to extract all CJ numbers with the corresponding dates from Sheet 1, into Sheet 2. It has to factor in, engineer name (as stated on sheet 2), the Month (as stated in sheet 2 "May") with the "Attended site" date in sheet 1....

If you see the worksheet it should give you the whole picture with a fair amount of clarity

Thanks very much for your help in advance!!

[playaller]

Hello,

Under what condition should everything be transferred or should everything be transferred over? and how do you want it initiated, based on a date you iput or...? Need just a little more specifics.

[alansidman]

If you change the value in Sheet 2, C3 to a numerical value, ie. For May, change to 5, then the following VBA will work for you. The value in C3 must be the number of the month

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[JBeaucaire]

Here's a non-vba solution using array formulas:

B5: =IFERROR(INDEX(Sheet1!$C$1:$C$34, SMALL(IF((Sheet1!$B$1:$B$34=$B$2)*(TEXT(Sheet1!$C$1:$C$34, "MMMM")=$C$3)*(Sheet1!$J$1:$J$34="Yes"), ROW(Sheet1!$B$1:$B$34), ""), ROW(A1))), "")

C5: =IFERROR(INDEX(Sheet1!$G$1:$G$34, SMALL(IF((Sheet1!$B$1:$B$34=$B$2)*(TEXT(Sheet1!$C$1:$C$34, "MMMM")=$C$3)*(Sheet1!$J$1:$J$34="Yes"), ROW(Sheet1!$B$1:$B$34), ""), ROW(A1))), "")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Once the B5 and C5 answers appear correctly, copy those two cells down the table.

[JBeaucaire]

Alan, a tweak to your macro that would allow him to still put "May" or "September" in C3:

Please Login or Register  to view this content.

[domgilberto]

Here's a non-vba solution using array formulas:

B5: =IFERROR(INDEX(Sheet1!$C$1:$C$34, SMALL(IF((Sheet1!$B$1:$B$34=$B$2)*(TEXT(Sheet1!$C$1:$C$34, "MMMM")=$C$3)*(Sheet1!$J$1:$J$34="Yes"), ROW(Sheet1!$B$1:$B$34), ""), ROW(A1))), "")

C5: =IFERROR(INDEX(Sheet1!$G$1:$G$34, SMALL(IF((Sheet1!$B$1:$B$34=$B$2)*(TEXT(Sheet1!$C$1:$C$34, "MMMM")=$C$3)*(Sheet1!$J$1:$J$34="Yes"), ROW(Sheet1!$B$1:$B$34), ""), ROW(A1))), "")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Once the B5 and C5 answers appear correctly, copy those two cells down the table.

Thanks for everyones input. I've dug into this one to start off with.

Actualy formula I am using is this one:

Please Login or Register  to view this content.

Problem is though, it's just returning a "0" now and again? Cannot figure out why that is?

As for the date, I am not as fussed about the specific dates for time being. The "CJ" number is the important part, but it's not pulling it properly?

Am I doing something wrong in that formula?

[JBeaucaire]

If your formula is bringing back even one correct value, then it's probably the correct formula and the reason for 0 is that the same formula is resolving to a row with no value due to an error in your data. To suggest what that error might be I'd have to see it happening.

[domgilberto]

I've actually sorted it now. I think I was applying your formula incorrectly! Thank you kindly, and everyone else for their solutions!

Appreciate it

[domgilberto]

If your formula is bringing back even one correct value, then it's probably the correct formula and the reason for 0 is that the same formula is resolving to a row with no value due to an error in your data. To suggest what that error might be I'd have to see it happening.

Could you elaborate on this? I am actually experiencing this issue after expanding on your original formula with this:

=IFERROR(INDEX('Overview - 2014'!$G$15:$G$5000, SMALL(IF(('Overview - 2014'!$B$15:$B$5000=$B$3)*(('Overview - 2014'!$C$15:$C$5000>=DATE(2014,4,28))*('Overview - 2014'!$C$15:$C$5000<=DATE(2014,5,27)))*('Overview - 2014'!$J$15:$J$5000="Yes"),ROW('Overview - 2014'!$B$15:$B$5000), ""), ROW(A1))), "")

I know it's tough to make out by glancing at it like this, but I have literally checked over my data points several times and cannot for the life of me see where I am going wrong...

So if it's returning "0" it definitely means that there is something wrong with the formatting of the data? Either way, when i FIRST put this into the relevant box and SHIFT+CTRL enter, it will always produce the right answer in that box, then when I try to simply drag it down it will produce a "0" if there is an applicable answer, however, it should be "CJxxxxx" (example).

Any ideas?

[domgilberto]

Any suggestions?

[JBeaucaire]

In my original formulas, I explicitly start the search range at row1 in each "range" of cells in the formula due to the fact the MATCH function in the formula doesn't need to be adjusted when you do that. Since your new version of the formula starts all the ranges at row15, then the MATCH function would need to be adjusted by 14 so the results now work in the INDEXed range.

Simplest answer, change all the 15 references to 1 and see if that resolves the issue.

Less simple to my mind, but still works, adjust the ROW() reference by 14 to match the INDEXed range:

=IFERROR(INDEX('Overview - 2014'!$G$15:$G$5000, SMALL(IF(('Overview - 2014'!$B$15:$B$5000=$B$3)*(('Overview - 2014'!$C$15:$C$5000>=DATE(2014,4,28))*('Overview - 2014'!$C$15:$C$5000<=DATE(2014,5,27)))*('Overview - 2014'!$J$15:$J$5000="Yes"),ROW('Overview - 2014'!$B$15:$B$5000)-14, ""), ROW(A1))), "")

[domgilberto]

Amazing - spot on! I think I understand.

Really appreciate your expertise!