Formula to transpose data

[WynH2017]

Sorry, first post so forgive me if I get anything wrong.

I am a fairly experienced excel user and happy to use any formula for my requirements I'm using 64 bit Excel 2016.

My issue is trying to transpose large amounts of data using a formula (vlookup/hlookup/index match are in my mind but not enough to make it work)

What I have:
A B C D
123 xxx ccc hhh
456 yyy ddd iii
456 zzz eee jjj
456 aaa fff kkk
789 bbb ggg lll

What I want:
A B1 B2 B3 C1 C2 C3 D1 D2 D3
123 xxx ccc hhh
456 yyy zzz aaa ddd eee fff iii jjj kkk
789 bbb ggg lll

But I have 5000 rows of data and B1-B3 etc would go as far as 50 extra columns

Any help would be much appreciated.

 

[frank_AL]

I can't think of a way to accomplish this using formulas but it would be rather straight forward to solve with a VBA macro.

Answer these questions and I'll work on a solution for you
Do you have a header row?
What row does data start on?
What is the name of the worksheet?

 

[WynH2017]

I'm up for that.

There is a header row and the data starts on row 2. The worksheet is named data but I'd want the resulting data in a new sheet named data_refined

Thanks

 

[frank_AL]

Got it, thanks for the quick reply. I'll get something worked up for you.

 

[frank_AL]

Give me your headers for the first 8 columns

 

[WynH2017]

Company Number
Addr. no.
Company Name 1
Company Name 2
Installed
Type of Contract
Count of Sites
Annual Spend

 

[Jborg]

It can be done with a formula:

First I listed an original of your numbers starting in cell A12 - 123
A13 - 456
A14 - 789

Then I posted the below formula starting from C12

=IFERROR(INDEX(INDEX($B$1:$D$5,MATCH($A12,$A$1:$A$5,0),):INDEX($B$1:$D$5,MATCH($A12,$A$1:$A$5,1),),INT(COLUMNS(E7:$F7)/3+1),MOD(COLUMNS($E8:E8)-1,3)+1),"")

and copy it down to A14.

I know that you have a large data, but the above worked with me with your sample.

 

[frank_AL]

WynH2017,

Let me know if Jborg's Formula solution works for you with your actual data. I started to write code but have been away most of the day. I won't make the effort if Jborg's solution gives you what you are looking for.

Let me know,

Frank

 

[Peter_SSs]

@Jborg
Hello John, good to see you about again.
I think you have not understood the OP's required final layout correctly because of how the forum has displayed that data. If you click 'Reply With Quote' under that first post i think you will see what I mean.

@WynH2017
Do all your original rows have 4 columns of data like your samples?
If not, do all your original rows have the same number of columns as each other?

 

[mrshl9898]

Hi,

Looking at your before and after I have this:

Sub sortinout()


'declare variables
Dim rownum As Long
Dim rownum2 As Long




'resets sheet
Sheets("data_refined").Cells.ClearContents




'fills in headings
Sheets("data_refined").Cells(1, 1).Value = "A"
Sheets("data_refined").Cells(1, 2).Value = "B1"
Sheets("data_refined").Cells(1, 3).Value = "B2"
Sheets("data_refined").Cells(1, 4).Value = "B3"
Sheets("data_refined").Cells(1, 5).Value = "C1"
Sheets("data_refined").Cells(1, 6).Value = "C2"
Sheets("data_refined").Cells(1, 7).Value = "C3"
Sheets("data_refined").Cells(1, 8).Value = "D1"
Sheets("data_refined").Cells(1, 9).Value = "D2"
Sheets("data_refined").Cells(1, 10).Value = "D3"




'set variables
rownum = 2
rownum2 = 2




'loops through data until there is a blank cell in column A
Do Until Sheets("data").Cells(rownum, 1).Value = ""




'new A
If Sheets("data").Cells(rownum, 1).Value <> Sheets("data").Cells(rownum - 1, 1).Value Then
Sheets("data_refined").Cells(rownum2, 1).Value = Sheets("data").Cells(rownum, 1).Value
Sheets("data_refined").Cells(rownum2, 2).Value = Sheets("data").Cells(rownum, 2).Value
Sheets("data_refined").Cells(rownum2, 5).Value = Sheets("data").Cells(rownum, 3).Value
Sheets("data_refined").Cells(rownum2, 8).Value = Sheets("data").Cells(rownum, 4).Value
rownum2 = rownum2 + 1
rownum = rownum + 1
End If




'2nd A row
If Sheets("data").Cells(rownum, 1).Value = Sheets("data").Cells(rownum - 1, 1).Value Then
Sheets("data_refined").Cells(rownum2 - 1, 3).Value = Sheets("data").Cells(rownum, 2).Value
Sheets("data_refined").Cells(rownum2 - 1, 6).Value = Sheets("data").Cells(rownum, 3).Value
Sheets("data_refined").Cells(rownum2 - 1, 9).Value = Sheets("data").Cells(rownum, 4).Value
rownum = rownum + 1
End If




'3rd A row
If Sheets("data").Cells(rownum, 1).Value = Sheets("data").Cells(rownum - 1, 1).Value Then
Sheets("data_refined").Cells(rownum2 - 1, 4).Value = Sheets("data").Cells(rownum, 2).Value
Sheets("data_refined").Cells(rownum2 - 1, 7).Value = Sheets("data").Cells(rownum, 3).Value
Sheets("data_refined").Cells(rownum2 - 1, 10).Value = Sheets("data").Cells(rownum, 4).Value
rownum = rownum + 1
End If




Loop




'formats data_refined sheet
    Sheets("data_refined").Select
    Cells.Select
    Cells.EntireColumn.AutoFit
    Sheets("data_refined").Range("A1").Select
    
End Sub