Transposing data SETS from vertical to horizontal

[tutuzdad]

I desperately need to transpose multiple SETS of data so that the now vertical columns are horizontal. I know about transposing but not when it comes to sets. For simplicity sake let’s say I have a long list of multiple addresses with the headers, NAME, ADDR, CITY, STATE, ZIP. Transposing these headers into a horizontal table is fairly simple. It’s getting the 5 rows of names, addresses, cities, states and zips under the correct headers that has me confounded. I’m using 2010. I’m pretty familiar with excel. Ideally a solution that I can reapply to future lists since this is something I do regularly and my output is always formatted this same annoying way. I don’t do VB but if we have to and you can simply it step-by-step for me I’d really appreciate it.

 

[tutuzdad]

tutuzdad, New sample raw data in worksheet Sheet1 (you can change the raw data worksheet name in the macro): Excel 2007

	A	B	C
1	A	1
2	B	2
3	C	3
4	D	4
5	E	5
6	F	6
7	G	7
8	H	8
9	I	9
10	J	10
11	K	11
12	L	12
13	M	13
14	N	14
15	O	15
16	P	16
17	Q	17
18	R	18
19	S	19
20	A	20
21	B	21
22	C	22
23	D	23
24	E	24
25	F	25
26	G	26
27	H	27
28	I	28
29	J	29
30	K	30
31	L	31
32	M	32
33	N	33
34	O	34
35	P	35
36	Q	36
37	R	37
38	S	38
39	A	39
40	B	40
41	C	41
42	D	42
43	E	43
44	F	44
45	G	45
46	H	46
47	I	47
48	J	48
49	K	49
50	L	50
51	M	51
52	N	52
53	O	53
54	P	54
55	Q	55
56	R	56
57	S	57
58

<colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

After the macro (using two arrays in memory) in a new worksheet Results: Excel 2007

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
2	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19
3	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38
4	39	40	41	42	43	44	45	46	47	48	49	50	51	52	53	54	55	56	57
5

<colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead>
</thead><tbody>
</tbody>

Results​

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).

  Sub TransposeData_V3() ' hiker95, 03/18/2015, ME842652 Dim w1 As Worksheet, wr As Worksheet Dim a As Variant, o As Variant Dim i As Long, j As Long Application.ScreenUpdating = False Set w1 = Sheets("Sheet1")   ''<-- change the raw data worksheet name here a = w1.Cells(1, 1).CurrentRegion ReDim o(1 To UBound(a, 1) / 19, 1 To 19) For i = 1 To UBound(a, 1) Step 19   j = j + 1   o(j, 1) = a(i, 2)   o(j, 2) = a(i + 1, 2)   o(j, 3) = a(i + 2, 2)   o(j, 4) = a(i + 3, 2)   o(j, 5) = a(i + 4, 2)   o(j, 6) = a(i + 5, 2)   o(j, 7) = a(i + 6, 2)   o(j, 8) = a(i + 7, 2)   o(j, 9) = a(i + 8, 2)   o(j, 10) = a(i + 9, 2)   o(j, 11) = a(i + 10, 2)   o(j, 12) = a(i + 11, 2)   o(j, 13) = a(i + 12, 2)   o(j, 14) = a(i + 13, 2)   o(j, 15) = a(i + 14, 2)   o(j, 16) = a(i + 15, 2)   o(j, 17) = a(i + 16, 2)   o(j, 18) = a(i + 17, 2)   o(j, 19) = a(i + 18, 2) Next i If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results" Set wr = Sheets("Results") With wr   .UsedRange.Clear   .Cells(1, 1).Resize(, 19).Value = Application.Transpose(w1.Range("A1:A19"))   .Cells(2, 1).Resize(UBound(o, 1), UBound(o, 2)) = o   .Columns(1).Resize(, UBound(o, 2)).AutoFit   .Activate 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 TransposeData_V3 macro.

Thank you. I envy your command of Excel. You are unquestionably a world class pro. Just FYI - I don't know how often (if ever) you hear "the rest of the story", but in solving this problem you have unknowingly provided a very important service to more than 1,000,000 people. Transposing the data so that it is formatted in this way essentially "translates it" and makes it possible for a public safety entity to feed it to analysis software, which will, in turn, analyze the data and help make the community a much safer place to live and play. The solution that you provided is the final key to a public safety project that began some 14 months ago and in about 15 minutes we will stick the key in the ignition and take the project out for it's first test drive. I just thought you might like to know the measure of the impact your involvement and selfless contribution has had on so many. You have my gratitude on their behalf. In an attempt to proactively prepare for whatever glitches might arise, at some point I might like to ask you if there is a way to ensure that the raw data is bound to it's corresponding header. For example, in the rare instance that some of the sets are NOT 19 rows long and happen to be only 17 or 18 rows long (which is somewhat rare and when it happens that line is not blank, but missing entirely (row, data, header and all) is there a way to transpose the data as you have done, but guarantee that the movement is contingent upon it's corresponding header - REGARDLESS of what row it is on. Put another way perhaps, how might we transpose this data and achieve the same end result if the sets were not consistently in the same order. [They are typically ordered 1,2, 3, 4, 6... but one or more of them happened to be 1, 2, 4, 5, 6... and another happened to be 1, 3, 4, 2, 6, 5... This may be something that requires some thought, but in the meantime let me say again how deeply appreciattive we are for your services rendered. Thank you so much.

 

[hiker95]

tutuzdad,

Thanks for the feedback.

You are very welcome. Glad I could help.


Unless there is a particular reason to do so, it is best not to quote whole long posts like you did with mine. It makes the thread harder to navigate/read and just takes up unnecessary space.

1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
2. Quote ONLY the specific part of the post that is relevant - - not the entire post.

This will keep thread clutter to a minimum and make the discussion easier to follow.

And, when you respond to your helper, please use their site ID/username/handle.

For example, in the rare instance that some of the sets are NOT 19 rows long and happen to be only 17 or 18 rows long (which is somewhat rare and when it happens that line is not blank, but missing entirely (row, data, header and all) is there a way to transpose the data as you have done, but guarantee that the movement is contingent upon it's corresponding header - REGARDLESS of what row it is on. Put another way perhaps, how might we transpose this data and achieve the same end result if the sets were not consistently in the same order. [They are typically ordered 1,2, 3, 4, 6... but one or more of them happened to be 1, 2, 4, 5, 6... and another happened to be 1, 3, 4, 2, 6, 5...

In order to continue I will have to see your actual NEW raw data structure, and, results (manually formatted by you) for the results you are looking for.

You will have to find a way, maybe on another computer outside of work, to post your actual NEW raw data structure, and, results (manually formatted by you) for the results you are looking for.

You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.


If you can not provide the above, then:

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[tutuzdad]

hiker95, We are learning much more than macros here. Thank you for both lessons.