I am dealing with a spreadsheet of properties in Philadelphia. I want to find each occurance of the text "LAND" in column B, and then have the same text "LAND" added to the blank cell in column C in the same row. When the text "LAND" is not in column B there will be other text in column C that is not to be altered. This file contains about 600k rows...so scolling is not as favorable as is a formula that addresses ranges! Thanks
Last edited by lehmannheinz; 04-02-2013 at 04:11 PM.
I'm going to assume that you meant Column A,and that you have a header row, so :
In B2:
Formula:
Please Login or Register to view this content.
or if the word land is part of a larger text :
Formula:Please Login or Register to view this content.
Drag either down as far as needed
Hope this helps
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
Is there a typo in your request? It says we're looking for "LAND" in Col_B cells and putting "LAND" in the blank Col_B cell on the same row. Col_B is referenced twice.
I also forgot to be clear...in that the row and column may look like this!
A B
LAND empty cell
LAND empty cell
RES RES
MULTI MULTI
LAND empty cell
so...the formula must be able to scan column A and not disrupt or remove text that is already in the non-blank column B cells!
Here is a sample of my file. The file actually contains 600000 rows...so draging is difficult...but dooable if you place something on the button as the mouse scrolls down!
see attached sample.
I think you are going to need VBA to do this, Unfortunately, My VBA is not that good
How about this! If column B2 has "LAND" and the C2 is blank, then I would like to have "LAND" placed into that empty cell. Could column D2 have the evaluation and action to place "LAND" into Column C2. I could latter erase column D?
ANY formula will ONLY affect the cell it is in, you could use the formula I suggested in column C then use this in column D:
Formula:Please Login or Register to view this content.
Drag down
then select the whole range (D column), copy, select E2, paste special ..values
then, if you are satisfied with the results, delete columns B - D, result is the new list
Hope this helps
Thanks, I will try your suggestions..
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks