fill column with pattern of information

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tankgirl215 - 19 Sep 2006 18:49 GMT

Hello,

I'm trying to fill in a single column in a worksheet with warehouse
locations as follows:

01AA01AA01
01AA01AA02
through:
01AA01AA99
then starting the same pattern with:
01AA01AB01
and so on to the last row in the column (3500 rows)

These locations are all in order starting with the first row of
information in the spreadsheet. Is there an easy way to get Excel to
recognize this pattern and fill in the remaining cells? I'd much
rather not type in all 3500 locations if possible. A formula? Macro?
Lol, anything?

Thanks!!

Reply to this Message

Dave Peterson - 19 Sep 2006 19:12 GMT

I think that this works.

I put this in A1:

="01AA01"&SUBSTITUTE(SUBSTITUTE(CELL("address",
OFFSET($AA$1,0,INT((ROW()-1)/99))),"$",""),"1","")
&TEXT(MOD(ROW()-1,99)+1,"00")

(copy and paste into the formula bar, then drag down.)

> Hello,
>
[quoted text clipped - 16 lines]
>
> Thanks!!

Signature

Dave Peterson

Reply to this Message

tankgirl215 - 19 Sep 2006 19:36 GMT

It worked perfectly!

Thanks!

Reply to this Message