Results 1 to 6 of 6

Thread: Reararange data in a specific format
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2013
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Reararange data in a specific format

    I am in a strange problem and need your help to solve in in excel vba,
    I am having a data format in a worksheet as below.


    601 301 101
    301 201 102
    201 602 103
    201 603 104







    Now I would like to arrage them in following order
    601 101 301 102 201 103 602 201
    104 603



    Note that
    1. maximum 8 columns are to be written in a row
    2. after 602, 201 is written as value in cells(forthe row, first column) is different than value in cells(third row, second column)
    Please, please help me

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Reararange data in a specific format

    Please explain why 201 appears twice but 301 appears only once. In fact please explain the "following order".
    Microsoft MVP - Excel

  3. #3
    New Member
    Join Date
    Dec 2013
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reararange data in a specific format

    Actually this is feed to another programm in fortran and this sequence is generated based on identification of veriable from the starting dogot of the value. for example 1** signifies particular veriable and ** signifies where this veriable lies in the series. like wise it is also mandetory that after 6** next veriable should be 2**. it is also mandetory that alternate veriable is always 1** with the exception of 6** after which 2** is mandetory....

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Reararange data in a specific format

    That makes absolutely no sense to me, sorry.
    Microsoft MVP - Excel

  5. #5
    New Member
    Join Date
    Dec 2013
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reararange data in a specific format

    ok.... concept is that whenever 2** veriable appears it should write that veriable or else it should go on writng cells(i, j+2), cells(i,j+1), unless cells(i,j) starts with 2**.

  6. #6
    New Member
    Join Date
    Dec 2013
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Reararange data in a specific format

    Reposting,
    I have data in this format in a worksheet as below.

    (A1)=601 (B1)=101 (C1)=301(A2)=301 (B2)=102 (C2)=201(A3)=201 (B3)=103 (C3)=602(A4)=201 (B4)=104 (C4)=603Now I would like to arrange them in a row of 8 cells in following order. (note after 8 cells it should start writing from the start of next line.

    (A1)=601 (B1)=101 (C1)=301 (B2)=102 (C2)=201 (B3)=103 (C3)=602 (A4)=201 (B4)=104 (C4)=603Note that
    1. maximum 8 columns are to be written in a row
    2. after (C3)=602, programm to write (A4)=201, and then (B4)=104
    I think this post should be understandable.
    STEP 1.



    Code:
    Sub Scattered_Inline()
        Dim inPutR, outPut()
        Dim i As Long, j As Long, n As Long
        '~~> Change this to the respective range
        inPutR = ThisWorkbook.Sheets("Sheet1").Range("A18:C21")
        ReDim Preserve outPut(UBound(inPutR, 1) * UBound(inPutR, 2))
        For i = LBound(inPutR, 1) To UBound(inPutR, 1)
            For j = LBound(inPutR, 2) To UBound(inPutR, 2)
                outPut(n) = inPutR(i, j)
                n = n + 1
            Next j
        Next i
        ThisWorkbook.Sheets("Sheet2").Range("A1").Resize(UBound(outPut) + 1) = _
        Application.Transpose(outPut)
    End Sub
    Above VBA programm writes all these elements in a column. I would like to convert them in 12 rows, and after 12 rows programm should go to next row.

    (A1)=601(B1)=101(B1)=301(A2)=301(B2)=102(C2)=201(A3)=201(B3)=103(C3)=602(A4)=201(B4)=104(C4)=603


    STEP 2 Now to delete repeated values I have used below programm.


    Code:
    Sub DeleteRows()
        With ActiveSheet
            Set Rng = Range("A1", Range("B1").End(xlDown))
            Rng.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
        End With
    End Sub
    Above programm generates below output.

    (A1)=601(B1)=101(C1)=301(B2)=102(C2)=201(B3)=103(C3)=602(B4)=104 '.... look (A4)=201 is missing(C4)=603


    I need to modify above programms to generate output as below.
    (A1)=601 (B1)=101 (C1)=301(B2)=102(C2)=201 (B3)=103 (C3)=602 (A4)=201 (B4)=104 (C4)=603
    Last edited by kaleyn; Dec 19th, 2013 at 06:32 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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