Consolidating Table

[dpmaki]

I have a table like this:

Number	Trait 1	Trait 2	Trait 3	Trait 4	Trait 5	Trait 6
22	x
13			x
45						x
50	x
22					x
13	x
45
50				x
22						x
13
45		x
50		x

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

I'm trying to come up with some VBA that would consolidate it into something like this:

Number	Trait 1	Trait 2	Trait 3	Trait 4	Trait 5	Trait 6
22	x				x	x
13	x		x
45		x				x
50	x	x		x

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

Any suggestions on getting me started?

 

[MickG]

Try this:-
Results as per your Thread
NB:- This code will alter you data.!!!

[COLOR="Navy"]Sub[/COLOR] MG16Sep51
[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] nRng            [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), 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] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]For[/COLOR] n = 1 To 6
            [COLOR="Navy"]If[/COLOR] Dn.Offset(, n).Value = "x" [COLOR="Navy"]Then[/COLOR] .Item(Dn.Value).Offset(, n).Value = "x"
        [COLOR="Navy"]Next[/COLOR] n
            [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]Set[/COLOR] nRng = Dn
            [COLOR="Navy"]Else[/COLOR]
                [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, Dn)
            [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]If[/COLOR] Not nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR] nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick