problems with displaying "duplicate vlookup values" in same column

[Guest]

Can someone please help me tweak my vlookup formulas so that the 'Client-Aide
Schedule'! sheet can display all the employees associated with a Case-Id in
'Employee Roster'! sheet that has more than 1 employee? Below is some random
data...please note that the current formulas only capture the first occurene
ie., 00057, 00058 when they are in the same lookup column. Below are the
Sheets in Question:

'JNNR Client Roster '!
CASE-ID LASTNAME
00001 TANNENBAUM
00002 SHELTON
00003 COOK
00007 CUSTEAU
00008 SINGER
00012 KREDA
00013 WAXMAN
00018 SIMON
00020 KATSKEE
00026 KELLER
00028 JOHNSTON
00031 GROSS
00040 WEIGNER
00042 SELIGMAN
00043 BREM
00048 KATZ
00049 LASHIN
00051 KOHN
00052 COHEN
00054 COOPER
00055 GOFF
00056 BOAM
00057 FARRACI
00058 GORDON
00059 SPENCE
00060 GINSBERG
00061 SCHRAG
00077 JOHNSON
99999 BLACKMAN
X1 KATZ
X2 MARDER
X3 REINDERS
X4 REISCHER
X5 GLASS

'Employee Roster'!
CASE-ID CASE-ID CASE-ID LASTNAME
00007 00043 00058 Stewart
00056 00055 00058 Thomas
00008 00042 Guerrero
00057 00059 Brinson
00001 00060 Distant
00042 00061 Lopez
00051 00061 Nievas
00061 X4 Plummer
00002 Toussaint
00003 LASTN2
00007 Jeremie
00008 San Julian
00012 Burgess
00013 Joseph
00018 Andrews
00020 Frizarim
00026 Roca
00028 Harris
00031 Burneth
00040 Cole
00040 Lozano
00043 Jean Jacques
00048 Atkinson
00049 Sparrow
00052 Williams
00054 Campbell
00055 Malva
00057 Bravo
00057 Hemmings
00057 Martinez
00058 Dalton
99999 Barbot
99999 Walker
X1 Lorissaint
X1 Moncur
X2 Londono
X3 Chantze
X5 LASTN


