Reararange data in a specific format

[kaleyn]

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

<TBODY>
[TD="class: votecell"][/TD]
[TD="class: postcell"]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.


[TABLE="class: grid, width: 500, align: left"]
<TBODY>[TR]

</TBODY>

<CODE>




</CODE>
Now I would like to arrage them in following order
[TABLE="class: grid, width: 500, align: left"]
<TBODY>[TR]
[TD]601
[/TD]
[TD]101
[/TD]
[TD]301
[/TD]
[TD]102
[/TD]
[TD]201
[/TD]
[TD]103
[/TD]
[TD]602
[/TD]
[TD]201
[/TD]
[/TR]
[TR]
[TD]104
[/TD]
[TD]603
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]



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
</PRE>[/TD]
[/TR]
</TBODY>[/TABLE]

 

[Andrew Poulsom]

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

 

[kaleyn]

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....

 

[Andrew Poulsom]

That makes absolutely no sense to me, sorry.

 

[kaleyn]

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**.

 

[kaleyn]

Reposting,

<TBODY>
[TD="class: votecell"]
[/TD]
[TD="class: postcell"]I have data in this format in a worksheet as below.

<CODE>(A1)=601 (B1)=101 (C1)=301(A2)=301 (B2)=102 (C2)=201(A3)=201 (B3)=103 (C3)=602(A4)=201 (B4)=104 (C4)=603</CODE></PRE>Now 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.

<CODE>(A1)=601 (B1)=101 (C1)=301 (B2)=102 (C2)=201 (B3)=103 (C3)=602 (A4)=201 (B4)=104 (C4)=603</CODE></PRE>Note 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.


[/TD]

</TBODY>

<TBODY>
[TD="class: votecell"][/TD]
[TD="class: postcell"]

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

</PRE>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.

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


</PRE> STEP 2 Now to delete repeated values I have used below programm.

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

</PRE>Above programm generates below output.

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


</PRE>I need to modify above programms to generate output as below.
<CODE>(A1)=601 (B1)=101 (C1)=301(B2)=102(C2)=201 (B3)=103 (C3)=602 (A4)=201 (B4)=104 (C4)=603 </CODE>[/TD]

</TBODY>