Consolidating Table

dpmaki

Board Regular
Joined
Sep 12, 2011
Messages
165
I have a table like this:

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

<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:

NumberTrait 1Trait 2Trait 3Trait 4Trait 5Trait 6
22xxx
13xx
45xx
50xxx


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

Any suggestions on getting me started?
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this:-
Results as per your Thread
NB:- This code will alter you data.!!!
Code:
[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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,331
Messages
5,528,052
Members
409,800
Latest member
camronmartin

This Week's Hot Topics

Top