vlookup, index/match or something else

Posted by Ian on July 17, 2001 10:52 AM

Hi All

i'll try and keep it simple
I have list of names in column a: and job titles in column b: on a master sheet!
on sheet2! i want to return all the people that are "agency" in order a1 a2 a3 etc. and the same on sheet2! for "perm".
how do i return the lists without blanks onto the 2 sheets.

Master sheet!

a b
ian perm
jo perm
steve agency
linda perm
brian agency
lisa agency

Sheet2!

a:
ian
jo
linda

Sheet3!

a:
steve
brain
lisa

all return 1 under the other,

thanks

Ian

Posted by Russell on July 17, 2001 11:05 AM

The way I would do it is in code (I used dashes for indenting - you can do a find replace for "-", replace with " "):

Sub Separate()

Dim intRow as Integer
Dim intJobCol as Integer
Dim intPermRow as Integer
Dim intAgencyRow as Integer

Sheets("Master").Select

intRow = 1
intPermRow = 1
intAgencyRow = 1
intCol = 2

Do While Trim(Cells(intRow, intCol).Text) &LT;&GT; ""

----Select Case Trim(Cells(intRow, intCol).Text)
----Case "perm"

--------Sheets("Sheet2").Cells(intPermRow, 1).Value = _
--------Sheets("Master").Cells(intRow, intCol).Value
--------intPermRow = intPermRow + 1

----Case "agency"

--------Sheets("Sheet2").Cells(intAgencyRow, 1).Value = _
--------Sheets("Master").Cells(intRow, intCol).Value
--------intAgencyRow = intAgencyRow + 1

----Case Else
----' Do nothing?

----End Select

----intRow = intRow + 1

Loop

End Sub

Hope this helps,

Russell

Posted by IML on July 17, 2001 11:31 AM

Cheezy Way

If you can afford a hidden column, you could put this in column C on Master and copy down.
=B1&COUNTIF($B$1:B1,B1)

To list your perms, you could use
=IF(ROW()&LT;=COUNTIF('Master Sheet'!$B$1:$B$6,"perm"),INDEX('Master Sheet'!A1:C6,MATCH("perm"&ROW(),'Master Sheet'!C1:C6,0),1),"")
and copy it down

Same formula for agency substituting your key word
=IF(ROW()&LT;=COUNTIF('Master Sheet'!$B$1:$B$6,"agency"),INDEX('Master Sheet'!A1:C6,MATCH("agency"&ROW(),'Master Sheet'!C1:C6,0),1),"")

Good luck.

Posted by Ian on July 17, 2001 12:01 PM

Thanks, will test these tomorrow.

Posted by Ian on July 17, 2001 3:03 PM

not sure how to get this to work?

it all look strange to me (having JUST start learning VB), if possible could you add comment as to what/why things are doing. Do I need to do anything on the sheet (field names, defining names, etc.)

thanks Ian