-1

This question is an extension/continuation of my previous question at How to re-arrange Excel database from 1 long row, into 3 short rows and automatically repeat the process? which was answered by Jason Lewis of which I'm grateful. But being a dummy in "Indirect' Excel function, I need assistance again :

For example :- In Sheet A, Row 1 has the following data in each cell (all together 72 cells occupied):

A1 B1 C1 D1 E1 F1 G1 H1 I1 J1 K1 L1 M1 N1 O1 P1 Q1 R1 S1 T1 U1 V1 W1 X1 Y1 Z1 AA1 AB1 AC1 AD1 AE1 AF1 AG1 AH1 AI1 AJ1 AK1 AL1 AM1 AN1 AO1 AP1 AQ1 AR1 AS1 AT1 AU1 AV1 AW1 AX1 AY1 AZ1 BA1 BB1 BC1 BD1 BE1 BF1 BG1 BH1 BI1 BJ1 BK1 BL1 BM1 BN1 BO1 BP1 BQ1 BR1 BS1 BT1

To be re-arranged into Sheet B in the following format:

Row 1 : A1 B1 C1 D1 E1 F1 G1 H1 I1 J1 K1 L1 M1 N1 O1 P1 Q1 R1 S1 T1 U1 V1 W1 X1 Y1 Z1 AA1 AB1 AC1 AD1 AE1 AF1 AG1 AH1 AI1

Row 2 : AJ1 AK1 AL1 AM1 AN1 AO1 AP1 AQ1 AR1 AS1 AT1 AU1 AV1 AW1 AX1 AY1 AZ1 BA1 BB1 BC1 BD1 BE1 BF1 BG1 BH1 BI1 BJ1 BK1

Row 3 : BL1 BM1 BN1 BO1 BP1 BQ1 BR1 BS1 BT1

The Sheet A (database sheet) has a lot of rows (example 3,000 rows, each rows has 72 cells occupied with data), hence the Sheet B (reformatted database) is estimated to have 9,000 rows (i.e. 3 x 3,000) of unequal lengths.

Thanking you in anticipation of your speedy response.

6
  • Can you also clarify what "start a new row". Is it X number of characters (doubtful because you've stated each row has unequal lengths). Why did ROW2 start with AJ1?
    – Dave
    Jun 5, 2014 at 7:53
  • Sorry, I'm not clear. I can see that Sheet 1, A1 has 72 cells. What I can't understand is how you want to split this into 3 rows. Sheet B A1 is A1 - AI1. Why AI1? Why not B1 or BK1? Why does Sheet B B1 start at AJ1 and end of BK1? Why doesn't it end on BT1 or BD1? Hopefully this makes my question easier to understand?
    – Dave
    Jun 5, 2014 at 8:14
  • Sheet A, Row 1 has 72 cells with data (example of data as per A1 to BT1). Those 72 cells in Sheet A to be re-arranged into Sheet B into the followings : Sheet B Row 1, 35 data (example of data, from A1 to AI1), Sheet B Row 2, 28 data (example of data, from AJ1 to BK1) and lastly Sheet B Row 3, 9 data (example of data, from BL1 to BT1). Therefore, Sheet B has 3 Rows of data of 'unequal lengths'. I'm not sure whether Excel can do it or need to delve into Microsoft Visual Basic for Excel to do it, hence Excel and VBA are being tagged to this question.
    – user326884
    Jun 5, 2014 at 8:20
  • I would appreciate some help if this could be done. I'm downloading data from an accounting package (Accounting Package A) using Seagate Crystal Reports (CR) and the output from CR is typically arranged into long rows (download format). As I need to upload data downloaded (from Accounting Package A) into a different accounting package (Accounting Package B), I am required to re-arrange the database from 1 long row into 3 shorter rows (in this case, 3 short rows of 'unequal lengths' (which is the uploading format), after which, then only it can be uploaded into Accounting Package B.
    – user326884
    Jun 5, 2014 at 8:27
  • I'm a dummy in VBA (I'm worse in VBA compared to Microsoft Excel), so, I may need help on even how to activate VBA, input commands into VBA, and run VBA. Sorry for your inconvenience.
    – user326884
    Jun 5, 2014 at 8:28

1 Answer 1

0

This macro will do it

Sub AnotherGo()

Dim row As Integer
row = 1

Dim row2 As Integer
row2 = 1

Do While (True)

If (Worksheets("Sheet1").Range("A" & row) = "") Then
Exit Do
End If

Worksheets("Sheet2").Range("A" & row2 & ":AI" & row2).Value = Worksheets("Sheet1").Range("A" & row & ":AI" & row).Value
Worksheets("Sheet2").Range("A" & row2 + 1 & ":AB" & row2 + 1).Value = Worksheets("Sheet1").Range("AJ" & row & ":BK" & row).Value
Worksheets("Sheet2").Range("A" & row2 + 2 & ":I" & row2 + 2).Value = Worksheets("Sheet1").Range("BL" & row & ":BT" & row).Value

row = row + 1
row2 = row2 + 3

Loop

End Sub

I have 3 rows in Sheet1

enter image description here

And Sheet 2 looks like (After macro runs)

enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .