Hey guys, I posted a question relating to this same work task I was
working on last week and got some great responses. I am having one
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 names
and the last 6 digits of the account numbers, but the XXXX exist in the
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 and
excelafter2.jpg, these show what I want the data to look like. I want
to replicate the name listed in collumn B automatically in collumn A
until the new name is shown in collumn B. I had to break apart the
last 2 pictures to make them smaller than the 100k limit.

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

Thanks for the help.

-Brendan


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

--
destrolennox
------------------------------------------------------------------------
destrolennox's Profile: http://www.excelforum.com/member.php...o&userid=36503
View this thread: http://www.excelforum.com/showthread...hreadid=564903

Try this formula pasted into the column A in the cell adjacent to the
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 finding
those which have a comma but not the word TOTAL. For these, it returns
the value of the cell in column B. For the others it returns the value
of the cell above. It follows, therefore, that this approach only works
if there is an unbroken column of formulae.


--
mrice

Research Scientist with many years of spreadsheet development experience
------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=564903