| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear cells
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear cells
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" wrote in message ... 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 (clare reads his mail with moe, nomail feeds the bit bucket :-) |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear cells
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. Clif "gcotterl" wrote in message ... 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 (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) |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear cells
"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 :-) |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear cells
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 : 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear cells
On Feb 3, 4:24*pm, Charabeuh wrote:
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!!!! |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear cells
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" wrote in message ... 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 : 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 (clare reads his mail with moe, nomail feeds the bit bucket :-) |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear cells
Hello,
Thanks for the feedback. gcotterl avait soumis l'idée : On Feb 3, 4:24*pm, Charabeuh wrote: 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!!!! |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Clear cells
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 : 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" wrote in message ... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Lookup cells in one column and clear cells in another | Excel Programming | |||
| Create a Clear button to clear unprotected cells | Excel Programming | |||
| clear contents cells of unprotected cells | Excel Programming | |||
| Clear cells with #N/A | Excel Programming | |||
| Clear cells range if certain cells are all empty | Excel Programming | |||
![gcotterl[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
