SPACING

Hello,
I am working on stock valuation where i have raw data in one sheet say
In sheet 1 i have the following data

PART NO. DESCRIPTION COST DATE OF PURCHASE
109118 ............ 27.05.03
1519123 27.05.03
11256 27.05.03

109118 28.05.03
1122 28.05.03

& SO ON
In second sheet i have to sort data in the descending order of the
purchase & after that i have to give a gap of about 5 blank rows which
i have to do manually.This consumes a lot of time taking into
consideration inventory of about 4000 items.How can i do it by using a
formula.
Hope somebody can help me
Thanks you in advance.

Regards
Rajeev Chadha

Hi Rajeev,

You will need code. Here is some

Sub Test()
Dim iLastRow As Long
Dim i As Long

With Worksheets("Sheet2")
ActiveSheet.Cells.Copy Destination:=.Cells
.Columns("A:D").Sort key1:=.Range("D2"), header:=xlYes
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
.Cells(i + 1, "A").Resize(5).EntireRow.Insert
Next i
End With

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"RAJEEV CHADHA" <rajeev@endolite.net> wrote in message
news:b152c196.0504170619.232d2daa@posting.google.com...
> Hello,
> I am working on stock valuation where i have raw data in one sheet say
> In sheet 1 i have the following data
>
> PART NO. DESCRIPTION COST DATE OF PURCHASE
> 109118 ............ 27.05.03
> 1519123 27.05.03
> 11256 27.05.03
>
> 109118 28.05.03
> 1122 28.05.03
>
> & SO ON
> In second sheet i have to sort data in the descending order of the
> purchase & after that i have to give a gap of about 5 blank rows which
> i have to do manually.This consumes a lot of time taking into
> consideration inventory of about 4000 items.How can i do it by using a
> formula.
> Hope somebody can help me
> Thanks you in advance.
>
> Regards
> Rajeev Chadha

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message news:<emH9541QFHA.3496@TK2MSFTNGP12.phx.gbl>...
> Hi Rajeev,
>
> You will need code. Here is some
>
> Sub Test()
> Dim iLastRow As Long
> Dim i As Long
>
> With Worksheets("Sheet2")
> ActiveSheet.Cells.Copy Destination:=.Cells
> .Columns("A:D").Sort key1:=.Range("D2"), header:=xlYes
> iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> For i = iLastRow To 2 Step -1
> .Cells(i + 1, "A").Resize(5).EntireRow.Insert
> Next i
> End With
>
> End Sub
>
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "RAJEEV CHADHA" <rajeev@endolite.net> wrote in message
> news:b152c196.0504170619.232d2daa@posting.google.com...
> > Hello,
> > I am working on stock valuation where i have raw data in one sheet say
> > In sheet 1 i have the following data
> >
> > PART NO. DESCRIPTION COST DATE OF PURCHASE
> > 109118 ............ 27.05.03
> > 1519123 27.05.03
> > 11256 27.05.03
> >
> > 109118 28.05.03
> > 1122 28.05.03
> >
> > & SO ON
> > In second sheet i have to sort data in the descending order of the
> > purchase & after that i have to give a gap of about 5 blank rows which
> > i have to do manually.This consumes a lot of time taking into
> > consideration inventory of about 4000 items.How can i do it by using a
> > formula.
> > Hope somebody can help me
> > Thanks you in advance.
> >
> > Regards
> > Rajeev Chadha



Hi Bob
Its very difficult to understand.Will you please clarify it.


Regards
Rajeev

What do you want clarified, how it works or how to implement it?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"RAJEEV CHADHA" <rajeev@endolite.net> wrote in message
news:b152c196.0504172009.5225bcf1@posting.google.com...
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:<emH9541QFHA.3496@TK2MSFTNGP12.phx.gbl>...
> > Hi Rajeev,
> >
> > You will need code. Here is some
> >
> > Sub Test()
> > Dim iLastRow As Long
> > Dim i As Long
> >
> > With Worksheets("Sheet2")
> > ActiveSheet.Cells.Copy Destination:=.Cells
> > .Columns("A:D").Sort key1:=.Range("D2"), header:=xlYes
> > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> > For i = iLastRow To 2 Step -1
> > .Cells(i + 1, "A").Resize(5).EntireRow.Insert
> > Next i
> > End With
> >
> > End Sub
> >
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "RAJEEV CHADHA" <rajeev@endolite.net> wrote in message
> > news:b152c196.0504170619.232d2daa@posting.google.com...
> > > Hello,
> > > I am working on stock valuation where i have raw data in one sheet say
> > > In sheet 1 i have the following data
> > >
> > > PART NO. DESCRIPTION COST DATE OF PURCHASE
> > > 109118 ............ 27.05.03
> > > 1519123 27.05.03
> > > 11256 27.05.03
> > >
> > > 109118 28.05.03
> > > 1122 28.05.03
> > >
> > > & SO ON
> > > In second sheet i have to sort data in the descending order of the
> > > purchase & after that i have to give a gap of about 5 blank rows which
> > > i have to do manually.This consumes a lot of time taking into
> > > consideration inventory of about 4000 items.How can i do it by using a
> > > formula.
> > > Hope somebody can help me
> > > Thanks you in advance.
> > >
> > > Regards
> > > Rajeev Chadha
>
>
>
> Hi Bob
> Its very difficult to understand.Will you please clarify it.
>
>
> Regards
> Rajeev

