Align duplicated values and string another column

[migliozm]

Hi everyone,

I am having trouble aligning multiple columns. For instance, I have this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]12[/TD]
[TD]15[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]17[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]10[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]12[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]16[/TD]
[TD]e[/TD]
[/TR]
</tbody>[/TABLE]

And I need to keep the values in the second and third columns together but align the duplicates in columns 1 and 2 like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]12[/TD]
[TD]12[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]15[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]17[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]16[/TD]
[TD]e[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]10[/TD]
[TD]c[/TD]
[/TR]
</tbody>[/TABLE]

Is there a way to do this??


****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD]12[/TD]
[TD][/TD]
[TD]15[/TD]
[TD]a[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD]17[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD]10[/TD]
[TD]c[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD]12[/TD]
[TD]d[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]16[/TD]
[TD]d[/TD]
[/TR]
</tbody>[/TABLE]

</body>

 

[drognanar]

Try this:

Sub AllignToFirstCol()
Dim WB As Workbook, WS As Worksheet
Dim x As Integer, RW As Integer, y As Integer
Dim alist As Variant
Dim blist As Variant
Dim Chk As Variant 'you can change data type to be more specific to what you have


Set WB = ThisWorkbook
Set WS = WB.Sheets("Sheet2") 'your worksheet name


RW = WS.Range("B3").End(xlDown).Row


ReDim alist(0 To RW - 1)
ReDim blist(0 To RW - 1)


For x = 1 To RW
    alist(x - 1) = WS.Range("B1").Offset(x - 1).Value
    blist(x - 1) = WS.Range("C1").Offset(x - 1).Value
Next x


For x = 1 To RW
    Chk = WS.Range("A1").Offset(x - 1).Value
        For y = 1 To RW
            If alist(y - 1) = Chk Then
            WS.Range("B1").Offset(x - 1).Value = alist(y - 1)
            WS.Range("C1").Offset(x - 1).Value = blist(y - 1)
            Exit For
            End If
        Next y
Next x
End Sub

Hopefully your columns are in A:A,B:B,C:C - and dont forget to change sheet name to what you have