Hello,

I have a list with column j containing job titles, some of which are
repeated. I need to produce another list which displays all the jobs
containing the words "manager" or "supervisor". I want the new list to
display unique job titles (ie. the same job title only appears once in the
new list).

I would like to do this with a formula if possible but I am assuming that I
need an array formula and I'm finding the learning curve a bit steep.

--
Thanks in advance,
MarkN

I hope that this is what you are looking for.

Have a look in the below link...

http://www.contextures.com/xlFunctio...tml#SumProduct

If this post helps, Click Yes!

--------------------
(Ms-Exl-Learner)
--------------------



"MarkN" wrote:

Hello,

I have a list with column j containing job titles, some of which are
repeated. I need to produce another list which displays all the jobs
containing the words "manager" or "supervisor". I want the new list to
display unique job titles (ie. the same job title only appears once in the
new list).

I would like to do this with a formula if possible but I am assuming that I
need an array formula and I'm finding the learning curve a bit steep.

--
Thanks in advance,
MarkN

Hi Mark

If you are interested in a macro try the below which will generate the
unique list with the mentioned criteria as a new sheet next to your data
sheet. Incase you are new to macros

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook. to the sheet with data
--Run macro from Tools|Macro|Run <selected macro()


Sub Macro()

Dim ws As Worksheet, wsTemp As Worksheet, lngRow As Long
Dim lngNRow As Long, varFound As Range

Set ws = ActiveSheet: Set wsTemp = Worksheets.Add(After:=ws)
For lngRow = 1 To ws.Cells(Rows.Count, "J").End(xlUp).Row
If InStr(1, ws.Range("J" & lngRow), "manager", vbTextCompare) + _
InStr(1, ws.Range("J" & lngRow), "supervisor", vbTextCompare) 0 Then
Set varFound = wsTemp.Columns(1).Find(ws.Range("j" & lngRow), _
LookIn:=xlValues, lookat:=xlWhole)
If varFound Is Nothing Then
lngNRow = lngNRow + 1
wsTemp.Range("A" & lngNRow) = ws.Range("J" & lngRow)
End If
End If
Next

End Sub



If this post helps click Yes
---------------
Jacob Skaria


"MarkN" wrote:

Hello,

I have a list with column j containing job titles, some of which are
repeated. I need to produce another list which displays all the jobs
containing the words "manager" or "supervisor". I want the new list to
display unique job titles (ie. the same job title only appears once in the
new list).

I would like to do this with a formula if possible but I am assuming that I
need an array formula and I'm finding the learning curve a bit steep.

--
Thanks in advance,
MarkN

Thanks once again Jacob, works perfectly. If I want this list to go to start
at a specific place on a worksheet that I already have am I better copying
the results on the temp sheet, pasting into the location I want it, then
deleting the temp sheet or can this be done by coding the macro to place the
results on the sheet where I want the result?

--
Thanks very much,
MarkN


"Jacob Skaria" wrote:

Hi Mark

If you are interested in a macro try the below which will generate the
unique list with the mentioned criteria as a new sheet next to your data
sheet. Incase you are new to macros

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook. to the sheet with data
--Run macro from Tools|Macro|Run <selected macro()


Sub Macro()

Dim ws As Worksheet, wsTemp As Worksheet, lngRow As Long
Dim lngNRow As Long, varFound As Range

Set ws = ActiveSheet: Set wsTemp = Worksheets.Add(After:=ws)
For lngRow = 1 To ws.Cells(Rows.Count, "J").End(xlUp).Row
If InStr(1, ws.Range("J" & lngRow), "manager", vbTextCompare) + _
InStr(1, ws.Range("J" & lngRow), "supervisor", vbTextCompare) 0 Then
Set varFound = wsTemp.Columns(1).Find(ws.Range("j" & lngRow), _
LookIn:=xlValues, lookat:=xlWhole)
If varFound Is Nothing Then
lngNRow = lngNRow + 1
wsTemp.Range("A" & lngNRow) = ws.Range("J" & lngRow)
End If
End If
Next

End Sub



If this post helps click Yes
---------------
Jacob Skaria


"MarkN" wrote:

Hello,

I have a list with column j containing job titles, some of which are
repeated. I need to produce another list which displays all the jobs
containing the words "manager" or "supervisor". I want the new list to
display unique job titles (ie. the same job title only appears once in the
new list).

I would like to do this with a formula if possible but I am assuming that I
need an array formula and I'm finding the learning curve a bit steep.

--
Thanks in advance,
MarkN

Thanks for the feedback. Try the below modified one. Edit the target sheet
name and range. Sheet2.Range("M2:M100"). To suit. Make sure you have a bigger
range given so as to accomodate all unique values.

Sub Macro()
Dim ws As Worksheet, rngTemp As Range, lngRow As Long
Dim lngNRow As Long, varFound As Range

Set ws = ActiveSheet
Set rngTemp = Worksheets("Sheet2").Range("M2:M100")
rngTemp.ClearContents
For lngRow = 1 To ws.Cells(Rows.Count, "J").End(xlUp).Row
If InStr(1, ws.Range("J" & lngRow), "manager", vbTextCompare) + _
InStr(1, ws.Range("J" & lngRow), "supervisor", vbTextCompare) 0 Then
Set varFound = rngTemp.Find(ws.Range("j" & lngRow), , xlValues, 1)
If varFound Is Nothing Then
lngNRow = lngNRow + 1
rngTemp(lngNRow) = ws.Range("J" & lngRow)
End If
End If
Next

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"MarkN" wrote:

