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]

Hi Rick

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

Having the file in hand showed me the problem immediately. You have formulas in Columns F:M down to Row 13 whereas Rows 5:12 are displaying no data anywhere within that column range. The method I used (works good on constant values) fails for formulas displaying nothing. Here is a modified version of my macro that will work with your data setup...

[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", Columns("F").Find("*", , xlValues, , xlRows, xlPrevious)).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 for amending the code

Have tested and it works perfectly

 

[Mike Szczesny]

Can you use something like this? This is a crazy complicated, but robust formula. I created a similar sized data sample. Depending where you put your =int, mod, row and column you can present your information in various directions. Use Cntrl+Shift+Enter. This formula is [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =IFERROR(INDEX($A$1:$G$3,INT(SMALL(IF($A$1:$G$3<>"",(ROW($A$1:$G$3)-ROW($A$1)+1)*10^9+COLUMN($A$1:$G$3)-COLUMN($A$1)+1),ROWS($A$5:A5))/10^9),MOD(SMALL(IF($A$1:$G$3<>"",(ROW($A$1:$G$3)-ROW($A$1)+1)*10^9+COLUMN($A$1:$G$3)-COLUMN($A$1)+1),ROWS($A$5:A5)),10^9))," ")[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]a[/TD]
[TD="width: 64"]b[/TD]
[TD="width: 64"]c[/TD]
[TD="width: 64"]d[/TD]
[TD="width: 64"]e[/TD]
[TD="width: 64"]f[/TD]
[TD="width: 64"]g[/TD]
[/TR]
[TR]
[TD]h[/TD]
[TD]i[/TD]
[TD]j[/TD]
[TD]k[/TD]
[TD]l[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]m[/TD]
[TD]n[/TD]
[TD]o[/TD]
[TD]p[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]g[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]h[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]i[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]j[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]k[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]l[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]m[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]n[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]o[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]p[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[howard]

Thanks for the help


I cannot get it to work and also need and explanation of how it works

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
5
6
7
8
9
10
11
12
13
14
15	KN-63301
16	KN-63301
17	KN-63301
18	KN-63301
19	KN-63301
20	KN-63301
21	KN-63301
22	KN-63301
23
24
25
26
Sheet1

Cell Formulas
Range	Formula
F15:F22	{=IFERROR(INDEX($F$1:$M$12,INT(SMALL(IF($F$1:$M$12<>"",(ROW($F$1:$M$12)-ROW($F$1)+1)*10^9+COLUMN($F$1:$M$12)-COLUMN($F$1)+1),ROWS($F$1:M1))/10^9),MOD(SMALL(IF($F$1:$M$12<>"",(ROW($F$1:$M$12)-ROW($F$1)+1)*10^9+COLUMN($F$1:$M$12)-COLUMN($F$1)+1),ROWS($E$5:E5)),10^9))," ")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Rick Rothstein]

I cannot get it to work...

The formula works for me. Describe what happens (or doesn't happen) when you use it.

...and also need and explanation of how it works

I'll leave that to Mike to provide.

 

[Mike Szczesny]

Hi Howard,
I see that your rows part of your formula are not the same. They need to be both (rows($E$5:E5)). Regarding how and why this formula works, that is above my "pay grade". You would need to get one of the Excel MVPs to help explain this to you (and me). For the time being, that is best I can do.

Mike

 

[howard]

Thanks Mike for you input.

I'm just intrigued how it works. Will test tomorrow and if I have a problem, then hopefully one of the Excel MVP's may be able to assist