how to display the values i first column

[divya g]

	intil	lwp	owp	training	black brink	photo ark	edge fellows	bci	rhino	ocean	arcitic
barbara	x					x		x
barbara				x		x			x		x
barbara	x					x				x
adam	x				x			x			x
adam			x				x		x
adam	x				x					x
adam	x										x

<tbody>
</tbody>

and here my question is i need to display all x in a one column and names have duplicates where ever x is there i have to display top most



expected table

	intil	lwp	owp	training	black brink	photoark	edge fellows	bci	rhino	ocean	arctic
barbara	x			x		x		x	x	x	x
barbara
barbara

<tbody>
</tbody>

 

[Special-K99]

Why does "barbara" appear 3 times in your output? Why isn't she just there once? The remaining two lines for her are blank and provide no information whatsoever since it's all on the first line.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG18Dec32
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Lst [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A" & Rows.Count).End(xlUp))
Lst = Cells("1", Columns.Count).End(xlToLeft).Column
[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] Ac = 1 To Lst
            [COLOR="Navy"]If[/COLOR] Not IsEmpty(Dn.Offset(, Ac).Value) [COLOR="Navy"]Then[/COLOR]
                .Item(Dn.Value).Offset(, Ac) = Dn.Offset(, Ac)
                Dn.Offset(, Ac).ClearContents
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MR_J3NKO]

Hi,

Here is a solution for you:

In a new sheet, create a list of names, no replications as per your expected table above. In cell B2 paste:

=IF(COUNTIFS(Old!$A$2:$A$9,New!$A2,Old!B$2:B$9,"X")>0,"X","")

The old range should cover your current table range from top to bottom (EXCLUDING HEADER ROW!)

Try that and shout if you have any problems

Thanks
AJ

<tbody>
</tbody>

 

[MR_J3NKO]

Hi,

Here is a solution for you:

In a new sheet, create a list of names, no replications as per your expected table above. In cell B2 paste:

=IF(COUNTIFS(Old!$A$2:$A$9,New!$A2,Old!B$2:B$9,"X")>0,"X","")

The old range should cover your current table range from top to bottom (EXCLUDING HEADER ROW!)

Try that and shout if you have any problems

Thanks
AJ

<tbody>
</tbody>

I should add you can drag/copy that formula, left to right and top to bottom to get a single line result for each name

Name	intil	lwp	owp	training	black brink	photoark	edge fellows	bci	rhino	ocean	arctic
barbara	X			X		X		X	X	X	X
Adam	X		X		X		X	X	X	X	X

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

 

[divya g]

thank you that is working fine