Calc: Searching for values in a certain range string

users
#1

Hi,

I am looking for a pointer or two to enable me to accomplish what I seek.
What I seek is to be able to have a sheet of Lottery results downloaded from the Internet and pasted into a Calc sheet. On another Calc sheet I will have a listing of the 6 numbers held for each specific draw date.

Each populated cell of my ticket sheet will indicate if the number in that cell is the same as a number in a range of cells from the results sheet, on a particular date.

As an example:

Assume the draw results below dated drew the following numbers:
04/02/2014 16, 03, 19, 21, 23, 06
11/02/2014 17, 05, 02, 20, 22, 04
18/02/2014 09, 40, 05, 09, 42, 03

Also assume that draw numbers I chose for a draw on a date were:
11/02/2014 02, 05, 18, 20, 22, 04

In theory looking at the data, if I submitted my ticket for prize redemption, I might score very well considering my ticket matches 5 out of 6 of the numbers from the draw. If any number on my ticket matches a corresponding date value, I want the cell to change background colour.

Now the trick is to put this in practice. :slight_smile:

After looking into the usage of vlookup and given I want the background colour to change if the conditions are met, I selected the cell containing the first number on my ticket (02) and then selected 'Format', Conditional Formatting'.

I selected that the cell value is equal to and entered vlookup($LottoResults.$B$39,$LottoResults.$C$39:$D$39,1,0) into the field, chose the new background style and said OK.

I checked the first number of my ticket on the specified date and noticed that the background had not changed as instructed to by the conditional formatting. :frowning:

Using the other option on Conditional Formatting I selected 'Formula is' and entered the same vlookup formula wit the same disastrous results, i.e. no colour change on the first cell of my ticket number :frowning:

Doing a second trial I said cell value must equal and entered $LottoResults.$C$39.
The cell on my ticket came out the requested colour however the formula did not allow me to check if on the ticket date and corresponding ticket draw were identical. :frowning:

The purchased ticket has 6 numbers on it, with the first 5 being in numerical order and the 6th being a bonus random ball( 01-49).
The draw results are not in numerical order.

So close yet so far, what am I missing?

pointers?

Regards
Hylton

#2

What I seek is to be able to have a sheet of Lottery results downloaded from the Internet and pasted into a Calc sheet. On another Calc sheet I will have a listing of the 6 numbers held for each specific draw date. Each populated cell of my ticket sheet will indicate if the number in that cell is the same as a number in a range of cells from the results sheet, on a particular date.

I'm guessing (since you don't say) that the ticket sheet is not the results sheet?

As an example:

Assume the draw results below dated drew the following numbers:
04/02/2014 16, 03, 19, 21, 23, 06
11/02/2014 17, 05, 02, 20, 22, 04
18/02/2014 09, 40, 05, 09, 42, 03

Also assume that draw numbers I chose for a draw on a date were:
11/02/2014 02, 05, 18, 20, 22, 04

If any number on my ticket matches a corresponding date value, I want the cell to change background colour. After looking into the usage of vlookup and given I want the background colour to change if the conditions are met, I selected the cell containing the first number on my ticket (02) and then selected 'Format', Conditional Formatting'. I selected that the cell value is equal to and entered vlookup($LottoResults.$B$39,$LottoResults.$C$39:$D$39,1,0) into the field, chose the new background style and said OK.

I think you are getting confused here: are you trying to search two things with one test? You presumably need to search the various date rows for the appropriate date, and for this VLOOKUP() might be appropriate. But you haven't attempted that here: you have mentioned only cells in row 39. Yes, you may then also need to search the row for a matching draw result, but that is along a row, not down a column, so HLOOKUP() might be more appropriate for that. Don't you need both?

You have also said that the ticket numbers are on a separate sheet from the draw numbers, but your search is confined to the results sheet. You appear to be searching to see if a draw result happens to be equal to the date on which it was drawn! That would happen on 11 February 2014 only if a number drawn were 41681! Now I can't speak for South African lotteries, but ...

Using the other option on Conditional Formatting I selected 'Formula is' and entered the same vlookup formula with the same disastrous results, ...

That tests the formula for zero result. How could your retrieved draw result be zero? Now if you subtracted your ticket number from the draw result ...

So close yet so far, what am I missing?

The need to apply two searches or tests. Oh, and the need to specify correct arguments.

pointers?

See above.

I trust this helps.

Brian Barker

#3

Suppose the results are on a Results sheet in columns A to G, starting in row 1. Suppose also that the draw numbers are on a Tickets sheet in the same columns, again starting in row 1.

o Create a cell style with a suitable background colour.
o Select cell Tickets.B1.
o Go to Format | Conditional Formatting... .
o Tick Condition 1.
o Select "Formula is".
o For Cell Style, select your cell style with the coloured background.
o In the formula box, enter NOT(ISNA(MATCH(B1;OFFSET(Results.$B$1:$G$1;MATCH($A1;Results.$A$1:$A$99;0)-1;0);0))) .
o With Tickets.B1 still selected, click the Format Paintbrush button in the Standard toolbar.
o Drag the paintbrush icon from Tickets.B1 across to G1 and down as many rows as necessary.

How does it work? The inner MATCH() function searches the date column of the Results sheet and returns the row number of the matching date. The OFFSET() function then creates a reference to the range of results (Bn:Gn) for that date. The outer MATCH() function then searches that range of results for the individual ticket number, returning a column number (which we don't use) if it is found or the error value #N/A if it is not. The ISNA() function tests for #N/A, so returns TRUE if the ticket number was not found, and the NOT() function inverts this, thus returning TRUE for ticket numbers that are matched.

Note that you may need to force recalculation of cells before you see new colouring if you modify the data.

I trust this helps.

Brian Barker

#4

Lotto.ods <http://nabble.documentfoundation.org/file/n4096842/Lotto.ods>

Brian and other interested folk

Thanks for the formula! I am going to need a few days to see how it works,
but it certainly has promise, however I cannot see the different sheets that
the ods file contains.

Remember: There is one sheet for the tickets purchased, another sheet for
the draw results.
The background colour of a number on the tickets sheet is going to change
colour if it is the same as any number shown on the corresponding date of
the draw sheet.

Have a look at the file attached via Nabble for a better view, and no
misunderstandings.

Ascertain if on a certain date on the Tickets sheet, on the same date in
the corresponding draw sheet, if any of the numbers match. If so colour the
cell background of each matching number on that same date via conditional
formatting.

Example with fictitious data:
Ticket sheet
01/02/2014 02 05 07 35 37 39

Draw sheet
01/02/2014 37 07 09 35 05 16

Therefore the cells on the Ticket sheet holding the numbers 07, 35, 05 would
have a background colour that is not white :slight_smile: If the 6th number on the
ticket and in the draw is the same the cell background needs to be a
different colour to the others.
This indicates to me that I have won some cash.

Hopefully this clarifies matters a bit and hope others can use the
attachment.