Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Nov 2005
    Location
    Asia Pacific, Bangkok Metropolis
    Posts
    378
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Format unique data into row (Excel 2003)

    Hi all

    I have a workbook which was downloaded into Excel, there are 5 columns and the data in Column A, B, D and E were repeated in 4 rows while
    Column C is having different infomation in each row. The next sub set can be identify by the differences in the Ref .
    I want to add a new sheet and insert 2 columns after Column B with header " SYMBOL " and " CODE "
    and move the data in the 2nd row of column C in the sub set above it. the info from row 3 and 4 of column C be move to " SYMBOL " and " CODE " respectively.

    I have attached a sample to show the original data in Sheet 1 and the result in Sheet 2

    Can this be accomplish by formula?

    Thanks in advance for your help

    regards, francis
    Attached Files Attached Files
    Hope this is helpful

    francis, <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    My Reading

    Pivot Table 101
    Pivot Table
    Array

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Format unique data into row (Excel 2003)

    A2:
    =OFFSET(Sheet1!$A$1,(ROW()-2)*4+1,COLUMN()-1)

    B2:
    =OFFSET(Sheet1!$A$1,(ROW()-2)*4+1,COLUMN()-1)

    C2:
    =OFFSET(Sheet1!$A$1,(ROW()-2)*4+4,COLUMN()-1)

    D2:
    =OFFSET(Sheet1!$A$1,(ROW()-2)*4+3,COLUMN()-2)

    E2:
    =OFFSET(Sheet1!$A$1,(ROW()-2)*4+2,COLUMN()-3)

    F2:
    =OFFSET(Sheet1!$A$1,(ROW()-2)*4+1,COLUMN()-3)

    G2:
    =OFFSET(Sheet1!$A$1,(ROW()-2)*4+1,COLUMN()-3)

    Autofill/Copy A2:G2 down the columns.

    [Note: This does not give your results since the pattern in the 2 groups are not the same. In one row you put the 4th item in col c and the 3rd in col D. In the other you had themn reversed....]

    Steve

Posting Permissions

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