"gcotterl" wrote in message
...
On Feb 3, 1:36 pm, "Clif McIrvin" wrote:
010633143-5 2004,010633143-5 2005,010633143-5 2006,010633143-5
2007,010633143-5 2008,010633143-5 2009,,,,

How can I clear a cell in cols R thru AA if it matches the "control-
number" (in col A) AND the "year" in cols B thru I

I don't know of a way to clear a cell without using a macro or the
user
interface...

Are you a VBA programmer at all? You can use the split() function in
VBA
to create an array: [ TestValue = split(range("R1")," ") ] would give
you:
TestValue(0) as the control number in R1, and
TestValue(1) as the year in R1

From there you can test against A1 for the control number, and use the
find method against B1:I1.

You can use the macro recorder to see how to use the find method.

Maybe this will get you started.

<...

No, I'm not familiar with VBA.

I guess I'll have to visually compare the control-number (in col A)
and the year (in cols B thru I) with the contents of the cells in cols
R thru AA and delete the matches. (Col A has 2,900 rows and col RR
has 5,000 rows)

------

Maybe this will help....

(Ocasionally I see a discussion where a more experienced user presents a
solution using COUNTIF or some other function ... sometimes as an array
formula. I don't have a lot of experience in that way ... but looking at
the help for countif might give you some ideas.)

I loaded the sample data you provided into a blank worksheet, with the
first set of data in A1:E11, and the second set in A13:J28, then I
entered this formula in M13:

=CELL("address",INDEX(A13:J13,1,MATCH(A1,A13:J13)) )

drag it down to M28, and it will give give you a list of the (first)
cell in each row that contains the control number in A1. Something like
this might take some of the pain out of your task.

Is this something that will need to be done again? If so, it's probably
worthwhile to learn how to create macros and customize the VBA code ....

There's probably a way to do this using SQL queries, too.

HTH!

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)