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

1. 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)  Reply With Quote

2. 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".  Reply With Quote

3. 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....  Reply With Quote

4. Re: Reararange data in a specific format

That makes absolutely no sense to me, sorry.  Reply With Quote

5. 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**.  Reply With Quote

6. 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  Reply With Quote

User Tag List

Tags for this Thread

data, excel, rearrange, vba  Posting Permissions

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