Fill series but a little different
 

Hi,
ı want to fill series based on cell data, a sample is below
a1 b1
james 10
jane 8
... ..
result should be

james 1
james 2
james 3
...
james 10
jane 1
jane 2
...
jane 8

thanks for any help.


--
kingjeremy
------------------------------------------------------------------------
kingjeremy's Profile: http://www.excelforum.com/member.php...o&userid=26444
View this thread: http://www.excelforum.com/showthread...hreadid=397155

kingjeremy Wrote:
Hi,
ı want to fill series based on cell data, a sample is below
a1 b1
james 10
jane 8
... ..
result should be

james 1
james 2
james 3
...
james 10
jane 1
jane 2
...
jane 8

thanks for any help.

Hi kingjeremy

Not 100% sure what you want but here goes

If James is in cell A1 and 1 is in B1, select both cells, move cursor
to bottom right of selected cells, it becomes a black cross, hold down
left mouse button and drag down

or if James is in Cell A1 and 10 is in B1 to fill the gaps back to
James 1, you will need to insert rows above James 10 and then do the
same thing but drag upwards


--
Paul Sheppard


------------------------------------------------------------------------
Paul Sheppard's Profile: http://www.excelforum.com/member.php...o&userid=24783
View this thread: http://www.excelforum.com/showthread...hreadid=397155

Are you working with Mellissa???

Try this against a copy of your workbook--it'll destroy the original data.

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim howMany As Long

Set wks = Worksheets("sheet1")

With wks
FirstRow = 2 'headers in row 1???
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
howMany = .Cells(iRow, "B").Value
If howMany 1 Then
.Rows(iRow + 1).Resize(howMany - 1).Insert
.Cells(iRow, "A").Resize(howMany).Value _
= .Cells(iRow, "A").Value
With .Cells(iRow, "B").Resize(howMany)
.Formula = "=row()-" & iRow - 1
.Value = .Value
End With
End If
Next iRow
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

And there's no validation against the value in column B. It can blow up really
good if you don't type in nice numbers.


kingjeremy wrote:

Hi,
ı want to fill series based on cell data, a sample is below
a1 b1
james 10
jane 8
.. ..
result should be

james 1
james 2
james 3
..
james 10
jane 1
jane 2
..
jane 8

thanks for any help.

--
kingjeremy
------------------------------------------------------------------------
kingjeremy's Profile: http://www.excelforum.com/member.php...o&userid=26444
View this thread: http://www.excelforum.com/showthread...hreadid=397155

--

Dave Peterson

er, Melissa???

no I'm neither working with her nor I'm melissa. It was just a
coincidence that we asked the same question almost at the same time.
And thanks for the solution it will really ease a lot of work.


--
kingjeremy
------------------------------------------------------------------------
kingjeremy's Profile: http://www.excelforum.com/member.php...o&userid=26444
View this thread: http://www.excelforum.com/showthread...hreadid=397155

Glad it worked for you (and Melissa, too <bg).

kingjeremy wrote:

no I'm neither working with her nor I'm melissa. It was just a
coincidence that we asked the same question almost at the same time.
And thanks for the solution it will really ease a lot of work.

--
kingjeremy
------------------------------------------------------------------------
kingjeremy's Profile: http://www.excelforum.com/member.php...o&userid=26444
View this thread: http://www.excelforum.com/showthread...hreadid=397155

--

Dave Peterson