How do I sort this Data?

[jpeavler]

Hello! I am trying to figure out a way to sort raw data into a desired format.

This is the format of the raw data:

[TABLE="class: grid, width: 193"]
<tbody>[TR]
[TD="colspan: 3, align: center"]Raw Data[/TD]
[/TR]
[TR]
[TD="align: center"]Name[/TD]
[TD="align: center"]Parameter[/TD]
[TD="align: center"]Value[/TD]
[/TR]
[TR]
[TD="align: center"]A100[/TD]
[TD="align: center"]Temp[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]A100[/TD]
[TD="align: center"]Press[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]A100[/TD]
[TD="align: center"]Flow[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]B200[/TD]
[TD="align: center"]Temp[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"]B200[/TD]
[TD="align: center"]Press[/TD]
[TD="align: center"]25[/TD]
[/TR]
[TR]
[TD="align: center"]B200[/TD]
[TD="align: center"]Flow[/TD]
[TD="align: center"]30[/TD]
[/TR]
[TR]
[TD="align: center"]C300[/TD]
[TD="align: center"]Temp[/TD]
[TD="align: center"]35[/TD]
[/TR]
[TR]
[TD="align: center"]C300[/TD]
[TD="align: center"]Press[/TD]
[TD="align: center"]40[/TD]
[/TR]
[TR]
[TD="align: center"]C300[/TD]
[TD="align: center"]Flow[/TD]
[TD="align: center"]45[/TD]
[/TR]
</tbody>[/TABLE]

This is the format that I want after the data is sorted:

[TABLE="class: grid, width: 247"]
<tbody>[TR]
[TD="colspan: 4, align: center"]Sorted Data[/TD]
[/TR]
[TR]
[TD="align: center"]Name[/TD]
[TD="align: center"]Temp[/TD]
[TD="align: center"]Press[/TD]
[TD="align: center"]Flow[/TD]
[/TR]
[TR]
[TD="align: center"]A100[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]B200[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]30[/TD]
[/TR]
[TR]
[TD="align: center"]C300[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]45[/TD]
[/TR]
</tbody>[/TABLE]


Any suggestions are appreciated. Thanks!

 

[ScottyZ92]

Hello! I am trying to figure out a way to sort raw data into a desired format.

This is the format of the raw data:

[TABLE="class: grid, width: 193"]
<tbody>[TR]
[TD="colspan: 3, align: center"]Raw Data[/TD]
[/TR]
[TR]
[TD="align: center"]Name[/TD]
[TD="align: center"]Parameter[/TD]
[TD="align: center"]Value[/TD]
[/TR]
[TR]
[TD="align: center"]A100[/TD]
[TD="align: center"]Temp[/TD]
[TD="align: center"]5[/TD]
[/TR]
[TR]
[TD="align: center"]A100[/TD]
[TD="align: center"]Press[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]A100[/TD]
[TD="align: center"]Flow[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]B200[/TD]
[TD="align: center"]Temp[/TD]
[TD="align: center"]20[/TD]
[/TR]
[TR]
[TD="align: center"]B200[/TD]
[TD="align: center"]Press[/TD]
[TD="align: center"]25[/TD]
[/TR]
[TR]
[TD="align: center"]B200[/TD]
[TD="align: center"]Flow[/TD]
[TD="align: center"]30[/TD]
[/TR]
[TR]
[TD="align: center"]C300[/TD]
[TD="align: center"]Temp[/TD]
[TD="align: center"]35[/TD]
[/TR]
[TR]
[TD="align: center"]C300[/TD]
[TD="align: center"]Press[/TD]
[TD="align: center"]40[/TD]
[/TR]
[TR]
[TD="align: center"]C300[/TD]
[TD="align: center"]Flow[/TD]
[TD="align: center"]45[/TD]
[/TR]
</tbody>[/TABLE]

This is the format that I want after the data is sorted:

[TABLE="class: grid, width: 247"]
<tbody>[TR]
[TD="colspan: 4, align: center"]Sorted Data[/TD]
[/TR]
[TR]
[TD="align: center"]Name[/TD]
[TD="align: center"]Temp[/TD]
[TD="align: center"]Press[/TD]
[TD="align: center"]Flow[/TD]
[/TR]
[TR]
[TD="align: center"]A100[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]B200[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]30[/TD]
[/TR]
[TR]
[TD="align: center"]C300[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]45[/TD]
[/TR]
</tbody>[/TABLE]


Any suggestions are appreciated. Thanks!

Hi,

Go to the insert tab. Choose insert table > pivot table.

Select everything but Raw Data. Hit enter.

Place Name in the rows section in the field list of the pivot table. Place Parameter in the Columns section of the field list. Place Values in the values table of the field list.

Hope this helps.

 

[kalak]

You can also run a macro.
If your data start in cell A1, just run this:

Sub rearrange()

Dim d1 As Object, d2 As Object, c()
Dim a, u1, u2, i As Long, n As Long

Set d1 = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")
a = Range("A1").CurrentRegion
n = UBound(a)
ReDim c(1 To n, 1 To n)

For i = 2 To n
    u1 = a(i, 1)
    u2 = a(i, 2)
    If Not d1.exists(u1) Then d1(u1) = d1.Count + 1
    If Not d2.exists(u2) Then d2(u2) = d2.Count + 1
    c(d1(u1), d2(u2)) = c(d1(u1), d2(u2)) & " " & a(i, 3)
Next i

[e1] = "Sorted Data": [e2] = "Name"
[e3].Resize(d2.Count, 1) = Application.Transpose(d1.keys)
[f2].Resize(1, d1.Count) = d2.keys
[f3].Resize(d1.Count, d2.Count) = c

End Sub

 

[jpeavler]

Thank you. That did the trick perfectly. I also formatted my raw data as a named table and then I used the named table for the data range when setting up the pivot table. This allowed me to have a dynamic range for my pivot table so that I did not end up with (blank) columns or rows in my pivot table. That was useful for my application. Thanks again for the tip.