PLS HELP! Look up and cross off

[LEVIguy]

Hi,

I'm hoping someone could help me with a little project that I'm stuck on... What I'd like to do.

The first section would include 25 columns across by 400 rows.
The second section would include 5 columns across by 40 rows.

If I fill in the first section with numbers... If should put in a number in the second section that matches any number in the first section ... that it would cross it off in the first section..

I.E. If I put the number 5 in the first section and then put the number 5 anywhere in the second section, the 5 in the first section would be crossed off

... PLEASE... If you can help me... email me at [email protected]...

THANKS... I'm also on MSN at [email protected]

THANKS !

[Cutter]

You can do it with Conditional Formatting

Highlight the range of cells where the numbers will appear in your first section.
Now click on Format > Conditional Formatting

In the first drop down choose: Formula is
Now in the formula space type: =COUNTIF(
Now reselect your second section
You will now see your range appear with $ included
Type a comma followed by the cell address of the upper left cell in your first section (no $ signs)
Now type a closing bracket followed by >0

Your formula should look like: =COUNTIF($AA$2:$AE$41,A2)>0

Now click on the Format button
You can choose the Strikethrough effect on the Font Tab but you'll probably be better off choosing a red colour and bolding (or even change the background colour from the Patterns Tab)

After choosing your formatting click OK
You'll see a preview of how your cell will appear when the condition is met
If you like it click OK again

You're done.

[LEVIguy]

okies... sounds great and understand everything but this part

"Type a comma followed by the cell address of the upper left cell in your first section (no $ signs)"

Thanks

[Cutter]

Your formula should look like: =COUNTIF($AA$2:$AE$41,A2)>0

The $AA$2:$AE$41 refers to your second section (5 columns, 40 rows)

then type a comma

and then type the cell address of the upper left cell of your first section (25 cols, 400 rows) so if your first section is from A2:Y401 then you use A2 (just make sure you don't have a $ sign before or after the A)

Adjust the formula to match the ranges you are using for your first and second sections

[LEVIguy]

TOTALLY AMAZING... I've been stuck on this for days now...

If its not pushing it...

What I need to do now...

in each row... any number that has not been scratched off I need a count... Not to add up the numbers... but....

I.E

Say out of 24 numbers.... 4 have been scratched out... I should have a count that says there are 20 numbers left...

Thanks so much for you help... this is a total JOB saver!

Kristopher

[Cutter]

I have a solution for you but it involves using an area equal in size to your first section (25 cols by 400 rows) as well as one column of 400 rows to give the count of numbers not crossed out in each row of your first section

I'm sure there is an easier way but I don't know it so I have started a new thread to get help on it.

If you want to go with my solution it is this:

Choose an area on your sheet, say BA2:BY401, to put in a formula
Without highlighting this entire range select BA2 and type this formula:
=IF(COUNTIF($AA$2:$AE$41,A2)=0,A2,"")
NOTE: I'm using the same ranges I used from previous posts, change if necessary

Now drag/copy this formula into the entire range BA2:BY401

Now in a column beside your first section, say Z2, type:
=COUNT(BA2:BY2) (referring to the new third section)
Now copy this formula down the 400 cells

This will give you the number of numbers in each row of your first section that have not appeared in your second section (therefore not "crossed off")

[Cutter]

I have received info from Olasa that will help you.

This is the easier way that I couldn't come up with:

In a cell to the right of your first row of your first section, I'll assume Z2, type:

=SUM(--(COUNTIF(AA$2:AE$41,A2:Y2)=0))
and then enter this as an array formula by holding Ctrl+Shift+Enter
Now drag copy this formula down the 400 rows
NOTE: Again, I used ranges from earlier examples, change to suit

This does what my solution does but without creating a third section.