Reararange data in a specific format

kaleyn

New Member
Joined
Dec 19, 2013
Messages
4
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

<TBODY>
</TBODY>

<CODE>




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

<TBODY>
</TBODY>



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>

<TBODY>
</TBODY>
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Please explain why 201 appears twice but 301 appears only once. In fact please explain the "following order".
 
Upvote 0
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....
 
Upvote 0
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**.
 
Upvote 0
Reposting,

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.



<TBODY>
</TBODY>



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


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

<TBODY>
</TBODY>
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,213
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top