Collating data from Multiple rows to columns

[DarkV]

Hello

I'm quite new to data manipulation with Excel, but have been tasked with converting a report into a more efficient format. The report comes in a format as below (but more fields and much more data (for up to 5000 people)

[TABLE="width: 347"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Jon Smith[/TD]
[TD]Age[/TD]
[TD]57[/TD]
[/TR]
[TR]
[TD]Jon Smith[/TD]
[TD]Height[/TD]
[TD]1.67[/TD]
[/TR]
[TR]
[TD]Jon Smith[/TD]
[TD]Weight[/TD]
[TD]96[/TD]
[/TR]
[TR]
[TD]Jon Smith[/TD]
[TD]Job[/TD]
[TD]Accountant[/TD]
[/TR]
[TR]
[TD]Bert Roberts[/TD]
[TD]Age[/TD]
[TD]43[/TD]
[/TR]
[TR]
[TD]Bert Roberts[/TD]
[TD]Height[/TD]
[TD]1.72[/TD]
[/TR]
[TR]
[TD]Bert Roberts[/TD]
[TD]Weight[/TD]
[TD]86[/TD]
[/TR]
[TR]
[TD]Bert Roberts[/TD]
[TD]Job[/TD]
[TD]Supervisor[/TD]
[/TR]
</tbody>[/TABLE]

I need to convert this so that I have the person's name in Column A, and then the subsequent columns show the data from the fields (in the example Age, Height and Weight) so that this can be easily exported and viewed by name.

I've tried a number of options, but am at a loss, as the names, and number of names on the report differs each day.

Any suggestions would be most appreciated.

DarkV

 

[hiker95]

DarkV,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


3. Will the raw data always be grouped/sorted in column A?


4. What is the raw data worksheet name?

5. What is the worksheet name for where the results will be written to?

The report comes in a format as below (but more fields and much more data (for up to 5000 people)

6. Will there be more than the five data titles (Name, Age, Height, Weight, and, Job) in the results worksheet?


I would like more information. Please see the Forum Use Guidelines in the following link:

http://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html


See reply #2 at the next link, if you want to show small screenshots.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


If you are not able to provide screenshots, then:

You can post your workbook/worksheets to the following free site (sensitive data changed), and provide us with a link to your workbook:

https://dropbox.com

 

[hiker95]

DarkV,

Can you supply a list of all the unique titles in the results worksheet, row 1, like the following?
Name
Age
Height
Weight
Job

 

[hiker95]

DarkV,

If you can supply the information I have requested, then, I can re-write the below macro.

Here is a macro for you to consider that is based on your flat text display, that will adjust to the varying number of raw data rows in your raw data worksheet, and, will adjust to the varying number of columns needed in the results worksheet.

I assume that both worksheets, Report, and, Master already exist.

You can change the worksheet names in the macro.

Sample raw data in worksheet Report:

Excel 2007
	A	B	C
1	Jon Smith	Age	57
2	Jon Smith	Height	1.67
3	Jon Smith	Weight	96
4	Jon Smith	Job	Accountant
5	Bert Roberts	Age	43
6	Bert Roberts	Height	1.72
7	Bert Roberts	Weight	86
8	Bert Roberts	Job	Supervisor
9
Report

Sample worksheet Master with just a title in cell A1:

Excel 2007
	A	B	C	D	E	F
1	Name
2
3
4
Master

After the macro:

Excel 2007
	A	B	C	D	E	F
1	Name	Age	Height	Weight	Job
2	Jon Smith	57	1.67	96	Accountant
3	Bert Roberts	43	1.72	86	Supervisor
4
Master

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.

Sub ReorgData()
' hiker95, 02/24/2016, ME923784
Dim wr As Worksheet, wm As Worksheet
Dim r As Range, nr As Long, nc As Long
Dim na As Range, t As Range
Application.ScreenUpdating = False
Set wr = Sheets("Report")   '<-- you can change the sheet name here
Set wm = Sheets("Master")   '<-- you can change the sheet name here
With wr
  For Each r In .Range("A1", .Range("A" & Rows.Count).End(xlUp))
    Set na = wm.Columns(1).Find(r.Value, LookAt:=xlWhole)
    If na Is Nothing Then
      nr = wm.Cells(wm.Rows.Count, "A").End(xlUp).Row + 1
      wm.Cells(nr, 1).Value = r.Value
    End If
    Set t = wm.Rows(1).Find(r.Offset(, 1).Value, LookAt:=xlWhole)
    If t Is Nothing Then
      nc = wm.Cells(1, wm.Columns.Count).End(xlToLeft).Column + 1
      wm.Cells(1, nc).Value = r.Offset(, 1).Value
    End If
    If (na Is Nothing) * (t Is Nothing) Then
      wm.Cells(nr, nc).Value = r.Offset(, 2).Value
    End If
    If (Not na Is Nothing) * (Not t Is Nothing) Then
      wm.Cells(na.Row, t.Column).Value = r.Offset(, 2).Value
    End If
    If (Not na Is Nothing) * (t Is Nothing) Then
      wm.Cells(na.Row, nc).Value = r.Offset(, 2).Value
    End If
    If (na Is Nothing) * (Not t Is Nothing) Then
      wm.Cells(nr, t.Column).Value = r.Offset(, 2).Value
    End If
  Next r
End With
With wm
  .Columns.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 ReorgData macro.