Transpose pairs of data on 1 row to 2 columns

[Kaneda]

Hello,

I have data that are in pairs of data of hrs and cost for a given process under a name and trying to transpose the data to columns for a given process so I can use an advanced filter.

Here is what my data looks like and it spans from Column C to Column KZ and goes down about 100 rows for 100 processes.

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Name 1[/TD]
[TD][/TD]
[TD]Name 2[/TD]
[TD][/TD]
[TD]Name 3[/TD]
[TD][/TD]
[TD]Name 4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Hrs[/TD]
[TD]Cost[/TD]
[TD]Hrs[/TD]
[TD]Cost[/TD]
[TD]Hrs[/TD]
[TD]Cost[/TD]
[TD]Hrs[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]Process 1[/TD]
[TD]2.13[/TD]
[TD]1827[/TD]
[TD]0.44[/TD]
[TD]160[/TD]
[TD]1.44[/TD]
[TD]966[/TD]
[TD]0.44[/TD]
[TD]357[/TD]
[/TR]
[TR]
[TD]Process 2[/TD]
[TD]9.63[/TD]
[TD]2005[/TD]
[TD]0.5[/TD]
[TD]131[/TD]
[TD]2.5[/TD]
[TD]485[/TD]
[TD]1.75[/TD]
[TD]465[/TD]
[/TR]
[TR]
[TD]Process 3[/TD]
[TD]0.13[/TD]
[TD]32[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0.5[/TD]
[TD]129[/TD]
[/TR]
</tbody>[/TABLE]

Output I am trying to get to:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Process 1[/TD]
[TD][/TD]
[TD]Process 2[/TD]
[TD][/TD]
[TD]Process 3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Hrs[/TD]
[TD]Cost[/TD]
[TD]Hrs[/TD]
[TD]Cost[/TD]
[TD]Hrs[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]Name 1[/TD]
[TD]2.13[/TD]
[TD]1827[/TD]
[TD]9.63[/TD]
[TD]2005[/TD]
[TD]0.13[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]Name 2[/TD]
[TD]0.44[/TD]
[TD]160[/TD]
[TD]0.5[/TD]
[TD]131[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Name 3[/TD]
[TD]1.44[/TD]
[TD]966[/TD]
[TD]2.5[/TD]
[TD]485[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Name 4[/TD]
[TD]0.44[/TD]
[TD]357[/TD]
[TD]1.75[/TD]
[TD]465[/TD]
[TD]0.5[/TD]
[TD]129[/TD]
[/TR]
</tbody>[/TABLE]

I tried using copy/transpose and using an index/match formulas but I think the best way would be a macro but I'm open to formulas as well. Any help would be greatly appreciated!

 

[hiker95]

Kaneda,

Here is a macro solution for you to consider, that is based on your two flat text displays, and, will adjust to the number of raw data rows, and, columns.

I assume that both worksheets already exist, and, that the raw data worksheet name is Input, and, that the results will be written to worksheet Output.

You can change the worksheet names in the macro.

With your raw data in worksheet Input in Range("A1:K5").

The results will be written to worksheet Output in Range("A1:G6").


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 ReorganizeData()
' hiker95, 10/17/2017, ME1025262
Application.ScreenUpdating = False
Dim wi As Worksheet, wo As Worksheet
Dim lr As Long, lc As Long, i As Long, c As Long
Dim sr As Long, sc As Long
Dim na As Range, pr As Range
Dim o
Set wi = Sheets("Input")    '<-- you can change the sheet name here
Set wo = Sheets("Output")   '<-- you can change the sheet name here
wo.UsedRange.ClearContents
With wi
  lr = .Cells.Find("*", , xlValues, xlWhole, xlByRows, xlPrevious, False).Row
  lc = .Cells.Find("*", , xlValues, xlWhole, xlByColumns, xlPrevious, False).Column
  sr = 1: sc = 2
  For i = 3 To lr Step 1
    wo.Cells(sr, sc).Value = wi.Cells(i, 3).Value
    wo.Cells(sr + 1, sc).Resize(, 2).Value = Array("Hrs", "Cost")
    sc = sc + 2
  Next i
  o = wi.Range(.Cells(1, 4), .Cells(1, lc)).Value
  wo.Range("A3").Resize(UBound(o, 2)) = Application.Transpose(o)
  Erase o
  lr = wo.Cells(Rows.Count, 1).End(xlUp).Row
  wo.Range("A3:A" & lr).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
End With
With wo
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  For c = 2 To lc Step 2
    Set pr = wi.Columns(3).Find(wo.Cells(1, c).Value, LookAt:=xlWhole)
    For i = 3 To lr Step 1
      Set na = wi.Rows(1).Find(wo.Cells(i, 1).Value, LookAt:=xlWhole)
      If (Not na Is Nothing) * (Not pr Is Nothing) Then
        .Cells(i, c).Value = wi.Cells(pr.Row, na.Column).Value
        .Cells(i, c + 1).Value = wi.Cells(pr.Row, na.Column + 1).Value
      End If
    Next i
  Next c
  .UsedRange.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 ReorganizeData macro.

 

[Kaneda]

Thank you very much! I'll give it a try tonight.

 

[hiker95]

Thank you very much! I'll give it a try tonight.

Kaneda,

You are very welcome.

I will be watching for your next reply.