| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross referencing with VLookup ?
I have a sheet with downloaded data:
col c with Name & col f with ID data as below: C F Smith 123 Jones 456 I have another sheet with the same info, different cols. H L Smith 123 Jones 321 I created another sheet that just has the above data referenced from the 2 sheets: A B C D sheet1!C1(smith) sheet1!F1(123) sheet2!H1(smith) sheet2!L1(123) OK sheet1!C2(jones) sheet1!F2(456) sheet2!H2(jones) sheet2!L2 (321) Alert I then want to cross-reference as follows: being that the ID#'s ( col B & col d) for smith in row 1 match, then I'll have an OK in the E col.. but if the #'s don't match, like in the jones row ( col B & col D), then I'll have an alert in the E col. I could do the last part, if(B1=D1,"ok","alert"), but what formulas/functions do I need on sheet 3 to obtain the data from sheet 1 & 2 ? The data locations on sheets 1 & 2 are variable, so I tried doing a Vlookup, but can't to get it to work. I tried to have Vlookup return the name next to the ID, and if the same ID from the other column didn't match it name, then the alert. This probably doesn't make any sense, but hopefully.... Any help would be greatly appreciated. Thanks, Steve |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cross referencing with VLookup ?
On sheet3:
Assuming data starts on row 1.... A1: =Sheet1!C1 B1: =Sheet1!F1 C1: =VLOOKUP(A1,sheet2!H:L,1,0) D1: =VLOOKUP(A1,sheet2!H:L,5,0) If there is a possibility data doesn't exist on sheet2 then: C1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,1,0)),"",VLOOKUP(A1 ,sheet2!H:L,1,0)) D1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,1,5)),"",VLOOKUP(A1 ,sheet2!H:L,1,5)) is one way. Copy formulae down. HTH "Steve" wrote: I have a sheet with downloaded data: col c with Name & col f with ID data as below: C F Smith 123 Jones 456 I have another sheet with the same info, different cols. H L Smith 123 Jones 321 I created another sheet that just has the above data referenced from the 2 sheets: A B C D sheet1!C1(smith) sheet1!F1(123) sheet2!H1(smith) sheet2!L1(123) OK sheet1!C2(jones) sheet1!F2(456) sheet2!H2(jones) sheet2!L2 (321) Alert I then want to cross-reference as follows: being that the ID#'s ( col B & col d) for smith in row 1 match, then I'll have an OK in the E col.. but if the #'s don't match, like in the jones row ( col B & col D), then I'll have an alert in the E col. I could do the last part, if(B1=D1,"ok","alert"), but what formulas/functions do I need on sheet 3 to obtain the data from sheet 1 & 2 ? The data locations on sheets 1 & 2 are variable, so I tried doing a Vlookup, but can't to get it to work. I tried to have Vlookup return the name next to the ID, and if the same ID from the other column didn't match it name, then the alert. This probably doesn't make any sense, but hopefully.... Any help would be greatly appreciated. Thanks, Steve |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cross referencing with VLookup ?
Typo ...
D1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,5,0)),"",VLOOKUP(A1 ,sheet2!H:L,5,0)) "Toppers" wrote: On sheet3: Assuming data starts on row 1.... A1: =Sheet1!C1 B1: =Sheet1!F1 C1: =VLOOKUP(A1,sheet2!H:L,1,0) D1: =VLOOKUP(A1,sheet2!H:L,5,0) If there is a possibility data doesn't exist on sheet2 then: C1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,1,0)),"",VLOOKUP(A1 ,sheet2!H:L,1,0)) D1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,1,5)),"",VLOOKUP(A1 ,sheet2!H:L,1,5)) is one way. Copy formulae down. HTH "Steve" wrote: I have a sheet with downloaded data: col c with Name & col f with ID data as below: C F Smith 123 Jones 456 I have another sheet with the same info, different cols. H L Smith 123 Jones 321 I created another sheet that just has the above data referenced from the 2 sheets: A B C D sheet1!C1(smith) sheet1!F1(123) sheet2!H1(smith) sheet2!L1(123) OK sheet1!C2(jones) sheet1!F2(456) sheet2!H2(jones) sheet2!L2 (321) Alert I then want to cross-reference as follows: being that the ID#'s ( col B & col d) for smith in row 1 match, then I'll have an OK in the E col.. but if the #'s don't match, like in the jones row ( col B & col D), then I'll have an alert in the E col. I could do the last part, if(B1=D1,"ok","alert"), but what formulas/functions do I need on sheet 3 to obtain the data from sheet 1 & 2 ? The data locations on sheets 1 & 2 are variable, so I tried doing a Vlookup, but can't to get it to work. I tried to have Vlookup return the name next to the ID, and if the same ID from the other column didn't match it name, then the alert. This probably doesn't make any sense, but hopefully.... Any help would be greatly appreciated. Thanks, Steve |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cross referencing with VLookup ?
Thanks much, it's partially working. The problem I know have is that in the
name column, sheet 1 has a variety of types ( last name only, last name first name, last name comma first name, last name comma first name middle initial, etc.) Sheet 2 has last names only, and therefore it only works if the names are an exact match. Is there some way I could have only the last name show in the cell, thereby getting the needed match ? Maybe something like 'to the right until a space is detected'? Thanks again, Steve "Toppers" wrote: Typo ... D1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,5,0)),"",VLOOKUP(A1 ,sheet2!H:L,5,0)) "Toppers" wrote: On sheet3: Assuming data starts on row 1.... A1: =Sheet1!C1 B1: =Sheet1!F1 C1: =VLOOKUP(A1,sheet2!H:L,1,0) D1: =VLOOKUP(A1,sheet2!H:L,5,0) If there is a possibility data doesn't exist on sheet2 then: C1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,1,0)),"",VLOOKUP(A1 ,sheet2!H:L,1,0)) D1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,1,5)),"",VLOOKUP(A1 ,sheet2!H:L,1,5)) is one way. Copy formulae down. HTH "Steve" wrote: I have a sheet with downloaded data: col c with Name & col f with ID data as below: C F Smith 123 Jones 456 I have another sheet with the same info, different cols. H L Smith 123 Jones 321 I created another sheet that just has the above data referenced from the 2 sheets: A B C D sheet1!C1(smith) sheet1!F1(123) sheet2!H1(smith) sheet2!L1(123) OK sheet1!C2(jones) sheet1!F2(456) sheet2!H2(jones) sheet2!L2 (321) Alert I then want to cross-reference as follows: being that the ID#'s ( col B & col d) for smith in row 1 match, then I'll have an OK in the E col.. but if the #'s don't match, like in the jones row ( col B & col D), then I'll have an alert in the E col. I could do the last part, if(B1=D1,"ok","alert"), but what formulas/functions do I need on sheet 3 to obtain the data from sheet 1 & 2 ? The data locations on sheets 1 & 2 are variable, so I tried doing a Vlookup, but can't to get it to work. I tried to have Vlookup return the name next to the ID, and if the same ID from the other column didn't match it name, then the alert. This probably doesn't make any sense, but hopefully.... Any help would be greatly appreciated. Thanks, Steve |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cross referencing with VLookup ?
Upon further review....
Virtually all the names in sheet a have a comma after the last name, so could something be used to use everything to the left of the comma ? Thanks again, Steve "Steve" wrote: Thanks much, it's partially working. The problem I know have is that in the name column, sheet 1 has a variety of types ( last name only, last name first name, last name comma first name, last name comma first name middle initial, etc.) Sheet 2 has last names only, and therefore it only works if the names are an exact match. Is there some way I could have only the last name show in the cell, thereby getting the needed match ? Maybe something like 'to the right until a space is detected'? Thanks again, Steve "Toppers" wrote: Typo ... D1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,5,0)),"",VLOOKUP(A1 ,sheet2!H:L,5,0)) "Toppers" wrote: On sheet3: Assuming data starts on row 1.... A1: =Sheet1!C1 B1: =Sheet1!F1 C1: =VLOOKUP(A1,sheet2!H:L,1,0) D1: =VLOOKUP(A1,sheet2!H:L,5,0) If there is a possibility data doesn't exist on sheet2 then: C1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,1,0)),"",VLOOKUP(A1 ,sheet2!H:L,1,0)) D1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,1,5)),"",VLOOKUP(A1 ,sheet2!H:L,1,5)) is one way. Copy formulae down. HTH "Steve" wrote: I have a sheet with downloaded data: col c with Name & col f with ID data as below: C F Smith 123 Jones 456 I have another sheet with the same info, different cols. H L Smith 123 Jones 321 I created another sheet that just has the above data referenced from the 2 sheets: A B C D sheet1!C1(smith) sheet1!F1(123) sheet2!H1(smith) sheet2!L1(123) OK sheet1!C2(jones) sheet1!F2(456) sheet2!H2(jones) sheet2!L2 (321) Alert I then want to cross-reference as follows: being that the ID#'s ( col B & col d) for smith in row 1 match, then I'll have an OK in the E col.. but if the #'s don't match, like in the jones row ( col B & col D), then I'll have an alert in the E col. I could do the last part, if(B1=D1,"ok","alert"), but what formulas/functions do I need on sheet 3 to obtain the data from sheet 1 & 2 ? The data locations on sheets 1 & 2 are variable, so I tried doing a Vlookup, but can't to get it to work. I tried to have Vlookup return the name next to the ID, and if the same ID from the other column didn't match it name, then the alert. This probably doesn't make any sense, but hopefully.... Any help would be greatly appreciated. Thanks, Steve |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cross referencing with VLookup ?
Try:
=LEFT(VLOOKUP(A1&"*",Sheet1!H:L,1,0),FIND(",",VLOO KUP(A1&"*",Sheet1!H:L,1,0))-1) Sheet1 is the one with "mixed" names "Steve" wrote: Upon further review.... Virtually all the names in sheet a have a comma after the last name, so could something be used to use everything to the left of the comma ? Thanks again, Steve "Steve" wrote: Thanks much, it's partially working. The problem I know have is that in the name column, sheet 1 has a variety of types ( last name only, last name first name, last name comma first name, last name comma first name middle initial, etc.) Sheet 2 has last names only, and therefore it only works if the names are an exact match. Is there some way I could have only the last name show in the cell, thereby getting the needed match ? Maybe something like 'to the right until a space is detected'? Thanks again, Steve "Toppers" wrote: Typo ... D1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,5,0)),"",VLOOKUP(A1 ,sheet2!H:L,5,0)) "Toppers" wrote: On sheet3: Assuming data starts on row 1.... A1: =Sheet1!C1 B1: =Sheet1!F1 C1: =VLOOKUP(A1,sheet2!H:L,1,0) D1: =VLOOKUP(A1,sheet2!H:L,5,0) If there is a possibility data doesn't exist on sheet2 then: C1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,1,0)),"",VLOOKUP(A1 ,sheet2!H:L,1,0)) D1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,1,5)),"",VLOOKUP(A1 ,sheet2!H:L,1,5)) is one way. Copy formulae down. HTH "Steve" wrote: I have a sheet with downloaded data: col c with Name & col f with ID data as below: C F Smith 123 Jones 456 I have another sheet with the same info, different cols. H L Smith 123 Jones 321 I created another sheet that just has the above data referenced from the 2 sheets: A B C D sheet1!C1(smith) sheet1!F1(123) sheet2!H1(smith) sheet2!L1(123) OK sheet1!C2(jones) sheet1!F2(456) sheet2!H2(jones) sheet2!L2 (321) Alert I then want to cross-reference as follows: being that the ID#'s ( col B & col d) for smith in row 1 match, then I'll have an OK in the E col.. but if the #'s don't match, like in the jones row ( col B & col D), then I'll have an alert in the E col. I could do the last part, if(B1=D1,"ok","alert"), but what formulas/functions do I need on sheet 3 to obtain the data from sheet 1 & 2 ? The data locations on sheets 1 & 2 are variable, so I tried doing a Vlookup, but can't to get it to work. I tried to have Vlookup return the name next to the ID, and if the same ID from the other column didn't match it name, then the alert. This probably doesn't make any sense, but hopefully.... Any help would be greatly appreciated. Thanks, Steve |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cross referencing with VLookup ?
.. and for column L data:
D1: =VLOOKUP($A1&"*",Sheet1!$H:$L,5,0) "Toppers" wrote: Try: =LEFT(VLOOKUP(A1&"*",Sheet1!H:L,1,0),FIND(",",VLOO KUP(A1&"*",Sheet1!H:L,1,0))-1) Sheet1 is the one with "mixed" names "Steve" wrote: Upon further review.... Virtually all the names in sheet a have a comma after the last name, so could something be used to use everything to the left of the comma ? Thanks again, Steve "Steve" wrote: Thanks much, it's partially working. The problem I know have is that in the name column, sheet 1 has a variety of types ( last name only, last name first name, last name comma first name, last name comma first name middle initial, etc.) Sheet 2 has last names only, and therefore it only works if the names are an exact match. Is there some way I could have only the last name show in the cell, thereby getting the needed match ? Maybe something like 'to the right until a space is detected'? Thanks again, Steve "Toppers" wrote: Typo ... D1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,5,0)),"",VLOOKUP(A1 ,sheet2!H:L,5,0)) "Toppers" wrote: On sheet3: Assuming data starts on row 1.... A1: =Sheet1!C1 B1: =Sheet1!F1 C1: =VLOOKUP(A1,sheet2!H:L,1,0) D1: =VLOOKUP(A1,sheet2!H:L,5,0) If there is a possibility data doesn't exist on sheet2 then: C1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,1,0)),"",VLOOKUP(A1 ,sheet2!H:L,1,0)) D1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,1,5)),"",VLOOKUP(A1 ,sheet2!H:L,1,5)) is one way. Copy formulae down. HTH "Steve" wrote: I have a sheet with downloaded data: col c with Name & col f with ID data as below: C F Smith 123 Jones 456 I have another sheet with the same info, different cols. H L Smith 123 Jones 321 I created another sheet that just has the above data referenced from the 2 sheets: A B C D sheet1!C1(smith) sheet1!F1(123) sheet2!H1(smith) sheet2!L1(123) OK sheet1!C2(jones) sheet1!F2(456) sheet2!H2(jones) sheet2!L2 (321) Alert I then want to cross-reference as follows: being that the ID#'s ( col B & col d) for smith in row 1 match, then I'll have an OK in the E col.. but if the #'s don't match, like in the jones row ( col B & col D), then I'll have an alert in the E col. I could do the last part, if(B1=D1,"ok","alert"), but what formulas/functions do I need on sheet 3 to obtain the data from sheet 1 & 2 ? The data locations on sheets 1 & 2 are variable, so I tried doing a Vlookup, but can't to get it to work. I tried to have Vlookup return the name next to the ID, and if the same ID from the other column didn't match it name, then the alert. This probably doesn't make any sense, but hopefully.... Any help would be greatly appreciated. Thanks, Steve |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cross referencing with VLookup ?
....in C1 do you really need to do a lookup? Only to capture if data is missing?
Otherwise make C1=A1 "Toppers" wrote: .. and for column L data: D1: =VLOOKUP($A1&"*",Sheet1!$H:$L,5,0) "Toppers" wrote: Try: =LEFT(VLOOKUP(A1&"*",Sheet1!H:L,1,0),FIND(",",VLOO KUP(A1&"*",Sheet1!H:L,1,0))-1) Sheet1 is the one with "mixed" names "Steve" wrote: Upon further review.... Virtually all the names in sheet a have a comma after the last name, so could something be used to use everything to the left of the comma ? Thanks again, Steve "Steve" wrote: Thanks much, it's partially working. The problem I know have is that in the name column, sheet 1 has a variety of types ( last name only, last name first name, last name comma first name, last name comma first name middle initial, etc.) Sheet 2 has last names only, and therefore it only works if the names are an exact match. Is there some way I could have only the last name show in the cell, thereby getting the needed match ? Maybe something like 'to the right until a space is detected'? Thanks again, Steve "Toppers" wrote: Typo ... D1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,5,0)),"",VLOOKUP(A1 ,sheet2!H:L,5,0)) "Toppers" wrote: On sheet3: Assuming data starts on row 1.... A1: =Sheet1!C1 B1: =Sheet1!F1 C1: =VLOOKUP(A1,sheet2!H:L,1,0) D1: =VLOOKUP(A1,sheet2!H:L,5,0) If there is a possibility data doesn't exist on sheet2 then: C1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,1,0)),"",VLOOKUP(A1 ,sheet2!H:L,1,0)) D1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,1,5)),"",VLOOKUP(A1 ,sheet2!H:L,1,5)) is one way. Copy formulae down. HTH "Steve" wrote: I have a sheet with downloaded data: col c with Name & col f with ID data as below: C F Smith 123 Jones 456 I have another sheet with the same info, different cols. H L Smith 123 Jones 321 I created another sheet that just has the above data referenced from the 2 sheets: A B C D sheet1!C1(smith) sheet1!F1(123) sheet2!H1(smith) sheet2!L1(123) OK sheet1!C2(jones) sheet1!F2(456) sheet2!H2(jones) sheet2!L2 (321) Alert I then want to cross-reference as follows: being that the ID#'s ( col B & col d) for smith in row 1 match, then I'll have an OK in the E col.. but if the #'s don't match, like in the jones row ( col B & col D), then I'll have an alert in the E col. I could do the last part, if(B1=D1,"ok","alert"), but what formulas/functions do I need on sheet 3 to obtain the data from sheet 1 & 2 ? The data locations on sheets 1 & 2 are variable, so I tried doing a Vlookup, but can't to get it to work. I tried to have Vlookup return the name next to the ID, and if the same ID from the other column didn't match it name, then the alert. This probably doesn't make any sense, but hopefully.... Any help would be greatly appreciated. Thanks, Steve |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cross referencing with VLookup ?
Perfect - It's working great. Thanks for all your patience and assistance.
The next problem I've deleloped is that when there is more than one of the same name, such as 5 Smiths, what it's doing is returning the ID data for the first Smith for all the Smiths, thereby producing errors for the 4 other smiths. I could manually deal with that, but if another formula could be devised to ignore anytime it finds more than one of the same name, that would save me some time. As an example, here are the formulas I'm using in the particular cells: In G 24: =IF(ISNA(VLOOKUP(D4,'Data'!C:F,4,0)),"",(VLOOKUP(D 24,'data'!C:F,4,0))) In H24: =IF(E24=G24,"","ID Alert") In J24: =IF(ISERROR(H24),"",(H24)) Like I said, it works great. But is there a way to supress " " the "ID Alert" in cell H24 if row C of the data sheet has any duplicate names, such as those 5 Smiths ? Again, thanks so much. Steve "Toppers" wrote: ...in C1 do you really need to do a lookup? Only to capture if data is missing? Otherwise make C1=A1 "Toppers" wrote: .. and for column L data: D1: =VLOOKUP($A1&"*",Sheet1!$H:$L,5,0) "Toppers" wrote: Try: =LEFT(VLOOKUP(A1&"*",Sheet1!H:L,1,0),FIND(",",VLOO KUP(A1&"*",Sheet1!H:L,1,0))-1) Sheet1 is the one with "mixed" names "Steve" wrote: Upon further review.... Virtually all the names in sheet a have a comma after the last name, so could something be used to use everything to the left of the comma ? Thanks again, Steve "Steve" wrote: Thanks much, it's partially working. The problem I know have is that in the name column, sheet 1 has a variety of types ( last name only, last name first name, last name comma first name, last name comma first name middle initial, etc.) Sheet 2 has last names only, and therefore it only works if the names are an exact match. Is there some way I could have only the last name show in the cell, thereby getting the needed match ? Maybe something like 'to the right until a space is detected'? Thanks again, Steve "Toppers" wrote: Typo ... D1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,5,0)),"",VLOOKUP(A1 ,sheet2!H:L,5,0)) "Toppers" wrote: On sheet3: Assuming data starts on row 1.... A1: =Sheet1!C1 B1: =Sheet1!F1 C1: =VLOOKUP(A1,sheet2!H:L,1,0) D1: =VLOOKUP(A1,sheet2!H:L,5,0) If there is a possibility data doesn't exist on sheet2 then: C1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,1,0)),"",VLOOKUP(A1 ,sheet2!H:L,1,0)) D1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,1,5)),"",VLOOKUP(A1 ,sheet2!H:L,1,5)) is one way. Copy formulae down. HTH "Steve" wrote: I have a sheet with downloaded data: col c with Name & col f with ID data as below: C F Smith 123 Jones 456 I have another sheet with the same info, different cols. H L Smith 123 Jones 321 I created another sheet that just has the above data referenced from the 2 sheets: A B C D sheet1!C1(smith) sheet1!F1(123) sheet2!H1(smith) sheet2!L1(123) OK sheet1!C2(jones) sheet1!F2(456) sheet2!H2(jones) sheet2!L2 (321) Alert I then want to cross-reference as follows: being that the ID#'s ( col B & col d) for smith in row 1 match, then I'll have an OK in the E col.. but if the #'s don't match, like in the jones row ( col B & col D), then I'll have an alert in the E col. I could do the last part, if(B1=D1,"ok","alert"), but what formulas/functions do I need on sheet 3 to obtain the data from sheet 1 & 2 ? The data locations on sheets 1 & 2 are variable, so I tried doing a Vlookup, but can't to get it to work. I tried to have Vlookup return the name next to the ID, and if the same ID from the other column didn't match it name, then the alert. This probably doesn't make any sense, but hopefully.... Any help would be greatly appreciated. Thanks, Steve |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cross referencing with VLookup ?
Steve,
Slightly confused by reference to Column E as previous postings have only used columns A to D. I realised also (after posting my reply!) that "Smiths" (multiple occurences of same surname) would cause a problem. Can you send me a sample w/book so I can look at the data and then try to offer a "best" solution? toppers at REMOVETHISjohntopley.fsnet.co.uk "Steve" wrote: Perfect - It's working great. Thanks for all your patience and assistance. The next problem I've deleloped is that when there is more than one of the same name, such as 5 Smiths, what it's doing is returning the ID data for the first Smith for all the Smiths, thereby producing errors for the 4 other smiths. I could manually deal with that, but if another formula could be devised to ignore anytime it finds more than one of the same name, that would save me some time. As an example, here are the formulas I'm using in the particular cells: In G 24: =IF(ISNA(VLOOKUP(D4,'Data'!C:F,4,0)),"",(VLOOKUP(D 24,'data'!C:F,4,0))) In H24: =IF(E24=G24,"","ID Alert") In J24: =IF(ISERROR(H24),"",(H24)) Like I said, it works great. But is there a way to supress " " the "ID Alert" in cell H24 if row C of the data sheet has any duplicate names, such as those 5 Smiths ? Again, thanks so much. Steve "Toppers" wrote: ...in C1 do you really need to do a lookup? Only to capture if data is missing? Otherwise make C1=A1 "Toppers" wrote: .. and for column L data: D1: =VLOOKUP($A1&"*",Sheet1!$H:$L,5,0) "Toppers" wrote: Try: =LEFT(VLOOKUP(A1&"*",Sheet1!H:L,1,0),FIND(",",VLOO KUP(A1&"*",Sheet1!H:L,1,0))-1) Sheet1 is the one with "mixed" names "Steve" wrote: Upon further review.... Virtually all the names in sheet a have a comma after the last name, so could something be used to use everything to the left of the comma ? Thanks again, Steve "Steve" wrote: Thanks much, it's partially working. The problem I know have is that in the name column, sheet 1 has a variety of types ( last name only, last name first name, last name comma first name, last name comma first name middle initial, etc.) Sheet 2 has last names only, and therefore it only works if the names are an exact match. Is there some way I could have only the last name show in the cell, thereby getting the needed match ? Maybe something like 'to the right until a space is detected'? Thanks again, Steve "Toppers" wrote: Typo ... D1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,5,0)),"",VLOOKUP(A1 ,sheet2!H:L,5,0)) "Toppers" wrote: On sheet3: Assuming data starts on row 1.... A1: =Sheet1!C1 B1: =Sheet1!F1 C1: =VLOOKUP(A1,sheet2!H:L,1,0) D1: =VLOOKUP(A1,sheet2!H:L,5,0) If there is a possibility data doesn't exist on sheet2 then: C1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,1,0)),"",VLOOKUP(A1 ,sheet2!H:L,1,0)) D1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,1,5)),"",VLOOKUP(A1 ,sheet2!H:L,1,5)) is one way. Copy formulae down. HTH "Steve" wrote: I have a sheet with downloaded data: col c with Name & col f with ID data as below: C F Smith 123 Jones 456 I have another sheet with the same info, different cols. H L Smith 123 Jones 321 I created another sheet that just has the above data referenced from the 2 sheets: A B C D sheet1!C1(smith) sheet1!F1(123) sheet2!H1(smith) sheet2!L1(123) OK sheet1!C2(jones) sheet1!F2(456) sheet2!H2(jones) sheet2!L2 (321) Alert I then want to cross-reference as follows: being that the ID#'s ( col B & col d) for smith in row 1 match, then I'll have an OK in the E col.. but if the #'s don't match, like in the jones row ( col B & col D), then I'll have an alert in the E col. I could do the last part, if(B1=D1,"ok","alert"), but what formulas/functions do I need on sheet 3 to obtain the data from sheet 1 & 2 ? The data locations on sheets 1 & 2 are variable, so I tried doing a Vlookup, but can't to get it to work. I tried to have Vlookup return the name next to the ID, and if the same ID from the other column didn't match it name, then the alert. This probably doesn't make any sense, but hopefully.... Any help would be greatly appreciated. Thanks, Steve |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Cross referencing with VLookup ?
Ok, great. Thanks,
It's on it's way. "Toppers" wrote: Steve, Slightly confused by reference to Column E as previous postings have only used columns A to D. I realised also (after posting my reply!) that "Smiths" (multiple occurences of same surname) would cause a problem. Can you send me a sample w/book so I can look at the data and then try to offer a "best" solution? toppers at REMOVETHISjohntopley.fsnet.co.uk "Steve" wrote: Perfect - It's working great. Thanks for all your patience and assistance. The next problem I've deleloped is that when there is more than one of the same name, such as 5 Smiths, what it's doing is returning the ID data for the first Smith for all the Smiths, thereby producing errors for the 4 other smiths. I could manually deal with that, but if another formula could be devised to ignore anytime it finds more than one of the same name, that would save me some time. As an example, here are the formulas I'm using in the particular cells: In G 24: =IF(ISNA(VLOOKUP(D4,'Data'!C:F,4,0)),"",(VLOOKUP(D 24,'data'!C:F,4,0))) In H24: =IF(E24=G24,"","ID Alert") In J24: =IF(ISERROR(H24),"",(H24)) Like I said, it works great. But is there a way to supress " " the "ID Alert" in cell H24 if row C of the data sheet has any duplicate names, such as those 5 Smiths ? Again, thanks so much. Steve "Toppers" wrote: ...in C1 do you really need to do a lookup? Only to capture if data is missing? Otherwise make C1=A1 "Toppers" wrote: .. and for column L data: D1: =VLOOKUP($A1&"*",Sheet1!$H:$L,5,0) "Toppers" wrote: Try: =LEFT(VLOOKUP(A1&"*",Sheet1!H:L,1,0),FIND(",",VLOO KUP(A1&"*",Sheet1!H:L,1,0))-1) Sheet1 is the one with "mixed" names "Steve" wrote: Upon further review.... Virtually all the names in sheet a have a comma after the last name, so could something be used to use everything to the left of the comma ? Thanks again, Steve "Steve" wrote: Thanks much, it's partially working. The problem I know have is that in the name column, sheet 1 has a variety of types ( last name only, last name first name, last name comma first name, last name comma first name middle initial, etc.) Sheet 2 has last names only, and therefore it only works if the names are an exact match. Is there some way I could have only the last name show in the cell, thereby getting the needed match ? Maybe something like 'to the right until a space is detected'? Thanks again, Steve "Toppers" wrote: Typo ... D1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,5,0)),"",VLOOKUP(A1 ,sheet2!H:L,5,0)) "Toppers" wrote: On sheet3: Assuming data starts on row 1.... A1: =Sheet1!C1 B1: =Sheet1!F1 C1: =VLOOKUP(A1,sheet2!H:L,1,0) D1: =VLOOKUP(A1,sheet2!H:L,5,0) If there is a possibility data doesn't exist on sheet2 then: C1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,1,0)),"",VLOOKUP(A1 ,sheet2!H:L,1,0)) D1: =IF(ISNA(VLOOKUP(A1,sheet2!H:L,1,5)),"",VLOOKUP(A1 ,sheet2!H:L,1,5)) is one way. Copy formulae down. HTH "Steve" wrote: I have a sheet with downloaded data: col c with Name & col f with ID data as below: C F Smith 123 Jones 456 I have another sheet with the same info, different cols. H L Smith 123 Jones 321 I created another sheet that just has the above data referenced from the 2 sheets: A B C D sheet1!C1(smith) sheet1!F1(123) sheet2!H1(smith) sheet2!L1(123) OK sheet1!C2(jones) sheet1!F2(456) sheet2!H2(jones) sheet2!L2 (321) Alert I then want to cross-reference as follows: being that the ID#'s ( col B & col d) for smith in row 1 match, then I'll have an OK in the E col.. but if the #'s don't match, like in the jones row ( col B & col D), then I'll have an alert in the E col. I could do the last part, if(B1=D1,"ok","alert"), but what formulas/functions do I need on sheet 3 to obtain the data from sheet 1 & 2 ? The data locations on sheets 1 & 2 are variable, so I tried doing a Vlookup, but can't to get it to work. I tried to have Vlookup return the name next to the ID, and if the same ID from the other column didn't match it name, then the alert. This probably doesn't make any sense, but hopefully.... Any help would be greatly appreciated. Thanks, Steve |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Cross Referencing | Excel Discussion (Misc queries) | |||
| Cross referencing | Excel Worksheet Functions | |||
| If/cross referencing functions | Excel Worksheet Functions | |||
| cross referencing | Excel Worksheet Functions | |||
| Cross-Referencing numbers. Need help please | Excel Discussion (Misc queries) | |||
Linear Mode