results w/current vlookup formulas
EMP NAME1 {=IF(ISERROR(VLOOKUP(J6,'Employee
Roster'!B$5:E$50,4,FALSE)),"",VLOOKUP(J6,'Employee Roster'!B$5:E$50,4,FALSE))}
EMP NAME2 {=IF(ISERROR(VLOOKUP(J6,'Employee
Roster'!C$5:E$50,3,FALSE)),"",VLOOKUP(J6,'Employee Roster'!C$5:E$50,3,FALSE))}
EMP NAME3 {=IF(ISERROR(VLOOKUP(J6,'Employee
Roster'!D$5:E$50,2,FALSE)),"",VLOOKUP(J6,'Employee Roster'!D$5:E$50,2,FALSE))}
'Client-Aide Schedule'!
CASE-ID CLIENT NAME EMP NAME1 EMP NAME2 EMP NAME3 EMP NAME4
00001 TANNENBAUM Distant
00002 SHELTON Toussaint
00003 COOK LASTN2
00007 CUSTEAU Stewart
00008 SINGER Guerrero
00012 KREDA Burgess
00013 WAXMAN Joseph
00018 SIMON Andrews
00020 KATSKEE Frizarim
00026 KELLER Roca
00028 JOHNSTON Harris
00031 GROSS Burneth
00040 WEIGNER Cole
00042 SELIGMAN Lopez Guerrero
00043 BREM Jean Jacques Stewart
00048 KATZ Atkinson
00049 LASHIN Sparrow
00051 KOHN Nievas
00052 COHEN Williams
00054 COOPER Campbell
00055 GOFF Malva Thomas
00056 BOAM Thomas
00057 FARRACI Brinson
00058 GORDON Dalton Stewart
00059 SPENCE Brinson
00060 GINSBERG Distant
00061 SCHRAG Plummer Lopez
00077 JOHNSON
99999 BLACKMAN Barbot
X1 KATZ Lorissaint
X2 MARDER Londono
X3 REINDERS Chantze
X4 REISCHER Plummer
X5 GLASS LASTN

 

[Guest]

VLOOKUP will only return one (first ) value so I offer an alternative VBA
solution which requires the "Employee Roster" to have only two columns
(CASE_ID,LASTNAME) sorted by CASE_ID.

CASE-ID LASTNAME
1 Distant
2 Toussaint
3 LASTN2
7 Jeremie
7 Stewart
8 Guerrero
8 San Julian
12 Burgess
13 Joseph
18 Andrews
20 Frizarim
26 Roca
28 Harris
31 Burneth
40 Cole
40 Lozano
42 Guerrero
42 Lopez
43 Jean Jacques
43 Stewart
48 Atkinson
49 Sparrow
51 Nievas
52 Williams
54 Campbell
55 Malva
55 Thomas
56 Thomas
57 Bravo
57 Brinson
57 Hemmings
57 Martinez
58 Dalton
58 Stewart
58 Thomas
59 Brinson
60 Distant
61 Lopez
61 Nievas
61 Plummer
99999 Barbot
99999 Walker
X1 Lorissaint
X1 Moncur
X2 Londono
X3 Chantze
X4 Plummer
X5 LASTN

The VBA routine which should be put in a general module:

Sub ABC()

Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet

Set ws1 = Worksheets("JNNR Client Roster")
Set ws2 = Worksheets("Employee Roster")
Set ws3 = Worksheets("Client- Aide Schedule")

Dim lstrow As Long, r As Long
Dim res As Variant

With ws3
' Find last row of "Client-Aide Schedule"
lastrow = .Cells(Rows.Count, "A").End(xlUp).row
For r = 2 To lastrow
' Get Client name
res = Application.VLookup(.Cells(r, "A"), ws1.Range("A:B"), 2, 0)
' Client Name found ......
If Not IsError(res) Then
ws3.Cells(r, "B") = res
End If
' Find first record for this CASE-ID
res = Application.Match(.Cells(r, "A"), ws2.Range("A:A"), 0)
' Match found ......
If Not IsError(res) Then
' Count records for this CASE_ID
n = Application.CountIf(ws2.Range("A:A"), Cells(r, "A"))
' Copy Names (from column B) and transpose to row
ws2.Cells(res, "B").Resize(n, 1).Copy
ws3.Cells(r, "C").PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End If
Next r
End With
End Sub

HTH

 

[Guest]

Dear Mr. Toppers,

I created the sheet with CASE-ID, LASTNAME as you suggested, however when I
pasted your code into a module I received a syntax error at

Operation:=xlNone, SkipBlanks:= _

Since I am a not very familiar with VBA, I do not know how to proceed.

In closing I would like to thank you for your help and would appreciate if
you could recheck the code so that I can try to implement it into my
spreadsheet.

Thanks Again
Pcast

 

[Guest]

This should be one line (but has appeared as two due to "line wrapping" on
the NG)

ws3.Cells(r, "C").PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, SkipBlanks:= _

Alternatively copy this as TWO lines: the "_" at end of the line is a
continuation mark

ws3.Cells(r, "C").PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, SkipBlanks:= _

HTH

 

[Guest]

Dear mr Toppers,

made the adjustment however, I now get the following error:
Set ws1 = Worksheets("AHN Client Roster")
Run-time error '9': subscript out of range

any additional help wuld be much appreciated...sorry for any inconvenience

pcast

 

[Guest]

Check (all) worksheet name(s) in the code. This all worked correctly for me
when I tested it.

 

[Guest]

dear Toppers,

your code works greeat but would like to ask if you could modify the code so
that it removes duplicate transposed rows and creates headers like EMPL_1
EMPL_2 EMPL_3 ... based on the CASE_ID with the most employees.

Any help would be greatly appreciated.

Thank you again for sharing your talent.

pcast

 

[Guest]

Why are there duplicates? - which sheet ? -- Client roster and/or Employee
Roster? Please post example(s).

Code below varies the heading .....

Sub ABC()

Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet

Set ws1 = Worksheets("JNNR Client Roster")
Set ws2 = Worksheets("Employee Roster")
Set ws3 = Worksheets("Client- Aide Schedule")

Dim lstrow As Long, r As Long
Dim res As Variant

Dim nMax As Integer
nMax = 0

Application.ScreenUpdating = False

With ws3
lastrow = .Cells(Rows.Count, "A").End(xlUp).row
For r = 2 To lastrow
res = Application.VLookup(.Cells(r, "A"), ws1.Range("A:B"), 2, 0)
If Not IsError(res) Then
ws3.Cells(r, "B") = res
End If
res = Application.Match(.Cells(r, "A"), ws2.Range("A:A"), 0)
If Not IsError(res) Then
n = Application.CountIf(ws2.Range("A:A"), Cells(r, "A"))
ws2.Cells(res, "B").Resize(n, 1).Copy
ws3.Cells(r, "C").PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
nMax = Application.Max(n, nMax)
End If
Next r
End With

ws3.Cells(1, 1).Resize(1, 2) = Array("CASE_ID", "LASTNAME")
For n = 1 To nMax
ws3.Cells(1, n + 2) = "EMPL_" & Trim(Str(n))
Next n

Application.ScreenUpdating = True
End Sub