Transpose 18000 rows in 3 languages into 6000 rows in 3 columns

[fva2600]

i need to transpose 18000 rows of 1 colums into 6000 rows of 3 columns

source array is

[TABLE="width: 1000"]
<tbody>[TR]
[TD]Termid[/TD]
[TD]Language[/TD]
[TD]Text description[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]EN[/TD]
[TD]words and sentences[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]FR[/TD]
[TD]mots et phrases[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]NL[/TD]
[TD]woorden en zinnen[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]EN[/TD]
[TD]more of this[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]FR[/TD]
[TD]plus de ça[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]NL[/TD]
[TD]meer van dat[/TD]
[/TR]
</tbody>[/TABLE]

target array is
[TABLE="width: 1000"]
<tbody>[TR]
[TD]Termid[/TD]
[TD]EN[/TD]
[TD]FR[/TD]
[TD]NL[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]words and sentences[/TD]
[TD]mots et phrases[/TD]
[TD]woorden en zinnen[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]more of this[/TD]
[TD]plus de ça[/TD]
[TD]meer van dat[/TD]
[/TR]
</tbody>[/TABLE]

When i use {=TRANSPOSE(c2:c4)} and i try to copy into the following rows, the row reference is only augmented with 1 instead of 3 and i get {=TRANSPOSE(c3:c5)} instead of {=TRANSPOSE(c5:c7)}.

is there another way to solve this?

txs,

fva2600

 

[alansidman]

Here is a VBA solution for you. With 6000 lines of code, be patient. It may take awhile.

Option Explicit


Sub ThreeLang()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Dim lr As Long, lr2 As Long
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    Dim i As Long


    Application.ScreenUpdating = False
    With s1
        .Range("A1").Copy s2.Range("A1")
        .Range("B2:B4").Copy
        s2.Range("B1").PasteSpecial xlPasteAll, , , True
        For i = 2 To lr
            lr2 = s2.Range("B" & Rows.Count).End(xlUp).Row
            .Range("A" & i).Copy s2.Range("A" & lr2 + 1)
            .Range("C" & i & ":C" & i + 2).Copy
            s2.Range("B" & lr2 + 1).PasteSpecial xlPasteAll, , , True
            i = i + 2
        Next i
    End With
    Application.ScreenUpdating = True


End Sub

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

 

[Macropod]

Assuming your data are on 'Sheet1', starting in A1, on a new sheet, insert:
=OFFSET(Sheet1!$A$1,(ROW()-2)*3+1,0)
in A2, and:
=OFFSET(Sheet1!$A$1,(ROW()-2)*3+COLUMN()-1,2)
in B2:D2, then copy down as far as needed. Row 1 on the new sheet is left for the column headings.

 

[Momentman]

You can also work it out this way

Excel 2010
	A	B	C	D	E	F	G	H
1	Termid	Language	Text description			EN	FR	NL
2	1	EN	words and sentences		1	words and sentences	mots et phrases	woorden en zinnen
3	1	FR	mots et phrases		2	more of this	plus de a	meer van dat
4	1	NL	woorden en zinnen
5	2	EN	more of this
6	2	FR	plus de a
7	2	NL	meer van dat
Sheet1

Cell Formulas
Range	Formula
F2	=INDEX($B$2:$C$7,3*ROWS($1:1)-2,2)
G2	=INDEX($B$2:$C$7,3*ROWS($1:1)-1,2)
H2	=INDEX($B$2:$C$7,3*ROWS($1:1),2)

 

[hiker95]

fva2600,

Welcome to the MrExcel forum.

With your raw data in groups of three rows, beginning in row 2.

Here is a macro solution for you to consider that is based on your raw data structure, and, that uses two arrays in memory, and, should be very fast.

You can change the raw data worksheet name in the macro.

Sample raw data:

Excel 2007
	A	B	C	D	E	F	G	H	I
1	Termid	Language	Text description
2	1	EN	words and sentences
3	1	FR	mots et phrases
4	1	NL	woorden en zinnen
5	2	EN	more of this
6	2	FR	plus de a
7	2	NL	meer van dat
8
Sheet1

And, after the macro:

Excel 2007
	A	B	C	D	E	F	G	H	I
1	Termid	Language	Text description			Termid	EN	FR	NL
2	1	EN	words and sentences			1	words and sentences	mots et phrases	woorden en zinnen
3	1	FR	mots et phrases			2	more of this	plus de a	meer van dat
4	1	NL	woorden en zinnen
5	2	EN	more of this
6	2	FR	plus de a
7	2	NL	meer van dat
8
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub ReorgData()
' hiker95, 12/13/2016, ME980664
Dim a As Variant, i As Long
Dim o As Variant, j As Long
Application.ScreenUpdating = False
With Sheets("Sheet1")   '<-- you can change the sheet name here
  .Columns(6).Resize(, 4).ClearContents
  a = .Cells(1, 1).CurrentRegion
  ReDim o(1 To (UBound(a, 1) - 1) / 3, 1 To 4)
  For i = 2 To UBound(a, 1) Step 3
    j = j + 1: o(j, 1) = a(i, 1): o(j, 2) = a(i, 3): o(j, 3) = a(i + 1, 3): o(j, 4) = a(i + 2, 3)
  Next i
  With .Cells(1, 6).Resize(, 4)
    .Value = Array("Termid", "EN", "FR", "NL")
    .Font.Bold = True
  End With
  .Cells(2, 6).Resize(UBound(o, 1), UBound(o, 2)) = o
  .Columns(6).Resize(, 4).AutoFit
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgData macro.

 

[hiker95]

fva2600,

If you need the results reported on another worksheet, then, let me know the raw data worksheet name, and, the name of the result worksheet.