Need formula help...

[destrolennox]

Hey guys, I posted a question relating to this same work task I wa
working on last week and got some great responses. I am having on
other problem I thought you guys might be able to help me with...


Here is a screenshot of what I am working with. (I altered the name
and the last 6 digits of the account numbers, but the XXXX exist in th
document I am working with as well)

I have attached a sample of the original data as EXCELBEFORE.JPG.

If you look at the other two attachments, excelafter1.jpg an
excelafter2.jpg, these show what I want the data to look like. I wan
to replicate the name listed in collumn B automatically in collumn
until the new name is shown in collumn B. I had to break apart th
last 2 pictures to make them smaller than the 100k limit.

I achieved the results shown in the jpgs by hand, which was easy fo
only 3 or 4 names. However, I have to do this for over 2000, which i
a very daunting task. A nice formula could help me out a lot, but
just can figure out how to get it to work...

Thanks for the help.

-Brenda

+-------------------------------------------------------------------
|Filename: excelafter2.JPG
|Download: http://www.excelforum.com/attachment.php?postid=5100
+-------------------------------------------------------------------

 

[mrice]

Try this formula pasted into the column A in the cell adjacent to th
first line of your data (assumed to be 5 in the example)

=IF(OR(ISERR(IF(FIND(",",B5)>0,1,0)),ISERR(IF(FIND("TOTAL",B5)>0,1,0))=FALSE),A4,B5)

and then copy it down for all the remainder of the rows.

You can then convert this to values by selecting column A, then copy
then paste special - values.


If works by looking for commas in the cells in column B and findin
those which have a comma but not the word TOTAL. For these, it return
the value of the cell in column B. For the others it returns the valu
of the cell above. It follows, therefore, that this approach only work
if there is an unbroken column of formulae