How to extract Right Titles

[niladri20052006]

Hi All,

I have a set of data where there are innumerable titles like below..Everyday day I work the same thing. I try to find first Network titles from my list then information technology Director then information technology Manager, information technology Executive and then any titles related with Systems.

Network Administrator
Software Engineer
Software Architect
Information Technology Director
Information Technology Manager
Chief Information Officer
Channel Manager
Account manger
Systems Executive

I want the list where the network title should be then Information technology and then Systems.

I am doing it applying contains from Filter tab. It works but takes a long time.

I have also added custom option in Sort function. It is not working.

As I am doing the same thing I created a micro but after running the data is completely mixed up.

Is there any formula can help me to get it done in a hassle free way?

Thanks in advance

 

[markmzz]

Hi Markmzz,

Thanks for reply. you are working hard for me.

the micro is working fine. here also the titles are coming up in a mixed up way.

I want the tiles "Network" first. Thank all the network titles should be in first position. Then the title related with Information technology will begin.

After information technology there will be no network titles as we have already taken it first.

I think you have got my point what i need.

Kudos to you!!

I appreciate your hardworking.

Thanks in advance!

Niladri Sekhar Biswas

Sorry, but I didn't understand. The macro work or not?

Here all is ok, look at this:

<TABLE style="WIDTH: 486pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=648><COLGROUP><COL style="WIDTH: 194pt; mso-width-source: userset; mso-width-alt: 9472" width=259><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" width=85><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 167pt; mso-width-source: userset; mso-width-alt: 8155" width=223><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 194pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=259>COMPANY_NAME</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 64pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=85>FIRST_NAME</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 61pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=81>LAST_NAME</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 167pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=223>TITLE</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Belcan Corporation</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Donald</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Berberich</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Network Manager</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Madeira Jr</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Becky</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Reisert</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Network Manager</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Unifund</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Dustin Dusty</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Nichols</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Network Engineer</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>totes Isotoner Corporation</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Erick</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Stropes</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Network Administrator</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>The Fechheimer Brothers Company, Inc.</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>David</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Casteel</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Network Administrator</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>The Health Alliance</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Michael</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Kincaid</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Network Administrator</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Frost Brown Todd LLC</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Rick</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Humphrey</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Information Technology Manager</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>The Wornick Co.</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Jason</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Morgan</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Information Technology Manager</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Sunny Delight Beverages Co.</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Steve</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Verret</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Information Technology Manager</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20>Xomox Corporation</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Dan</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Ridge</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Information Technology Executive</TD></TR></TBODY></TABLE>

Markmzz

 

[niladri20052006]

Hi Markmz,

Thanks again...

Yes this is list I am trying to get. I want exactly the same what you have shown above.

But how have done this trick? Have you sorted it? or there is macro.

if you are doing it with macro then please send me the code..

Waiting your reply...

Thanks in advance...

Niladri Sekhar Biswas

 

[markmzz]

Hi Markmz,

Thanks again...

Yes this is list I am trying to get. I want exactly the same what you have shown above.

But how have done this trick? Have you sorted it? or there is macro.

if you are doing it with macro then please send me the code..

Waiting your reply...

Thanks in advance...

Niladri Sekhar Biswas

Hi Niladri20052006,

The macros of the post #7 and post #9 are differents.

Try this macro of the post #9:

[COLOR=blue]Option Explicit[/COLOR]
[COLOR=blue]Sub RightTitles()[/COLOR]
[COLOR=blue]   '=============================================================[/COLOR]
[COLOR=blue]   'IMPORTANT - THE SHEET Sheet12 WILL BE DELETED WITH THIS MACRO[/COLOR]
[COLOR=blue]   '=============================================================[/COLOR]
[COLOR=blue]   '[/COLOR]
[COLOR=blue]   Dim LastRow, LastCol As Long[/COLOR]
[COLOR=blue]   Application.ScreenUpdating = False[/COLOR]
 
[COLOR=blue]   Sheets("Sheet1").Select[/COLOR]
[COLOR=blue]   LastCol = Cells(1, Columns.Count).End(xlToLeft).Column[/COLOR]
[COLOR=blue]   LastRow = Cells(Rows.Count, 1).End(xlUp).Row[/COLOR]
 
[COLOR=blue]   Application.DisplayAlerts = False[/COLOR]
[COLOR=blue]   On Error Resume Next[/COLOR]
[COLOR=blue]   Sheets("Sheet12").Delete[/COLOR]
[COLOR=blue]   On Error GoTo 0[/COLOR]
[COLOR=blue]   Application.DisplayAlerts = True[/COLOR]
 
[COLOR=blue]   Sheets.Add.Name = "Sheet12"[/COLOR]
 
[COLOR=blue]   Cells(1, LastCol + 2).Value = "TITLE"[/COLOR]
[COLOR=blue]   Cells(2, LastCol + 2).Value = "network"[/COLOR]
[COLOR=blue]   Cells(3, LastCol + 2).Value = "Information technology"[/COLOR]
 
[COLOR=blue]   Sheets("Sheet1").Range(Cells(1, 1).Address, Cells(LastRow, LastCol).Address). _[/COLOR]
[COLOR=blue]       AdvancedFilter _[/COLOR]
[COLOR=blue]           Action:=xlFilterCopy, _[/COLOR]
[COLOR=blue]           CriteriaRange:=Range(Cells(1, LastCol + 2), Cells(3, LastCol + 2)), _[/COLOR]
[COLOR=blue]           CopyToRange:=Cells(1, 1)[/COLOR]
[COLOR=blue]   Cells(1, LastCol + 2).EntireColumn.Delete[/COLOR]
[COLOR=blue]   LastCol = Cells(1, Columns.Count).End(xlToLeft).Column[/COLOR]
[COLOR=blue]   Range(Cells(1, 1), Cells(1, LastCol)).EntireColumn.AutoFit[/COLOR]
 
[COLOR=blue][COLOR=red]   Range(Cells(1, 1), Cells(LastRow, LastCol)).Sort _[/COLOR]
[COLOR=blue]       Key1:=Cells(1, LastCol), _[/COLOR]
[COLOR=blue]       Order1:=xlDescending[/COLOR]
        
    Application.ScreenUpdating = True
End Sub[/COLOR]

Markmzz

 

[niladri20052006]

Hi Markmzz,

This is exactly what I wanted.

Thanks a lot.

I appreciate your hardworking...

One thing more can you please suggest me any tutorial or site from where I can learn VBA.


Again Thanks a lot...

 

[markmzz]

Hi Markmzz,

This is exactly what I wanted.

Thanks a lot.

I appreciate your hardworking...

One thing more can you please suggest me any tutorial or site from where I can learn VBA.


Again Thanks a lot...

Hi Niladri20052006,

First, I'm happy that your problem was solved.

Second, I like the book Microsoft Office Excel 2007 Visual Basic for Applications Step by Step.

Finally, thank you for the feedback and until the next formula/code.

Markmzz