how to display the values i first column

D

divya g

New Member
Joined
Dec 12, 2018
Messages
4
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]intil[/TD]
[TD]lwp[/TD]
[TD]owp[/TD]
[TD]training[/TD]
[TD]black brink[/TD]
[TD]photo ark[/TD]
[TD]edge fellows[/TD]
[TD]bci[/TD]
[TD]rhino[/TD]
[TD]ocean[/TD]
[TD]arcitic[/TD]
[/TR]
[TR]
[TD]barbara[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]barbara[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]barbara[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]adam[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]adam[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]adam[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]adam[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]intil[/TD]
[TD]lwp[/TD]
[TD]owp[/TD]
[TD]training[/TD]
[TD]black brink[/TD]
[TD]photoark[/TD]
[TD]edge fellows[/TD]
[TD]bci[/TD]
[TD]rhino[/TD]
[TD]ocean[/TD]
[TD]arctic[/TD]
[/TR]
[TR]
[TD]barbara[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[TD]x[/TD]
[/TR]
[TR]
[TD]barbara[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]barbara[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
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.
 
Upvote 0
Try this:-
Code: 
[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
 
Upvote 0
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
[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl63, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
MR_J3NKO said:
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
[TABLE="width: 64"]
<tbody>[TR]
[TD="class: xl63, width: 64"][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

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

[TABLE="width: 768"]
<colgroup><col width="64" span="12" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Name[/TD]
[TD="class: xl65, width: 64"]intil[/TD]
[TD="class: xl65, width: 64"]lwp[/TD]
[TD="class: xl65, width: 64"]owp[/TD]
[TD="class: xl65, width: 64"]training[/TD]
[TD="class: xl65, width: 64"]black brink[/TD]
[TD="class: xl65, width: 64"]photoark[/TD]
[TD="class: xl65, width: 64"]edge fellows[/TD]
[TD="class: xl65, width: 64"]bci[/TD]
[TD="class: xl65, width: 64"]rhino[/TD]
[TD="class: xl65, width: 64"]ocean[/TD]
[TD="class: xl65, width: 64"]arctic[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]barbara[/TD]
[TD="class: xl65, width: 64"]X[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]X[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]X[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]X[/TD]
[TD="class: xl65, width: 64"]X[/TD]
[TD="class: xl65, width: 64"]X[/TD]
[TD="class: xl65, width: 64"]X[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Adam[/TD]
[TD="class: xl65, width: 64"]X[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]X[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]X[/TD]
[TD="class: xl65, width: 64"] [/TD]
[TD="class: xl65, width: 64"]X[/TD]
[TD="class: xl65, width: 64"]X[/TD]
[TD="class: xl65, width: 64"]X[/TD]
[TD="class: xl65, width: 64"]X[/TD]
[TD="class: xl65, width: 64"]X[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Locked
  • Question Question
how to return header value
Replies
2
Views
618
Fluff
Fluff
E
  • Question Question
How many times do column A values appear in B
Replies
3
Views
932
Epyle
E

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top