Formula for Duplicating Rows!!
 

I have an Excel worksheet with a few thousand rows. The first 2 columns look
as seen below. I need the Account # of every customer to be on every row.
As of now, the Account # is only on the First row and the rest of rows blank.
What type of formula can I create to replace the blank rows after the
account # with the account # that corresponds to customer. See below for
complete description.

Currently is:

Account Customer

P0182202 Bearid
Bearid
Bearid
Bearid

P38451US 360 OHIO DOT
360 OHIO DOT

P37544US 3DS BRIDGE PAINTING CORP. 3DS BRIDGE PAINTING CORP.

P01471US 4 D CORROSION

Should Be:

Account Customer

P0182202 Bearid
P0182202 Bearid
P0182202 Bearid
P0182202 Bearid

P38451US 360 OHIO DOT
P38451US 360 OHIO DOT

P37544US 3DS BRIDGE PAINTING CORP.
P37544US 3DS BRIDGE PAINTING CORP.

P01471US 4 D CORROSION

Formula for Duplicating Rows!!
 

Try this user defined function

Function AccountNumber(Cell)
N = 1
Do While InStr(Cell.Offset(-N, 0).FormulaR1C1, "=") < 0
N = N + 1
Loop
AccountNumber = Cell.Offset(-N, 0).Value
End Function

If you put =AccountNumber(A2) in Cell A2 etc. you should get the value
of the first non formula cell in the upwards direction.


--
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=544422

Formula for Duplicating Rows!!
 

You can use a formula like this in an additional column (starting in C2, for
example):
=IF(A2<"",A2,IF(B2<"",C1,""))
If column A has a value, it will be return that; if there is value, and the
column B is not empty, it will return the value just over it on its own
column, that it will be the repeated value expected. If both columns are
blank, it will return blank.
You can later copy/paste values from this column to the original one.

Hope this helps,
Miguel.

"GOL" wrote:

I have an Excel worksheet with a few thousand rows. The first 2 columns look
as seen below. I need the Account # of every customer to be on every row.
As of now, the Account # is only on the First row and the rest of rows blank.
What type of formula can I create to replace the blank rows after the
account # with the account # that corresponds to customer. See below for
complete description.

Currently is:

Account Customer

P0182202 Bearid
Bearid
Bearid
Bearid

P38451US 360 OHIO DOT
360 OHIO DOT

P37544US 3DS BRIDGE PAINTING CORP. 3DS BRIDGE PAINTING CORP.

P01471US 4 D CORROSION

Should Be:

Account Customer

P0182202 Bearid
P0182202 Bearid
P0182202 Bearid
P0182202 Bearid

P38451US 360 OHIO DOT
P38451US 360 OHIO DOT

P37544US 3DS BRIDGE PAINTING CORP.
P37544US 3DS BRIDGE PAINTING CORP.

P01471US 4 D CORROSION