Data Retrival from selected RANGE with Multiple criteria

Hello,
I have years of data in column N to T
and I would like to retrieve specific data in column A to G based on the year input I choose.
I have attached a sheet with sample data and desired result. (No formulas)
I have tried with MATCH INDEX but I am only able to retrieve 1 Year at the time and not multiple as desired.

Can multiple criteria be used for the retrieval of data?

Thank you for your time
NIno

I'm not sure if you are using column M for anything in your real file, but if not you can put this formula in cell M5:

=IF(AND(N5>=DATE($K$5,1,1),N5<=DATE($K$6,12,31)),MAX(M$4:M4)+1,"-")

then copy it down to the bottom of your data by double-clicking the fill handle (the small black square in the bottom right corner of the cursor) with M5 selected.

Then you can use this formula in cell A5:

=IFERROR(INDEX(N:N,MATCH(ROWS($1:1),$M:$M,0)),"")

Copy this across into cells B5:G5, ensuring that A5 is formatted as a date and B5:G5 as numbers. Then you can copy those 7 cells down as far as you need to, and if you change the start and/or end years the displayed values will change accordingly.

Hope this helps.

Pete

Hello Pete,
thank you for the formula and your time.
It works as I expected but sometime I have to enter manually a text in column N where the original Data resides.
And when I do that the formulas skips the entire row.

For instance:
If the original data is:
1/2/1998 725.0 8,475.0 725.0 8,475.0 87.0 5.0
1/5/1998 8,425.0 275.0 765.0 87.0 27.0 65.0
1/6/1998 87.0 8,775.0 75.0 7.0 877.0 5.0
1/7/1998 775.0 7,475.0 65.0 74.0 77.0 65.0
1/8/1998 7,375.0 7,725.0 65.0 625.0 772.0 65.0
1/9/1998 4,625.0 63.0 255.0 25.0 63.0 55.0

with your formula I get the desired result:
1/2/1998 725.00 8,475.00 725.00 8,475.00 87.00 5.00
1/5/1998 8,425.00 275.00 765.00 87.00 27.00 65.00
1/6/1998 87.00 8,775.00 75.00 7.00 877.00 5.00
1/7/1998 775.00 7,475.00 65.00 74.00 77.00 65.00
1/8/1998 7,375.00 7,725.00 65.00 625.00 772.00 65.00
1/9/1998 4,625.00 63.00 255.00 25.00 63.00 55.00

but the moment I enter a text in any N cell (Date) such as:
1/2/1998 725.0 8,475.0 725.0 8,475.0 87.0 5.0
1/5/1998 8,425.0 275.0 765.0 87.0 27.0 65.0
1/6/1998 87.0 8,775.0 75.0 7.0 877.0 5.0
Not Good 775.0 7,475.0 65.0 74.0 77.0 65.0
1/8/1998 7,375.0 7,725.0 65.0 625.0 772.0 65.0
1/9/1998 4,625.0 63.0 255.0 25.0 63.0 55.0

The result skips the row completely and i don't want to loose the info beside the text,so with your formula i get the following as a result:

1/2/1998 725.00 8,475.00 725.00 8,475.00 87.00 5.00
1/5/1998 8,425.00 275.00 765.00 87.00 27.00 65.00
1/6/1998 87.00 8,775.00 75.00 7.00 877.00 5.00
1/8/1998 7,375.00 7,725.00 65.00 625.00 772.00 65.00
1/9/1998 4,625.00 63.00 255.00 25.00 63.00 55.00
1/12/1998 2.00 4,825.00 45.00 455.00 82.00 45.00

As you can see the data from 1/7/1998 is not appearing.

Thanks again for you help
Cheers
Nino

Hi Nino,

you need to change the formula in M5 to this:

=IF(OR(ISTEXT(N5),AND(N5>=DATE($K$5,1,1),N5<=DATE($K$6,12,31))),MAX(M$4:M4)+1,"-")

then copy down to the bottom of your data.

The attached file shows this.

Hope this helps.

Pete

Hi Pete,
I wanted to thank you for your patience and your prompt response as well
Much appreciated.

Having done some test to your revised formula i discovered a small issue,

Whats Happening with The revised formula in M column is, that although its doing all I wanted to do, one
thing is not working right.

If you Note Cell K5 and K6 both criteria are set for the year 1998
although your formula does retrieve all data only from Year 1998 (including text cells in 1998)
it also retrieves data from all populated cells containing "Text", regardless of the set Year-criteria.

I have been playing with the formula in M column By adding IF statements but no luck,
I have reattached your last file highlighting the problem.

Thank you once more
Cheers
Nino

The trouble is where you have text in column N there is no date to refer to, so you can't say "ensure it is within the years specified by the start and end dates" within the formula. Visually, we could look at the cell before or after the one with text to check on the year, but if you then have 2 consecutive cells with text in then you would need to look further afield - again, this is easy if done manually, but more difficult to achieve by formula.

Why is there a need to put text values in some of those cells in column N?

Could those text cells also incorporate the year, like "Test 1999" or "no good 1998"? The formula could then take account of that and ensure that it only reported on the required years.

I'm going out soon, but I'll check back for your comments when I get back in.

Hope this helps.

Pete

Hello Pete,
Thank you for your response..
Yes, I can do text cells incorporate the year, like "Test 1999" or "no good 1998"

I have tried doing that with both of your formulas but I get same result..
Perhaps i miss-understood you.

Thank you for trying Pete
Cheers
Nino

Okay, you need to change the formula in M5 to this:

=IF(OR(AND(ISTEXT(N5),RIGHT(N5,4)*1>=$K$5,RIGHT(N5,4)*1<=$K$6),AND(N5>=DATE($K$5,1,1),N5<=DATE($K$6,12,31))),MAX(M$4:M4)+1,"-")

then copy down. You also need to ensure that the last 4 characters of your text values incorporate the year, otherwise the RIGHT(N5,4)*1 term will produce #VALUE errors in column M - I've done this in the attached file and added a few more text values for 1998 (all coloured green).

Hope this helps.

Pete

Hello Pete
Thank you so much, its always a good feeling to see things work flawlessly

Thank you once more for your affords Pete. Its working great.
All the best
Nino

Glad to hear that, Nino, and thanks for the rep.

While I was waiting for your reply in Post #7 I tried it another way by taking the MAX of the 4 cells before the one with text in and the MIN of the 4 cells after to try to see if the years of those cells fell between the range of dates specified. That cleared all the text cells which were outside the range, but it failed to pick up the text cells if they were the first of the year or the last of the year, as the dates of the surrounding cells were in a different year, so I'm glad that you said you were able to include the year within the comments.

Cheers,

Pete

Hi Pete, Hope you have been well
I have been advancing with my excel project till i reached a wall.
I am hoping you will have time to have a look at it.
Its the same file you worked on for the transposing formula with Text and year. Its now Called MA1104 issues.

If you try to change percentage in red Cell S13 you will see column F begins to act weird and gives me #NUM! results.

Not sure why that is

Thank you Pete
Cheers
Nino

I don't have the AGGREGATE function in XL2007, and you have used this in column F, so I'm afraid I can't help you with that.

Pete