Clear cells

[gcotterl]

Each cell in col A contains a "control-number".
Each cell in cols B thru I contains a "year".
For example:

010595369-6,2008,2009,,,,,,
010595370-6,2008,2009,,,,,,
010597700-7,2009,,,,,,,
010597703-0,2007,2008,2009,,,,,
010598830-4,2008,2009,,,,,,
010599037-8,2007,2008,,,,,,
010631923-7,2009,,,,,,,
010632739-1,2005,2006,2007,2008,,,,
010632814-5,2009,,,,,,,
010633029-6,2009,,,,,,,
010633143-5,2009,,,,,,,


Each cell in cols R thru AA contains a "control-number" AND a year.
For example:

010595369-6 2003,010595369-6 2004,010595369-6 2005,010595369-6
2006,010595369-6 2007,010595369-6 2008,010595369-6 2009,,,

010595370-6 2003,010595370-6 2004,010595370-6 2005,010595370-6
2006,010595370-6 2007,010595370-6 2008,010595370-6 2009,,,

010597700-7 2004,010597700-7 2005,010597700-7 2006,010597700-7
2007,010597700-7 2008,010597700-7 2009,,,,

010597703-0 2004,010597703-0 2005,010597703-0 2006,010597703-0
2007,010597703-0 2008,010597703-0 2009,,,,

010597969-4 2004,010597969-4 2005,010597969-4 2006,010597969-4
2007,010597969-4 2008,010597969-4 2009,,,,

010598830-4 2003,010598830-4 2004,010598830-4 2005,010598830-4
2006,010598830-4 2007,010598830-4 2008,010598830-4 2009,,,

010599037-8 2003,010599037-8 2004,010599037-8 2005,010599037-8
2006,010599037-8 2007,010599037-8 2008,010599037-8 2009,,,

010630761-4 2007,010630761-4 2008,010630761-4 2009,,,,,,,

010631923-7 2004,010631923-7 2005,010631923-7 2006,010631923-7
2007,010631923-7 2008,010631923-7 2009,,,,

010632313-9 2009,,,,,,,,,

010632314-0 2009,,,,,,,,,

010632739-1 2000,010632739-1 2001,010632739-1 2002,010632739-1
2003,010632739-1 2004,010632739-1 2005,010632739-1 2006,010632739-1
2007,010632739-1 2008,010632739-1 2009

010632814-5 2004,010632814-5 2005,010632814-5 2006,010632814-5
2007,010632814-5 2008,010632814-5 2009,,,,

010632843-1 2009,,,,,,,,,

010633029-6 2005,010633029-6 2006,010633029-6 2007,010633029-6
2008,010633029-6 2009,,,,,

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

 

[Clif McIrvin]

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.

Clif

 

[gcotterl]

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.

Clif

Each cell in col A contains a "control-number".
Each cell in cols B thru I contains a "year".
For example:

Each cell in cols R thru AA contains a "control-number" AND a year.
For example:

010595369-6 2003,010595369-6 2004,010595369-6 2005,010595369-6
2006,010595369-6 2007,010595369-6 2008,010595369-6 2009,,,

010595370-6 2003,010595370-6 2004,010595370-6 2005,010595370-6
2006,010595370-6 2007,010595370-6 2008,010595370-6 2009,,,

010597700-7 2004,010597700-7 2005,010597700-7 2006,010597700-7
2007,010597700-7 2008,010597700-7 2009,,,,

010597703-0 2004,010597703-0 2005,010597703-0 2006,010597703-0
2007,010597703-0 2008,010597703-0 2009,,,,

010597969-4 2004,010597969-4 2005,010597969-4 2006,010597969-4
2007,010597969-4 2008,010597969-4 2009,,,,

010598830-4 2003,010598830-4 2004,010598830-4 2005,010598830-4
2006,010598830-4 2007,010598830-4 2008,010598830-4 2009,,,

010599037-8 2003,010599037-8 2004,010599037-8 2005,010599037-8
2006,010599037-8 2007,010599037-8 2008,010599037-8 2009,,,

010630761-4 2007,010630761-4 2008,010630761-4 2009,,,,,,,

010631923-7 2004,010631923-7 2005,010631923-7 2006,010631923-7
2007,010631923-7 2008,010631923-7 2009,,,,

010632313-9 2009,,,,,,,,,

010632314-0 2009,,,,,,,,,

010632739-1 2000,010632739-1 2001,010632739-1 2002,010632739-1
2003,010632739-1 2004,010632739-1 2005,010632739-1 2006,010632739-1
2007,010632739-1 2008,010632739-1 2009

010632814-5 2004,010632814-5 2005,010632814-5 2006,010632814-5
2007,010632814-5 2008,010632814-5 2009,,,,

010632843-1 2009,,,,,,,,,

010633029-6 2005,010633029-6 2006,010633029-6 2007,010633029-6
2008,010633029-6 2009,,,,,

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

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket - Hide quoted text -

- Show quoted text -

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)

 

[Clif McIrvin]

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!

 

[Charabeuh]

Hello,

I don't know if I have understood what you want to do.
I built a file that you can download here.

http://www.cijoint.fr/cj201102/cij1Qug66O.xls

Hope it will help you !






gcotterl avait soumis l'idée :

 

[gcotterl]

Hello,

I don't know if I have understood what you want to do.
I built a file that you can download here.

http://www.cijoint.fr/cj201102/cij1Qug66O.xls

Hope it will help you !

Your understanding was perfect!!!!! The results are exactly what I
was looking for.

Thanks!!!!

 

[Clif McIrvin]

It took me a while, but with the aid of the formula auditor I understand
what you did.

Thanks for showing me something new!

Clif

 

[Charabeuh]

Hello,

Thanks for the feedback.


gcotterl avait soumis l'idée :

 

[Charabeuh]

Hello,

Thanks for the feedback.

What I know about excel, I have learned it on Excel forums
(MS Forum and now MS Answers forum)



Clif McIrvin a couché sur son écran :