Selecting a range of values and returning the whole line

[John Sio]

Hi,

I have a sheet with 10,000 lines of entry. My example below is only 5 lines just to describe my query.

I would like to be able to select a range of values from a column (i.e. select from column A entries in the range 1.6 through to 1.9) and return the whole line to another sheet or elsewhere. In other words the whole of line 2 through to line 5 will be selected and copied elsewhere.

Can this sort of copy/paste be performed without manually using copy/paste functionality?

A B C D E F G H I J K L M N O P
1.5 6.0 7.0 1.2 1.8 3.5 6.0 3.0 180 350 4 1 1 1 0 6
1.6 3.0 7.0 2.3 7.0 1.8 3.0 1.5 180 350 4 0 2 1 3 3
1.7 3.0 3.0 1.5 3.0 3.0 3.0 1.0 185 320 2 1 0 0 1 2
1.8 4.5 4.0 2.0 1.6 8.0 9.0 3.0 185 325 6 1 2 2 2 4
1.9 6.0 3.0 2.0 1.5 6.0 6.0 1.5 185 350 4 1 1 2 1 3

Thanks

John Sio

[FDibbins]

Hi and welcome to the forum

See if the attached is something you can work with?

On sheet2 change the values in B1:B3 to see the table below change accordingly

[John Sio]

Thank you, I appreciate your reply.

However, i am pretty much a newcomer when it comes to excel so looking at your formulas/code etc seems very in depth to me.

Regardless, i will try and work it out. I will most probably be back with more questions!

Thanks again

[FDibbins]

Ask away if you dont understand anything

On sheet1, the helper column 1st tests the column (in the sample, column A) for values between the criteria set on sheet2 B2 and B3. If it is within the criteria, it adds a 1 to the highest value above it (so, for the 1st match, it adds 1 to 0, for the next match, it adds 1 to 1, and so on)

Then on sheet1, I use this increasing sequence of numbers to select teh row to return the values from. With a vlookup or index/match, it will only ever give you the 1st instance of a "find", so once it has found "1", it wont look for another "1"

[John Sio]

thanks again

fantastic work