Multiple Columns to Single Rows

[leott]

Hello all,
My data looks like below.
row 1 is just a label to show this example
name column(s) are A through L
Number column(s) are M through Q
state column(s) are M through Q
info column(s) are M through Q
comments column(s) are M through Q
value column is R through W

What I would like is each name data in one row followed by columns number, state, info and comments

name 1 number state info comments

Each "group" of Name rows occupy four rows but "name" is only in first of the four rows (and are repeated thousands of times)

Thanks for any suggestions

excel sheet:

name

name1

Number:

value1

name1

state

value 2

name1

info

value3

name1

comments

value4

name2

Number:

value5

name2

state

value6

name2

info

value7

name2

comments

value8

name3

Number:

value9

name3

state

value10

name3

info

value11

name3

comments

value12

name4

Number:

value13

name4

state

value14

name4

info

value15

name4

comments

value16

<tbody>
</tbody>

 

[hiker95]

leott,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?

It would really help if we could see your actual raw data worksheet, and, if we could see on another worksheet (manually formatted by you) the results you are looking for.

Because of the description of your dataset, screenshots are probably out of the question, as per your original screenshot.

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.

 

[leott]

What version of Excel and Windows are you using?

It would really help if we could see your actual raw data worksheet, and, if we could see on another worksheet (manually formatted by you) the results you are looking for.

Because of the description of your dataset, screenshots are probably out of the question, as per your original screenshot.

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.[/QUOTE]

I am using Excel 2003 and have viewer for 2007. Workbooks were put together with 2007.

Links to files:
workbook to change:
https://app.box.com/s/14w2i8ooya0o5i0ffsta

final workbook needed (with arbitrary data)
https://app.box.com/s/rrew1dc87q1uqmp0g8xn

Thanks for suggestions!

 

[hiker95]

leott,

Thanks for the workbooks.

I see you are a fellow birder - I just love the raptors.

Sample raw data in worksheet Sheet1 (not all rows are shown for brevity):

Excel 2007
	A	M	R	W
1	Species	Year	2012 [113] Count Date: 12/30/2012 # Participants: 16 # Species Reported: 41 Total Hrs.: 25.50
2	Canada Goose [Branta canadensis]	Number:	45
3		Num/Party Hrs.:	1.7647
4		Flags:
5		Editor Comments:
6	Mallard [Anas platyrhynchos]	Number:	143
7		Num/Party Hrs.:	5.6078
8		Flags:
9		Editor Comments:
10	American Green-winged Teal [Anas crecca]	Number:	4
11		Num/Party Hrs.:	0.1569
12		Flags:
13		Editor Comments:
14	Ring-necked Duck [Aythya collaris]	Number:	34
15		Num/Party Hrs.:	1.3333
16		Flags:
17		Editor Comments:
18	Lesser Scaup [Aythya affinis]	Number:	1
19		Num/Party Hrs.:	0.0392
20		Flags:
21		Editor Comments:
22	Wild Turkey [Meleagris gallopavo]	Number:	1
23		Num/Party Hrs.:	0.0392
24		Flags:
25		Editor Comments:
26	Bald Eagle [Haliaeetus leucocephalus]	Number:	4
27		Num/Party Hrs.:	0.1569
28		Flags:	HC,
29		Editor Comments:
Sheet1

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

Excel 2007
	A	B	C	D	E
1	Species	Number	Hours	Flags	Comments
2	Canada Goose	45	1.7647
3	Mallard	143	5.6078
4	American Green-winged Teal	4	0.1569
5	Ring-necked Duck	34	1.3333
6	Lesser Scaup	1	0.0392
7	Wild Turkey	1	0.0392
8	Bald Eagle	4	0.1569	HC,
9	Red-tailed Hawk	6	0.2353
10	Golden Eagle	4	0.1569
11	American Kestrel	1	0.0392
12	Wilson's Snipe	cw	0
13	Rock Pigeon	56	2.1961
14	Eurasian Collared-Dove	12	0.4706	HC,
15	Belted Kingfisher	2	0.0784
16	Downy Woodpecker	1	0.0392
17	Hairy Woodpecker	1	0.0392
18	Northern (Red-shafted) Flicker	2	0.0784
19	Gray Jay	2	0.0784
20	Steller's Jay	35	1.3725
21	Western Scrub-Jay	27	1.0588
22	Clark's Nutcracker	1	0.0392
23	Black-billed Magpie	163	6.3922
24	American Crow	179	7.0196
25	Common Raven	131	5.1373	HC,
26	Black-capped Chickadee	87	3.4118
27	Mountain Chickadee	80	3.1373
28	Red-breasted Nuthatch	2	0.0784
29	White-breasted Nuthatch	1	0.0392
30	American Dipper	36	1.4118	HC,
31	Townsend's Solitaire	13	0.5098
32	American Robin	1	0.0392
33	European Starling	56	2.1961
34	Cedar Waxwing	cw	0
35	Song Sparrow	3	0.1176
36	Dark-eyed Junco	3	0.1176
37	Dark-eyed (Gray-headed) Junco	2	0.0784
38	Gray-crowned Rosy-Finch	50	1.9608
39	Brown-capped Rosy-Finch	162	6.3529
40	Pine Grosbeak	cw	0
41	Cassin's Finch	1	0.0392
42	House Finch	29	1.1373
43	Common Redpoll	7	0.2745	US,HC,	NC
44	American Goldfinch	3	0.1176
45	Evening Grosbeak	30	1.1765
46	House Sparrow	176	6.902
47
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).

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.

Option Explicit
Sub ExtractData()
' hiker95, 12/24/2013
' http://www.mrexcel.com/forum/excel-questions/746701-multiple-columns-single-rows.html
Dim a As Variant, o As Variant, s
Dim i As Long, ii As Long, lr As Long, n As Long, alen As Long
With Sheets("Sheet1")
  alen = .Cells(1, 1).ColumnWidth * 2
  lr = .Cells(Rows.Count, "M").End(xlUp).Row
  a = .Range("A2:W" & lr)
  n = Application.Ceiling(lr - 1, 4)
  ReDim o(1 To n / 4, 1 To 5)
End With
For i = 1 To UBound(a, 1) Step 4
  ii = ii + 1
  s = Split(a(i, 1), vbLf)
  o(ii, 1) = s(0)
  o(ii, 2) = a(i, 18)
  o(ii, 3) = a(i + 1, 18)
  o(ii, 4) = a(i + 2, 18)
  o(ii, 5) = a(i + 3, 18)
Next i
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=Sheets("Sheet1")).Name = "Results"
With Sheets("Results")
  .UsedRange.Clear
  .Cells(1, 1).Resize(, 5).Value = [{"Species","Number","Hours","Flags","Comments"}]
  .Cells(1, 1).ColumnWidth = alen
  .Cells(2, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
  .Columns.AutoFit
  .Activate
End With
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

Then run the ExtractData macro.

 

[leott]

leott,

Thanks for the workbooks.

I see you are a fellow birder - I just love the raptors.


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

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension [B].xlsm[/B]

Then run the [B]ExtractData[/B] macro.[/QUOTE]

The code worked fantastic!  Thanks much!  I have put 20-30 hours trying to convert.

Thanks again fellow birder!
checkout: http://coloradobirdingsociety.net16.net 

Leonard

 

[hiker95]

leott,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

I will check out the link - enjoy.