+ Reply to Thread
Results 1 to 2 of 2

Copy table information and paste sorted based on specific field.

  1. #1
    Paul
    Guest

    Copy table information and paste sorted based on specific field.

    Hi,

    I have a table in excel formated something like this

    A/C Cur Amount
    123 CND 100.00
    153 EUR 132.22
    173 US 643.99
    178 EUR 124.22
    142 CND 124.32
    109 EUR 187.70
    158 US 188.43

    This is a very brief example - I have 1000's of lines like this. The
    important field here is Cur (currency). There maybe up to 10 currencys in
    the original. What I need to do is extract all these columns into a second
    preformated sheet and separate the information out based on currency. The
    second sheet should look something like this.

    Co Type A/C Cur Ref Amount
    SP 123 CND Batch 100.00
    142 CND 124.32

    SP 153 EUR Batch 132.22
    178 EUR 124.22
    109 EUR 187.70

    SP 173 US Batch 643.99
    158 US 188.43

    The important thing is that for each currency the extra information is
    standard and is only required on the first line.

    I hope I've explained this clear enough. Any suggestions on how I would
    automate this process using code would be greatly appreciated.

    Paul



  2. #2
    Tom Ogilvy
    Guest

    Re: Copy table information and paste sorted based on specific field.

    Sub SetUpTable()
    Range("A1").CurrentRegion.Sort _
    Key1:=Range("B1"), Order1:=xlAscending, _
    Header:=xlYes
    Columns(3).Insert
    Columns(1).Insert
    rw = Cells(Rows.Count, 3).End(xlUp).Row
    For i = rw To 2 Step -1
    If Cells(i, 3) <> Cells(i - 1, 3) Then
    Rows(i).Insert
    Cells(i + 1, 1) = "SP"
    Cells(i + 1, 4) = "Batch"
    End If
    Next
    Cells(1, 1) = "Co Type"
    Cells(1, 4) = "Ref"
    Rows(2).Delete
    End Sub

    --
    Regards,
    Tom Ogilvy


    "Paul" <paulm dot c @ iol dot ie> wrote in message
    news:%23Q%2352mbmFHA.1088@TK2MSFTNGP14.phx.gbl...
    > Hi,
    >
    > I have a table in excel formated something like this
    >
    > A/C Cur Amount
    > 123 CND 100.00
    > 153 EUR 132.22
    > 173 US 643.99
    > 178 EUR 124.22
    > 142 CND 124.32
    > 109 EUR 187.70
    > 158 US 188.43
    >
    > This is a very brief example - I have 1000's of lines like this. The
    > important field here is Cur (currency). There maybe up to 10 currencys in
    > the original. What I need to do is extract all these columns into a second
    > preformated sheet and separate the information out based on currency. The
    > second sheet should look something like this.
    >
    > Co Type A/C Cur Ref Amount
    > SP 123 CND Batch 100.00
    > 142 CND 124.32
    >
    > SP 153 EUR Batch 132.22
    > 178 EUR 124.22
    > 109 EUR 187.70
    >
    > SP 173 US Batch 643.99
    > 158 US 188.43
    >
    > The important thing is that for each currency the extra information is
    > standard and is only required on the first line.
    >
    > I hope I've explained this clear enough. Any suggestions on how I would
    > automate this process using code would be greatly appreciated.
    >
    > Paul
    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1