|
problems with displaying "duplicate vlookup values" in same column
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 |
problems with displaying "duplicate vlookup values" in same column
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 "p CAST" wrote: 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 |
problems with displaying "duplicate vlookup values" in same co
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 "Toppers" wrote: 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 "p CAST" wrote: 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 |
problems with displaying "duplicate vlookup values" in same co
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 "p CAST" wrote: 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 "Toppers" wrote: 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 "p CAST" wrote: 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 |
problems with displaying "duplicate vlookup values" in same co
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 "Toppers" wrote: 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 "p CAST" wrote: 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 "Toppers" wrote: 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 "p CAST" wrote: 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 |
problems with displaying "duplicate vlookup values" in same co
Check (all) worksheet name(s) in the code. This all worked correctly for me
when I tested it. "p CAST" wrote: 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 "Toppers" wrote: 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 "p CAST" wrote: 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 "Toppers" wrote: 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 "p CAST" wrote: 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 |
problems with displaying "duplicate vlookup values" in same co
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 "Toppers" wrote: Check (all) worksheet name(s) in the code. This all worked correctly for me when I tested it. "p CAST" wrote: 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 "Toppers" wrote: 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 "p CAST" wrote: 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 "Toppers" wrote: 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 "p CAST" wrote: 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 |
problems with displaying "duplicate vlookup values" in same co
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 "p CAST" wrote: 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 "Toppers" wrote: Check (all) worksheet name(s) in the code. This all worked correctly for me when I tested it. "p CAST" wrote: 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 "Toppers" wrote: 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 "p CAST" wrote: 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 "Toppers" wrote: 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 "p CAST" wrote: 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 |
| All times are GMT +1. The time now is 10:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com