Thanks once again Jacob, works perfectly. If I want this list to go to start
at a specific place on a worksheet that I already have am I better copying
the results on the temp sheet, pasting into the location I want it, then
deleting the temp sheet or can this be done by coding the macro to place the
results on the sheet where I want the result?

--
Thanks very much,
MarkN


"Jacob Skaria" wrote:

Hi Mark

If you are interested in a macro try the below which will generate the
unique list with the mentioned criteria as a new sheet next to your data
sheet. Incase you are new to macros

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook. to the sheet with data
--Run macro from Tools|Macro|Run <selected macro()


Sub Macro()

Dim ws As Worksheet, wsTemp As Worksheet, lngRow As Long
Dim lngNRow As Long, varFound As Range

Set ws = ActiveSheet: Set wsTemp = Worksheets.Add(After:=ws)
For lngRow = 1 To ws.Cells(Rows.Count, "J").End(xlUp).Row
If InStr(1, ws.Range("J" & lngRow), "manager", vbTextCompare) + _
InStr(1, ws.Range("J" & lngRow), "supervisor", vbTextCompare) 0 Then
Set varFound = wsTemp.Columns(1).Find(ws.Range("j" & lngRow), _
LookIn:=xlValues, lookat:=xlWhole)
If varFound Is Nothing Then
lngNRow = lngNRow + 1
wsTemp.Range("A" & lngNRow) = ws.Range("J" & lngRow)
End If
End If
Next

End Sub



If this post helps click Yes
---------------
Jacob Skaria


"MarkN" wrote:

Hello,

I have a list with column j containing job titles, some of which are
repeated. I need to produce another list which displays all the jobs
containing the words "manager" or "supervisor". I want the new list to
display unique job titles (ie. the same job title only appears once in the
new list).

I would like to do this with a formula if possible but I am assuming that I
need an array formula and I'm finding the learning curve a bit steep.

--
Thanks in advance,
MarkN

I don't know what you need to get a 1 next to your name but you can't be far
away. Much appreciated.
--
Thanks,
MarkN


"Jacob Skaria" wrote:

Thanks for the feedback. Try the below modified one. Edit the target sheet
name and range. Sheet2.Range("M2:M100"). To suit. Make sure you have a bigger
range given so as to accomodate all unique values.

Sub Macro()
Dim ws As Worksheet, rngTemp As Range, lngRow As Long
Dim lngNRow As Long, varFound As Range

Set ws = ActiveSheet
Set rngTemp = Worksheets("Sheet2").Range("M2:M100")
rngTemp.ClearContents
For lngRow = 1 To ws.Cells(Rows.Count, "J").End(xlUp).Row
If InStr(1, ws.Range("J" & lngRow), "manager", vbTextCompare) + _
InStr(1, ws.Range("J" & lngRow), "supervisor", vbTextCompare) 0 Then
Set varFound = rngTemp.Find(ws.Range("j" & lngRow), , xlValues, 1)
If varFound Is Nothing Then
lngNRow = lngNRow + 1
rngTemp(lngNRow) = ws.Range("J" & lngRow)
End If
End If
Next

End Sub

If this post helps click Yes
---------------
Jacob Skaria


"MarkN" wrote:

Thanks once again Jacob, works perfectly. If I want this list to go to start
at a specific place on a worksheet that I already have am I better copying
the results on the temp sheet, pasting into the location I want it, then
deleting the temp sheet or can this be done by coding the macro to place the
results on the sheet where I want the result?

--
Thanks very much,
MarkN


"Jacob Skaria" wrote:

Hi Mark

If you are interested in a macro try the below which will generate the
unique list with the mentioned criteria as a new sheet next to your data
sheet. Incase you are new to macros

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook. to the sheet with data
--Run macro from Tools|Macro|Run <selected macro()


Sub Macro()

Dim ws As Worksheet, wsTemp As Worksheet, lngRow As Long
Dim lngNRow As Long, varFound As Range

Set ws = ActiveSheet: Set wsTemp = Worksheets.Add(After:=ws)
For lngRow = 1 To ws.Cells(Rows.Count, "J").End(xlUp).Row
If InStr(1, ws.Range("J" & lngRow), "manager", vbTextCompare) + _
InStr(1, ws.Range("J" & lngRow), "supervisor", vbTextCompare) 0 Then
Set varFound = wsTemp.Columns(1).Find(ws.Range("j" & lngRow), _
LookIn:=xlValues, lookat:=xlWhole)
If varFound Is Nothing Then
lngNRow = lngNRow + 1
wsTemp.Range("A" & lngNRow) = ws.Range("J" & lngRow)
End If
End If
Next

End Sub



If this post helps click Yes
---------------
Jacob Skaria


"MarkN" wrote:

Hello,

I have a list with column j containing job titles, some of which are
repeated. I need to produce another list which displays all the jobs
containing the words "manager" or "supervisor". I want the new list to
display unique job titles (ie. the same job title only appears once in the
new list).

I would like to do this with a formula if possible but I am assuming that I
need an array formula and I'm finding the learning curve a bit steep.

--
Thanks in advance,
MarkN