Rearranging Vertical Data blocks horizontally

[doktorigi]

Hi Guys,

I need some help with a bunch of data blocks that are stacked vertically in excel. I would like to put them adjacent to each other horizontally.

example:

What the Data looks like
AAA
BBB
CCC

AAA
BBB
CCC
DDD

AAA
BBB
CCC
DDD
EEE

What the data has to look like
AAA AAA AAA
BBB BBB BBB
CCC CCC CCC
DDD DDD
EEE

I would appreciate any help. I would like to have a macro that checks the size of the data block creates a range and moves it horizontally to be adjacent to the other data sets Then continue looping the procedure until it reaches the end of the vertical Data blocks.

Thanks in advance.

 

[MickG]

Try this:-
Results start "B1"

[COLOR="Navy"]Sub[/COLOR] MG29Jun11
[COLOR="Navy"]Dim[/COLOR] Rng     [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] n       [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Q
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.count).End(xlUp))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] Dn <> "" [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            n = n + 1
            .Add Dn.Value, Array(n, 2)
            Cells(n, Dn.Offset(, 1).Column) = Dn
        [COLOR="Navy"]Else[/COLOR]
            Q = .Item(Dn.Value)
            Q(1) = Q(1) + 1
            Cells(Q(0), Q(1)).Value = Dn
            .Item(Dn.Value) = Q
        [COLOR="Navy"]End[/COLOR] If
     [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[doktorigi]

Thanks Mick That worked great!

 

[MickG]

Not quite sure why that is , you may have some added spaces in the cells that should be duplicate.
Try adding the line in Red, as below:-

 For Each Dn In Rng
        Dn = Trim(Dn.Value)
      If Dn <> "" Then

Ps:- Just got your new message, Thanks for the feedback

 

[doktorigi]

Sorry Mick I just tried to change a few of the data blocks before I applied it to the raw files and the output changed to something else Column A was changed around alittle.
I need for it to work for any data sets that are stacked they don't really follow a pattern. Any Ideas?


[TABLE="width: 320"]
<colgroup><col style="width:48pt" span="5" width="64"> </colgroup><tbody>[TR]
[TD="width: 64"]AAA
[/TD]
[TD="width: 64"]AAA [/TD]
[TD="width: 64"]AAA [/TD]
[TD="width: 64"]AAA [/TD]
[TD="width: 64"]AAA [/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD]BBB[/TD]
[TD]BBB[/TD]
[TD]BBB[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CCC [/TD]
[TD]CCC [/TD]
[TD]CCC [/TD]
[TD]CCC [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]FFF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]FFF
[/TD]
[TD]EEE[/TD]
[TD]EEE[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EEE[/TD]
[TD]QQQ [/TD]
[TD]QQQ [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AAA [/TD]
[TD]WWW [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]DDD [/TD]
[TD]DDD [/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]QQQ [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WWW
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]QQQ
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AAA [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CCC
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DDD
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AAA [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]CCC
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DDD [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]EEE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]