Converting vertical dataset to horizontal

[mhaidar81]

Hello,

I am trying to convert a vertical list to a horizontal one, but I am having trouble placing the cells in the correct column.

Here's an example:

ID #| Code| Rate| Price
135 |A001| 2560|1300
135 |A003| 3260|9631
135 |A002| 4523|8523

This is the outcome I'm looking for:


ID #|Code1|Rate|Price|Code2|Rate|Price|Code3|Rate|Price
135 |A001|2560| 1300|A003|3260|9631|A002|4523|8523

However, I want to take it one more step and place all A001 cells in Column B, all A002 items in Column D, all A003 items in Column F, etc...

The code below will convert the data into a horizontal form. Any idea how to sort the data into the desired columns?

Option Explicit


Sub MergeData()
Dim LastRow As Long, NextCol As Long
Dim LastCol As Long, Rw As Long, Cnt As Long
Dim delRNG As Range
Application.ScreenUpdating = False




    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1").CurrentRegion.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes
    
    Set delRNG = Range("A" & LastRow + 10)
    
    For Rw = LastRow To 2 Step -1
        If Cells(Rw, "A").Value = Cells(Rw - 1, "A").Value Then
            Range(Cells(Rw, "B"), Cells(Rw, Columns.Count).End(xlToLeft)).Copy _
                Cells(Rw - 1, Columns.Count).End(xlToLeft).Offset(0, 1)
            Set delRNG = Union(delRNG, Range("A" & Rw))
        End If
    Next Rw


    delRNG.EntireRow.Delete (xlShiftUp)
    Set delRNG = Nothing




    NextCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1
    LastCol = Cells(1, 1).CurrentRegion.Columns.Count
    Range("B1", Cells(1, NextCol - 1)).Copy Range(Cells(1, NextCol), Cells(1, LastCol))


Cells.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

I would appreciate any help with this.

 

[mhaidar81]

Any way I can use an if statement to move cells to the correct column?

 

[JLGWhiz]

Any way I can use an if statement to move cells to the correct column?

However, I want to take it one more step and place all A001 cells in Column B, all A002 items in Column D, all A003 items in Column F, etc...

You need to give a better explanation of this statement or show an example of what you expect to see. Click on the word 'Attachments' below for a link to how to attach images.

 

[mhaidar81]

Before

ID	Code	Count	Total
113	00000.AC.456	1519	1671.8
113	00000.AC.123	75	159.2
113	00000.AC.789	432	558.78

<tbody>
</tbody>

After

ID	Code 1	Count	Rate	Code 2	Count	Rate	Code 3	Count	Rate
113	00000.AC.123	75	159.2	00000.AC.456	1519	1671.8	00000.AC.789	432	558.78

<tbody>
</tbody>

There will be Code 4, Code 5, Code 6, and Code 7 as well, but I didn't include them in the example above.

 

[JLGWhiz]

See it this works for you

Sub t()
Dim  lc As Long, i As Long
With ActiveSheet    
    For i = .Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        If Trim(.Cells(i, 1).Value) = Trim(.Cells(i - 1, 1).Value) Then
            lc = .Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious).Column
            .Range(.Cells(i, 2), .Cells(i, lc)).Cut .Cells(i - 1, Columns.Count).End(xlToLeft).Offset(, 1)
            .Cells(i, 1).EntireRow.Delete xlShiftUp
        End If
    Next
End With
End Sub

 

[mhaidar81]

Perfect, thank you!

The last step I'm looking for is to put the codes in columns with predefined headers.

If code is "00000.AC.123", place in column B. If code is "00000.AC.456," place in column E. If code is "00000.AC.789", place in column H. If code is "00000.AD.123," place in column K.

Before:

ID	Code	Count	Rate
113	00000.AC.456	1519	1671.8
113	00000.AC.123	75	159.2
113	00000.AC.789	432	558.78
630	00000.AC.456	32	362.32
630	00000.AD.123	14	459.62
911	00000.AC.789	63	2253.12

<tbody>
</tbody>

After

A ID	B Code 1	C Count	D Rate	E Code 2	F Count	G Rate	H Code 3	I Count	J Rate	K Code 4	L Count	M Rate
113	00000.AC.123	75	159.2	00000.AC.456	1519	1671.8	00000.AC.789	432	558.78
630				00000.AC.456	32	362.32				00000.AD.123	14	459.62
911							00000.AC.789	63	2253.12

<tbody>
</tbody>

 

[JLGWhiz]

Perfect, thank you!

The last step I'm looking for is to put the codes in columns with predefined headers.

If code is "00000.AC.123", place in column B. If code is "00000.AC.456," place in column E. If code is "00000.AC.789", place in column H. If code is "00000.AD.123," place in column K.

Before:

ID	Code	Count	Rate
113	00000.AC.456	1519	1671.8
113	00000.AC.123	75	159.2
113	00000.AC.789	432	558.78
630	00000.AC.456	32	362.32
630	00000.AD.123	14	459.62
911	00000.AC.789	63	2253.12

<tbody>
</tbody>

After

A ID	B Code 1	C Count	D Rate	E Code 2	F Count	G Rate	H Code 3	I Count	J Rate	K Code 4	L Count	M Rate
113	00000.AC.123	75	159.2	00000.AC.456	1519	1671.8	00000.AC.789	432	558.78
630				00000.AC.456	32	362.32				00000.AD.123	14	459.62
911							00000.AC.789	63	2253.12

<tbody>
</tbody>

i will let someoe more adept at rearranging arrays tackle that one,
regards, JLG