+ Reply to Thread
Results 1 to 6 of 6

How do i write a Macro that does following

  1. #1
    Gautam
    Guest

    How do i write a Macro that does following

    i have a sheet which contains following information

    Name Last name
    Address Title
    City Telephone
    Zip e mail
    Country
    of about 100nds of addresses

    What i want to do in here is to get this information on another sheet stored
    in columns i.e

    Name Address City Zip Country Title Telephone e-mail

  2. #2
    Bob Phillips
    Guest

    Re: How do i write a Macro that does following

    Take a look at VLOOKUP in help.

    --
    HTH

    Bob Phillips

    "Gautam" <[email protected]> wrote in message
    news:[email protected]...
    > i have a sheet which contains following information
    >
    > Name Last name
    > Address Title
    > City Telephone
    > Zip e mail
    > Country
    > of about 100nds of addresses
    >
    > What i want to do in here is to get this information on another sheet

    stored
    > in columns i.e
    >
    > Name Address City Zip Country Title Telephone e-mail




  3. #3
    Don Guillett
    Guest

    Re: How do i write a Macro that does following

    Have a look in HELP index for TRANSPOSE

    --
    Don Guillett
    SalesAid Software
    [email protected]
    "Gautam" <[email protected]> wrote in message
    news:[email protected]...
    > i have a sheet which contains following information
    >
    > Name Last name
    > Address Title
    > City Telephone
    > Zip e mail
    > Country
    > of about 100nds of addresses
    >
    > What i want to do in here is to get this information on another sheet

    stored
    > in columns i.e
    >
    > Name Address City Zip Country Title Telephone e-mail




  4. #4
    Max
    Guest

    Re: How do i write a Macro that does following

    Perhaps one way to try
    (dependent on data structure) ..

    Assuming source data is in cols A and B, in groups of 5 lines each, w/o any
    blank rows in-between groups, viz.:
    (Link to a sample file is provided below)

    Name1 Last name1
    Address1 Title1
    City1 Telephone1
    Zip1 e mail1
    Country1
    Name2 Last name2
    Address2 Title2
    City2 Telephone2
    Zip2 e mail2
    Country2
    Name3 Last name3
    Address3 Title3
    City3 Telephone3
    Zip3 e mail3
    Country3
    etc

    Put in D1: =OFFSET($A$1,ROWS($A$1:A1)*5-5+COLUMNS($A$1:A1)-1,)
    Copy D1 across to H1

    Put in B1: =OFFSET($B$1,ROWS($A$1:A1)*5-5+COLUMNS($A$1:B1)-1,)
    Copy B1 across to K1

    Select D1:K1, fill down until zeros appear,
    signalling exhaustion of data from cols A and B

    Freeze the results in cols D to K
    with a copy > paste special > values > OK
    either in-place or elsewhere

    The results will be returned in the desired format:
    Name1 Address1 City1 Zip1 Country1 Title1 Telephone1 e mail1
    Name2 Address2 City2 Zip2 Country2 Title2 Telephone2 e mail2
    Name3 Address3 City3 Zip3 Country3 Title3 Telephone3 e mail3
    etc

    Here's a sample file with the implemented construct:
    http://flypicture.com/p.cfm?id=52841
    (Right-click on the link: "Download File"
    at the top in the page, just above the ads)
    File: Gautam_newusers_1.xls
    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----
    "Gautam" <[email protected]> wrote in message
    news:[email protected]...
    > i have a sheet which contains following information
    >
    > Name Last name
    > Address Title
    > City Telephone
    > Zip e mail
    > Country
    > of about 100nds of addresses
    >
    > What i want to do in here is to get this information on another sheet

    stored
    > in columns i.e
    >
    > Name Address City Zip Country Title Telephone e-mail




  5. #5
    Max
    Guest

    Re: How do i write a Macro that does following

    Here's a new link to the sample file
    with the implemented construct:
    http://www.savefile.com/files/3671927
    File: Gautam_newusers_1.xls
    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----



  6. #6
    Max
    Guest

    Re: How do i write a Macro that does following

    Sorry, typo in the 2 lines just discovered:

    > Put in B1: =OFFSET($B$1,ROWS($A$1:A1)*5-5+COLUMNS($A$1:B1)-1,)
    > Copy B1 across to K1


    Should read as:
    Put in I1: =OFFSET($B$1,ROWS($A$1:A1)*5-5+COLUMNS($A$1:B1)-1,)
    Copy I1 across to K1

    --
    Rgds
    Max
    xl 97
    ---
    GMT+8, 1° 22' N 103° 45' E
    xdemechanik <at>yahoo<dot>com
    ----



+ 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