Thread: Sort or filter data

Greetings,

I have a workbook with 2 sheets that I'm using to track lottery winning numbers for my lottery group.

Sheet 1 - Data
The winning numbers plus bonus number.

Row 1 - A1:AY1
Date 1 2 3 .. 49 Bonus
Row 2 - B2:I2 with winning numbers (note the last number (in I2) is the bonus number)



Sheet 2 - Lottery
Where I want to the winning numbers filtered ? / sorted ? to it's appropriate column.
Row 1 - same as Row 1 in Data Sheet ( Date 1 2 3 .. 49 Bonus)


I've attempted to make this work with the following and thought I was onto something until
column H

=IF(data!$B$2:data!$H$2>=F1,F1,"x") - this works but only too column H
=IF(data!$B$2:data!$H$2<=I1,I1,"x") - this returns #VALUE! error


=IF(data!B2:H2=1,1,"x") - it works .. sort of. Only returns X


=IF(data!$B$2:data$H$2<=B1:AX1," ","x") - this seemed to make sense until I applied it. It's invalid - whatever that means at this point I haven't a clue.

Any help / direction you can offer would be appreciated (not too mention relieving my frustration attempting to make this work).

TIA

Can you upload example?

Avoid important data but keep correct formating

I've added an attachment of the spreadsheet

Hi, osrry I couldn't solve this yesterday.
I'm not sure I getting what you up to...

Is this it?

if not, please in your example write more input parameters (for example I don't know why on first sheet COLUMNS (not rows) are up to AY1 while there can be only 7+1 number...

Also, write in example desired output.

lottery(1).xls

Originally Posted by zbor

Hi, osrry I couldn't solve this yesterday.
I'm not sure I getting what you up to...

Is this it?

if not, please in your example write more input parameters (for example I don't know why on first sheet COLUMNS (not rows) are up to AY1 while there can be only 7+1 number...

Also, write in example desired output.

Zbor,

Thank you for your efforts, I do appreciate it.

You almost have what I have hoped for.

Instead of the 'x' I had hoped for the value.
i.e. on first row the first value is 5 then 5 would appear in F2 not 'x'


The second part may be a little harder. The bonus number - on Sept 25 is 4 - I had hoped to have each bonus number appear in the COLUMN bonus

I've provide an example in the file.

Thanks again Zbor.

Sorry, don't have time to review your workbook right now but here is a MegaLottery (5 numbers and bonus ball) workbook I created. Maybe you can adapt.

I've managed to get the desired result editing Zbor solution.

Mods -- kindly marked solved. Thanks


Thanks to Zbor for his efforts and ChemistB for suggestion