Macro to Tranpose Data

[howard]

I have data in Col F1 to M10 (max) that contains Data


I would like a macro or formula to transpose this data in Cell F15 Onwards

See Sample data below that needs to be transposed

Excel 2012
	F	G	H	I	J	K	L
1	KN-63301	KN-63311	KN-97260	KN-97305	KN-97290	KN-97310	KN-94300
2	KN-63304	KN-63314	KN-97300	KN-97325	KN-97330
3	KN-63303	KN-63313	KN-63314	KN-63315
4
Sheet1

 

[Rick Rothstein]

I would like a macro or formula to transpose this data in Cell F15 Onwards

Cell 15 onward in what direction (a single row across or a single row down)?

 

[howard]

Hi Rick

Thanks for the reply

I need a single row down from F15 onwards

 

[Rick Rothstein]

Hi Rick

Thanks for the reply

I need a single row down from F15 onwards

Okay, now, in what order? Using addresses only, which of these is the order for the transposing...
[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD="align: center"]F15[/TD]
[TD="align: center"]=>[/TD]
[TD="align: center"][/TD]
[TD="align: center"]F1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]F1[/TD]
[/TR]
[TR]
[TD="align: center"]F16[/TD]
[TD="align: center"]=>[/TD]
[TD="align: center"][/TD]
[TD="align: center"]F2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]G1[/TD]
[/TR]
[TR]
[TD="align: center"]F17[/TD]
[TD="align: center"]=>[/TD]
[TD="align: center"][/TD]
[TD="align: center"]F3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]or[/TD]
[TD="align: center"][/TD]
[TD="align: center"]H1[/TD]
[/TR]
[TR]
[TD="align: center"]F18[/TD]
[TD="align: center"]=>[/TD]
[TD="align: center"][/TD]
[TD="align: center"]G1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]I1[/TD]
[/TR]
[TR]
[TD="align: center"]etc.[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]etc.[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]etc.[/TD]
[/TR]
</tbody>[/TABLE]

 

[howard]

Thanks for the reply


The Data must be transposed from eg F1 to M1, then F2 to M2 etc in F15 , F16 onwards

See Sample data of data manually transposed

Excel 2012
	F
14
15	KN-63301
16	KN-63311
17	KN-97260
18	KN-97305
19	KN-97290
20	KN-97310
21	KN-94300
22	KN-94301
23	KN-63304
24	KN-63314
25	KN-97300
26	KN-97325
27	KN-97330
28	KN-63303
29	KN-63313
30	KN-63314
31	KN-63315
Sheet1

 

[Rick Rothstein]

The Data must be transposed from eg F1 to M1, then F2 to M2 etc in F15 , F16 onwards

Assuming your posted example data is representative of your actual data, give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub TransposeColumnsFthruM()
  Dim R As Long, C As Long, LastRow As Long, Joined As Variant
  Range("F15:F112").Clear
  LastRow = 14
  For R = 1 To Range("F1").CurrentRegion.Rows.Count
    Joined = Split(Trim(Join(Application.Index(Cells(R, "F").Resize(, 8).Value, 1, 0))))
    Cells(LastRow + 1, "F").Resize(UBound(Joined) + 1) = Application.Transpose(Joined)
    LastRow = LastRow + UBound(Joined) + 1
  Next
End Sub[/td]
[/tr]
[/table]

 

[howard]

Thanks Rick Code works perfectly

 

[howard]

Hi Rick

I have run your code on another file & get type Mismatch and the following code is highlighted

 Cells(LastRow + 1, "F").Resize(UBound(Joined) + 1) = Application.Transpose(Joined)

Please test and advise


See Sample Data. Col E is data I inputted and Col F to M contains a Formula

Need to Transpose Data Col F1 to the last items row on Col M containing Data

Excel 2012
	E	F	G	H	I	J	K	L	M
1	6721	883216	883218	883200	883202	883208	883210
2	6983	887400
3	6500A	883003
4	6600M	883104	883105	883114	883115	883004	883005	883014	883015
Sheet1

Cell Formulas
Range	Formula
F1	{=IF(COLUMNS($F1:F1)>COUNTIF($A$1:$A$4000,$E1),"",INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$E1,ROW($A$1:$A$4000)-ROW($A$1)+1),COLUMNS($F1:F1))))}
G1	{=IF(COLUMNS($F1:G1)>COUNTIF($A$1:$A$4000,$E1),"",INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$E1,ROW($A$1:$A$4000)-ROW($A$1)+1),COLUMNS($F1:G1))))}
H1	{=IF(COLUMNS($F1:H1)>COUNTIF($A$1:$A$4000,$E1),"",INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$E1,ROW($A$1:$A$4000)-ROW($A$1)+1),COLUMNS($F1:H1))))}
I1	{=IF(COLUMNS($F1:I1)>COUNTIF($A$1:$A$4000,$E1),"",INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$E1,ROW($A$1:$A$4000)-ROW($A$1)+1),COLUMNS($F1:I1))))}
J1	{=IF(COLUMNS($F1:J1)>COUNTIF($A$1:$A$4000,$E1),"",INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$E1,ROW($A$1:$A$4000)-ROW($A$1)+1),COLUMNS($F1:J1))))}
K1	{=IF(COLUMNS($F1:K1)>COUNTIF($A$1:$A$4000,$E1),"",INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$E1,ROW($A$1:$A$4000)-ROW($A$1)+1),COLUMNS($F1:K1))))}
L1	{=IF(COLUMNS($F1:L1)>COUNTIF($A$1:$A$4000,$E1),"",INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$E1,ROW($A$1:$A$4000)-ROW($A$1)+1),COLUMNS($F1:L1))))}
M1	{=IF(COLUMNS($F1:M1)>COUNTIF($A$1:$A$4000,$E1),"",INDEX($B$1:$B$4000,SMALL(IF($A$1:$A$4000=$E1,ROW($A$1:$A$4000)-ROW($A$1)+1),COLUMNS($F1:M1))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Rick Rothstein]

Since I don't have a data set for Columns A:B, I cannot produce the result set you have in the range F1:M4, so I made up a formula that fills in the data layout you show. No matter what I try, I cannot make my code fail... it works consistently for me. Can you post a copy of your workbook (sensitive data removed) to DropBox (or some other such file sharing service) so I can download it and watch my code fail... that would allow me to trace the problem directly.

 

[howard]

Hi Rick

I have uploaded file on drop box- Dropbox - Extract New Account Numbers test .xlsm


Kindly test and advise