"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message news:<uB$CvJ$QFHA.2436@TK2MSFTNGP10.phx.gbl>...
> What do you want clarified, how it works or how to implement it?
>
> --
>
> HTH
>
> RP
> (remove nothere from the email address if mailing direct)
>
>
> "RAJEEV CHADHA" <rajeev@endolite.net> wrote in message
> news:b152c196.0504172009.5225bcf1@posting.google.com...
> > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> news:<emH9541QFHA.3496@TK2MSFTNGP12.phx.gbl>...
> > > Hi Rajeev,
> > >
> > > You will need code. Here is some
> > >
> > > Sub Test()
> > > Dim iLastRow As Long
> > > Dim i As Long
> > >
> > > With Worksheets("Sheet2")
> > > ActiveSheet.Cells.Copy Destination:=.Cells
> > > .Columns("A:D").Sort key1:=.Range("D2"), header:=xlYes
> > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> > > For i = iLastRow To 2 Step -1
> > > .Cells(i + 1, "A").Resize(5).EntireRow.Insert
> > > Next i
> > > End With
> > >
> > > End Sub
> > >
> > >
> > > --
> > >
> > > HTH
> > >
> > > RP
> > > (remove nothere from the email address if mailing direct)
> > >
> > >
> > > "RAJEEV CHADHA" <rajeev@endolite.net> wrote in message
> > > news:b152c196.0504170619.232d2daa@posting.google.com...
> > > > Hello,
> > > > I am working on stock valuation where i have raw data in one sheet say
> > > > In sheet 1 i have the following data
> > > >
> > > > PART NO. DESCRIPTION COST DATE OF PURCHASE
> > > > 109118 ............ 27.05.03
> > > > 1519123 27.05.03
> > > > 11256 27.05.03
> > > >
> > > > 109118 28.05.03
> > > > 1122 28.05.03
> > > >
> > > > & SO ON
> > > > In second sheet i have to sort data in the descending order of the
> > > > purchase & after that i have to give a gap of about 5 blank rows which
> > > > i have to do manually.This consumes a lot of time taking into
> > > > consideration inventory of about 4000 items.How can i do it by using a
> > > > formula.
> > > > Hope somebody can help me
> > > > Thanks you in advance.
> > > >
> > > > Regards
> > > > Rajeev Chadha
> >
> >
> >
> > Hi Bob
> > Its very difficult to understand.Will you please clarify it.
> >
> >
> > Regards
> > Rajeev


Hi Bob,
I want to know that how we can implement it step by step.If possible if you can
illustrate it with an example.
Thanks & Best Regards
Rajeev

I am confused as to why you want an example, it addresses the question you
asked!

Just slap it into a standard code module (Alt-F11, menu Insert>Module) and
run it.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"RAJEEV CHADHA" <rajeev@endolite.net> wrote in message
news:b152c196.0504180449.10ff69f0@posting.google.com...
> "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:<uB$CvJ$QFHA.2436@TK2MSFTNGP10.phx.gbl>...
> > What do you want clarified, how it works or how to implement it?
> >
> > --
> >
> > HTH
> >
> > RP
> > (remove nothere from the email address if mailing direct)
> >
> >
> > "RAJEEV CHADHA" <rajeev@endolite.net> wrote in message
> > news:b152c196.0504172009.5225bcf1@posting.google.com...
> > > "Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
> > news:<emH9541QFHA.3496@TK2MSFTNGP12.phx.gbl>...
> > > > Hi Rajeev,
> > > >
> > > > You will need code. Here is some
> > > >
> > > > Sub Test()
> > > > Dim iLastRow As Long
> > > > Dim i As Long
> > > >
> > > > With Worksheets("Sheet2")
> > > > ActiveSheet.Cells.Copy Destination:=.Cells
> > > > .Columns("A:D").Sort key1:=.Range("D2"), header:=xlYes
> > > > iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
> > > > For i = iLastRow To 2 Step -1
> > > > .Cells(i + 1, "A").Resize(5).EntireRow.Insert
> > > > Next i
> > > > End With
> > > >
> > > > End Sub
> > > >
> > > >
> > > > --
> > > >
> > > > HTH
> > > >
> > > > RP
> > > > (remove nothere from the email address if mailing direct)
> > > >
> > > >
> > > > "RAJEEV CHADHA" <rajeev@endolite.net> wrote in message
> > > > news:b152c196.0504170619.232d2daa@posting.google.com...
> > > > > Hello,
> > > > > I am working on stock valuation where i have raw data in one sheet
say
> > > > > In sheet 1 i have the following data
> > > > >
> > > > > PART NO. DESCRIPTION COST DATE OF PURCHASE
> > > > > 109118 ............ 27.05.03
> > > > > 1519123 27.05.03
> > > > > 11256 27.05.03
> > > > >
> > > > > 109118 28.05.03
> > > > > 1122 28.05.03
> > > > >
> > > > > & SO ON
> > > > > In second sheet i have to sort data in the descending order of the
> > > > > purchase & after that i have to give a gap of about 5 blank rows
which
> > > > > i have to do manually.This consumes a lot of time taking into
> > > > > consideration inventory of about 4000 items.How can i do it by
using a
> > > > > formula.
> > > > > Hope somebody can help me
> > > > > Thanks you in advance.
> > > > >
> > > > > Regards
> > > > > Rajeev Chadha
> > >
> > >
> > >
> > > Hi Bob
> > > Its very difficult to understand.Will you please clarify it.
> > >
> > >
> > > Regards
> > > Rajeev
>
>
> Hi Bob,
> I want to know that how we can implement it step by step.If possible if
you can
> illustrate it with an example.
> Thanks & Best Regards
> Rajeev