Powerball Reconciliation

[ed9213]

OK, I'm the new guy and was given the task of reconciling the winning 2010 Powerball numbers with the team's purchased tickets.
They don't think they've won, but I have to prove it. Yes, they're all QuickPicks.
I've tried Excel Conditional Formatting, row-by-row comparisons, but the means of automating this process is escaping me.
I have the list of winning numbers, and lists of numbers purchased.
Excel likes to compare columns, but even transposing the rows into columns hasn't worked for me.
I have to compare the winning numbers in one row with the rest of the numbers on only that same row, highlight the duplicates, then do the process again for each ticket date.
It ought to be a simple find duplicates in the same row and highlight them, but it's not working out.
Internal Excel features can't handle it, and I'm sure VB will, but I don't know how to go about it.
The sample spreadsheet is attached. Only the duplicate numbers on the same row need to be highlighted.
Thanks, in advance, for your help.
Ed9213

[Bob Phillips]

Take a look at this

[ed9213]

I can't see your reply. Was it attached, or don't I have permission to see attachments?
Thanks.
Ed9213

[Bob Phillips]

Yes, I attached a file.

[ed9213]

Now I saw it. That was what I was trying to do! I don't see how you accomplished it. Would you share?

[ed9213]

Found it! I overlooked that! Very elegant!

Thanks, again!
ed9213

[Bob Phillips]

Sure, it is simply conditional formatting.

I selected all of the cells in one block, and added a formula in conditional formatting of

=ISNUMBER(MATCH(M3,$C3:$H3,0))

and used a fill colour of green.

I then copied the format of that first block to each of the other blocks.

[ed9213]

Is there a simple way to add another piece to that formula (or another condition) to highlight the source cell (as M3 in your example) if, and only if, there is a match? Right now I have a similar formatting formula in the source cells going the other way, but if I can use the just the first formula, I don't have to worry about how many columns of data there are to make sure the range in the source cells includes them all.
Thanks, again.

[Bob Phillips]

I'm not sure I understand the difference between what I gave you, and what you are now asking.

Can you re-phrase it, perhaps with an example?

[ed9213]

Xld,
Using your example above =ISNUMBER(MATCH(M3,$C3:$H3,0))
If M3 is a match to one of the cells in the $C3:$H3 range, it is highlighted. Using another condition or element, what would it take to highlight the cell that was matched?
Right now I have another conditional formula in the source cells (the lookup_array) that reaches out and looks for matching cells in the data columns, but I was wondering if that mission could be accomplished during the original matching process.
Hope that is clearer.
Thanks, again.
ed9213

[Bob Phillips]

If I understand correctly, that would not work. Normally, you could use an OR condition, but as one column is A and another looks left 12 columns that won't work.

Have two separate CFs seems the best solution AFAICS.