sorting of data in excel in a column

R

rupashree

New Member
Joined
Jun 16, 2011
Messages
21
hi...i have data with duplicates like

<table width="238" border="0" cellpadding="0" cellspacing="0"><col style="width: 179pt;" width="238"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; width: 179pt;" width="238" height="17">BM1001177_SPMP907817_LD</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">BM1001177_SPMP907817_EN</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">BM1001177_SPMP907817_TM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
BM1001939_SPMP907641_LD</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">BM1001939_SPMP907641_EN</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">BM1001939_SPMP907641_TM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
BM1001556_SPMP907642_LD</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">BM1001556_SPMP907642_EN

the rows with duplicate data ending with _LD,_EN,_TM SHOULD BE COLOURED.when all the three _LD,_EN,_TM come it should be marked....rest should not be marked.
</td> </tr> </tbody></table>please help me..............:)
<table width="238" border="0" cellpadding="0" cellspacing="0"><tbody></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
rupashree said:
hi...i have data with duplicates like

<table width="238" border="0" cellpadding="0" cellspacing="0"><col style="width: 179pt;" width="238"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt; width: 179pt;" width="238" height="17">BM1001177_SPMP907817_LD</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">BM1001177_SPMP907817_EN</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl22" style="height: 12.75pt;" height="17">BM1001177_SPMP907817_TM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">
BM1001939_SPMP907641_LD</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">BM1001939_SPMP907641_EN</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl23" style="height: 12.75pt;" height="17">BM1001939_SPMP907641_TM</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
BM1001556_SPMP907642_LD</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">BM1001556_SPMP907642_EN

the rows with duplicate data ending with _LD,_EN,_TM SHOULD BE COLOURED.when all the three _LD,_EN,_TM come it should be marked....rest should not be marked.
</td> </tr> </tbody></table>please help me..............:)
<table width="238" border="0" cellpadding="0" cellspacing="0"><tbody></tbody></table>
Click to expand...

please give me solution for this problem....:mad:
 
Upvote 0
use conditional formatting to change the cell color or something when the cell contains "_LD" etc.
 
Upvote 0
Try this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG20Jun44
[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] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rw [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Ray
[COLOR="Navy"]Dim[/COLOR] C [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] oTm [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
    Ray = Array("LD", "EN", "TM")
        [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    oTm = Left(Dn, Len(Dn) - 2)
    [COLOR="Navy"]If[/COLOR] Not .Exists(oTm) [COLOR="Navy"]Then[/COLOR]
        .Add oTm, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] .Item(oTm) = Union(.Item(oTm), Dn)
            [COLOR="Navy"]If[/COLOR] .Item(oTm).Count = 3 [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Rw [COLOR="Navy"]In[/COLOR] .Item(oTm)
                    [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Ray)
                        [COLOR="Navy"]If[/COLOR] Ray(n) = Right(Rw, 2) [COLOR="Navy"]Then[/COLOR]
                            C = C + 1
                        [COLOR="Navy"]End[/COLOR] If
                    [COLOR="Navy"]Next[/COLOR] n
                [COLOR="Navy"]Next[/COLOR] Rw
                    col = IIf(col = 34, 35, 34)
                    [COLOR="Navy"]If[/COLOR] C = 3 [COLOR="Navy"]Then[/COLOR] .Item(oTm).Interior.ColorIndex = col
                    C = 0
            [COLOR="Navy"]End[/COLOR] If
    [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
MickG said:
Try this:-
Code: 
[COLOR=Navy]Sub[/COLOR] MG20Jun44
[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] nRng [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Rw [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Ray
[COLOR=Navy]Dim[/COLOR] C [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer[/COLOR]
[COLOR=Navy]Dim[/COLOR] oTm [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] col [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
    Ray = Array("LD", "EN", "TM")
        [COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
            .CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    oTm = Left(Dn, Len(Dn) - 2)
    [COLOR=Navy]If[/COLOR] Not .Exists(oTm) [COLOR=Navy]Then[/COLOR]
        .Add oTm, Dn
    [COLOR=Navy]Else[/COLOR]
        [COLOR=Navy]Set[/COLOR] .Item(oTm) = Union(.Item(oTm), Dn)
            [COLOR=Navy]If[/COLOR] .Item(oTm).Count = 3 [COLOR=Navy]Then[/COLOR]
                [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Rw [COLOR=Navy]In[/COLOR] .Item(oTm)
                    [COLOR=Navy]For[/COLOR] n = 0 To UBound(Ray)
                        [COLOR=Navy]If[/COLOR] Ray(n) = Right(Rw, 2) [COLOR=Navy]Then[/COLOR]
                            C = C + 1
                        [COLOR=Navy]End[/COLOR] If
                    [COLOR=Navy]Next[/COLOR] n
                [COLOR=Navy]Next[/COLOR] Rw
                    col = IIf(col = 34, 35, 34)
                    [COLOR=Navy]If[/COLOR] C = 3 [COLOR=Navy]Then[/COLOR] .Item(oTm).Interior.ColorIndex = col
                    C = 0
            [COLOR=Navy]End[/COLOR] If
    [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
Click to expand...



sir thanks a lot ........u really helped me
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Problems Encountered When Generating VBA Excel Charts with 15-Minute Interval Data.
Replies
0
Views
364
dilshod_k
D
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
B
  • Question Question
Webscrapping Loop to get table data
Replies
1
Views
678
John_w
J
T
  • Question Question
How can I make queryselector to properly loop through a range of cells and get results from a webpage
Replies
1
Views
2K
Worf
Worf

Forum statistics

Threads
1,224,531
Messages
6,179,379
Members
452,907
Latest member
Roland Deschain

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