How to extract Right Titles

niladri20052006

Board Regular
Joined
Dec 3, 2010
Messages
121
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
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
niladri20052006,

What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples (what you have and what you expect to achieve) directly in the forum.

To attach screenshots, see below in my Signature block: Post a screen shot with one of these:
 
Upvote 0
niladri20052006,

Thanks for the workbook.


This is all that is in the workbook:


Excel Workbook
ABCD
1COMPANY_NAMEFIRST_NAMELAST_NAMETITLE
2totes Isotoner CorporationErickStropesNetwork Administrator
3Belcan CorporationDonaldBerberichNetwork Manager
4The Fechheimer Brothers Company, Inc.DavidCasteelNetwork Administrator
5UnifundDustin DustyNicholsNetwork Engineer
6Champion Window Manufacturing Inc.GeorgepriceDirector Of Information Technology
7United Pet Group IncDanielSyvertsenDirector Of Information Technology
8Xomox CorporationDanRidgeInformation Technology Executive
9Frost Brown Todd LLCRickHumphreyInformation Technology Manager
10The Wornick Co.JasonMorganInformation Technology Manager
11The Health AllianceMichaelKincaidNetwork Administrator
12Madeira JrBeckyReisertNetwork Manager
13Talbert HousePeterJollisDirector Of Information Technology
14Sunny Delight Beverages Co.SteveVerretInformation Technology Manager
15Horan Associates IncGregKroegerSystems Adminstrator
16United Dairy Farmers, Inc.JohnOsborneDirector - Security
17The Western and Southern Life Insurance CompanyDaveWoodTechnology Manager
18Schiff,kreidler-ShellBradCooleySystems Adminstrator
19
Sheet1





Can we have another workbook, with an additional worksheet manually formatted by you with the results you are looking for?
 
Upvote 0
Hi Hiker95,

Thanks for your reply...

I want all the info where only network contacts and Information technology available in the list using formula. I can do it using filter then contains (network) or Information technology

Is it possible to do it with micro or by using formula?

I want to get in a different workbook or in the same workbook but in a different sheet.

I am sure I am capable enough to make you understand what I need.


Please revert for any clarification...

Thanks,

Niladri
 
Upvote 0
niladri20052006,

So that we can get it right the first time, can we have another workbook, with Sheet1 as displayed in my Reply #4, and with the additional worksheets Network and Information Technology manually formatted by you with the results you are looking for?
 
Upvote 0
Niladri20052006,

Try this code (the name of the sheet with data is Sheet1):

NOTE1: USE THIS MACRO WITH A COPY OF YOUR WORKBOOK.

NOTE2: THE SHEET Sheet12 WILL BE DELETED WITH THIS MACRO.

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

Markmzz
 
Upvote 0
Hi Markmzz,

Thanks for your reply.

The code is working fine. I am getting my result...

The result is coming where it is getting network and Information technology titles. I also want the same.But I want the network titles will come first then "Information Technology"

but is there any way to get "Network" titles first then Information Technology?

Thanks in Advance!

Waiting for your reply..
 
Upvote 0
Niladri20052006,

Try this:

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

Markmzz
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,591
Messages
6,179,766
Members
452,940
Latest member
rootytrip

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