Multiple Columns to Single Rows

L

leott

New Member
Joined
Dec 23, 2013
Messages
5
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:
[TABLE="width: 1338"]
<tbody>[TR]
[TD]name
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]name1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Number:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]value1[/TD]
[/TR]
[TR]
[TD]name1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]state[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]value 2[/TD]
[/TR]
[TR]
[TD]name1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]info[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]value3[/TD]
[/TR]
[TR]
[TD]name1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]comments[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]value4[/TD]
[/TR]
[TR]
[TD]name2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Number:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]value5[/TD]
[/TR]
[TR]
[TD]name2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]state[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]value6[/TD]
[/TR]
[TR]
[TD]name2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]info[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]value7[/TD]
[/TR]
[TR]
[TD]name2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]comments[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]value8[/TD]
[/TR]
[TR]
[TD]name3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Number:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]value9[/TD]
[/TR]
[TR]
[TD]name3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]state[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]value10[/TD]
[/TR]
[TR]
[TD]name3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]info[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]value11[/TD]
[/TR]
[TR]
[TD]name3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]comments[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]value12[/TD]
[/TR]
[TR]
[TD]name4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Number:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]value13[/TD]
[/TR]
[TR]
[TD]name4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]state[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]value14[/TD]
[/TR]
[TR]
[TD]name4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]info[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]value15[/TD]
[/TR]
[TR]
[TD]name4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]comments[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]value16[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
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.
 
Upvote 0
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!
 
Upvote 0
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
AMRW
1SpeciesYear2012 [113] Count Date: 12/30/2012 # Participants: 16 # Species Reported: 41 Total Hrs.: 25.50
2Canada Goose [Branta canadensis]Number:45
3Num/Party Hrs.:1.7647
4Flags:
5Editor Comments:
6Mallard [Anas platyrhynchos]Number:143
7Num/Party Hrs.:5.6078
8Flags:
9Editor Comments:
10American Green-winged Teal [Anas crecca]Number:4
11Num/Party Hrs.:0.1569
12Flags:
13Editor Comments:
14Ring-necked Duck [Aythya collaris]Number:34
15Num/Party Hrs.:1.3333
16Flags:
17Editor Comments:
18Lesser Scaup [Aythya affinis]Number:1
19Num/Party Hrs.:0.0392
20Flags:
21Editor Comments:
22Wild Turkey [Meleagris gallopavo]Number:1
23Num/Party Hrs.:0.0392
24Flags:
25Editor Comments:
26Bald Eagle [Haliaeetus leucocephalus]Number:4
27Num/Party Hrs.:0.1569
28Flags:HC,
29Editor Comments:
Sheet1


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


Excel 2007
ABCDE
1SpeciesNumberHoursFlagsComments
2Canada Goose451.7647
3Mallard1435.6078
4American Green-winged Teal40.1569
5Ring-necked Duck341.3333
6Lesser Scaup10.0392
7Wild Turkey10.0392
8Bald Eagle40.1569HC,
9Red-tailed Hawk60.2353
10Golden Eagle40.1569
11American Kestrel10.0392
12Wilson's Snipecw0
13Rock Pigeon562.1961
14Eurasian Collared-Dove120.4706HC,
15Belted Kingfisher20.0784
16Downy Woodpecker10.0392
17Hairy Woodpecker10.0392
18Northern (Red-shafted) Flicker20.0784
19Gray Jay20.0784
20Steller's Jay351.3725
21Western Scrub-Jay271.0588
22Clark's Nutcracker10.0392
23Black-billed Magpie1636.3922
24American Crow1797.0196
25Common Raven1315.1373HC,
26Black-capped Chickadee873.4118
27Mountain Chickadee803.1373
28Red-breasted Nuthatch20.0784
29White-breasted Nuthatch10.0392
30American Dipper361.4118HC,
31Townsend's Solitaire130.5098
32American Robin10.0392
33European Starling562.1961
34Cedar Waxwingcw0
35Song Sparrow30.1176
36Dark-eyed Junco30.1176
37Dark-eyed (Gray-headed) Junco20.0784
38Gray-crowned Rosy-Finch501.9608
39Brown-capped Rosy-Finch1626.3529
40Pine Grosbeakcw0
41Cassin's Finch10.0392
42House Finch291.1373
43Common Redpoll70.2745US,HC,NC
44American Goldfinch30.1176
45Evening Grosbeak301.1765
46House Sparrow1766.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.

Code: 
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.
 
Upvote 0
hiker95 said:
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

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
Click to expand...
 
Upvote 0
leott,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

I will check out the link - enjoy.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question Question
Send email to specific name that meets weeknumber criteria
Replies
0
Views
185
Birdy01962
B
ExcelNewbie2020
  • Solved
Restructure list form from horizontal to vertical based on condition
Replies
2
Views
92
Fluff
Fluff
ExcelNewbie2020
  • Solved
FILTER IF the criteria is within the set value (figures)
Replies
8
Views
545
Peter_SSs
Peter_SSs
G
  • Question Question
Populating multiple rows from one sheet to another
Replies
4
Views
502
Fluff
Fluff
G
  • Solved
need macro help to concatenate columns based on criteria
Replies
2
Views
429
Gogleguy
G

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top