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:

Raw Data
Name	Parameter	Value
A100	Temp	5
A100	Press	10
A100	Flow	15
B200	Temp	20
B200	Press	25
B200	Flow	30
C300	Temp	35
C300	Press	40
C300	Flow	45

<tbody>
</tbody>

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

Sorted Data
Name	Temp	Press	Flow
A100	5	10	15
B200	20	25	30
C300	35	40	45

<tbody>
</tbody>

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:

Raw Data
Name	Parameter	Value
A100	Temp	5
A100	Press	10
A100	Flow	15
B200	Temp	20
B200	Press	25
B200	Flow	30
C300	Temp	35
C300	Press	40
C300	Flow	45

<tbody>
</tbody>

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

Sorted Data
Name	Temp	Press	Flow
A100	5	10	15
B200	20	25	30
C300	35	40	45

<tbody>
</tbody>

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.