Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
V Lookup and Paste Special
I'm doing a VLookup function in Worksheet A to find in Worksheet B. The data
in Worksheet B was paste special: values. I did subtotals on the original data and wanted to see the subtotals without the detail this is why I pasted special as values. . Even when I change the format in Worksheet B to match in Worksheet A, it doesnt work. The data in both worksheets are in Text but the VLookup still doesnt work. When I retype the values(names) in the first column of the data range, it works. This may be fine for a few names but not a couple of hundred. Does anyone know how to resolve this? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
V Lookup and Paste Special
I'm not sure about the values(names) thing, but vlookup is feeble under
certain circumstances. Try Index/Match as an alternative: http://www.contextures.com/xlFunctio...tml#IndexMatch That link should give you a few ideas and help you overcome the challenge you face. Regards, Ryan-- -- RyGuy "Manny" wrote: I'm doing a VLookup function in Worksheet A to find in Worksheet B. The data in Worksheet B was paste special: values. I did subtotals on the original data and wanted to see the subtotals without the detail this is why I pasted special as values. . Even when I change the format in Worksheet B to match in Worksheet A, it doesnt work. The data in both worksheets are in Text but the VLookup still doesnt work. When I retype the values(names) in the first column of the data range, it works. This may be fine for a few names but not a couple of hundred. Does anyone know how to resolve this? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
V Lookup and Paste Special
You can obtain your subtotals in a separate sheet by means of the
SUMIF function. Assume your names are in column A of both sheets, and in SheetA you have the names once only (unique list) but there may be several of each in SheetB. Assume you have some numbers in column B of SheetB which you want sub-totalled in SheetA. Put this formula in B1 of SheetA: =SUMIF(SheetB!A:A,A1,SheetB!B:B) and copy down. The names in SheetB do not have to be in any particular order. Hope this helps. Pete On Mar 27, 2:12*pm, Manny wrote: I'm doing a VLookup function in Worksheet A to find in Worksheet B. The data in Worksheet B was paste special: values. I did subtotals on the original data and wanted to see the subtotals without the detail this is why I pasted special as values. . * Even when I change the format in Worksheet B to match in Worksheet A, it doesnt work. The data in both worksheets are in Text but the VLookup still doesnt work. When I retype the values(names) in the first column of the data range, it works. This may be fine for a few names but not a couple of hundred. Does anyone know how to resolve this? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
V Lookup and Paste Special
Pete,
The issue isn't trying to reference the subtotals of names in two seperate worksheets. What is the issue is trying to reference a name in one sheet to another sheet. Earlier I mentioned that the first column (which is a list of names) of the Range_Lookup was pasted special as values. When I retype these names, the function works, however, I am attempting to identify the solution so I wont have to retype hundreds of names. So this means the function itself is correct its just that the way the data in the cells are contained return a #N/A error. "Pete_UK" wrote: You can obtain your subtotals in a separate sheet by means of the SUMIF function. Assume your names are in column A of both sheets, and in SheetA you have the names once only (unique list) but there may be several of each in SheetB. Assume you have some numbers in column B of SheetB which you want sub-totalled in SheetA. Put this formula in B1 of SheetA: =SUMIF(SheetB!A:A,A1,SheetB!B:B) and copy down. The names in SheetB do not have to be in any particular order. Hope this helps. Pete On Mar 27, 2:12 pm, Manny wrote: I'm doing a VLookup function in Worksheet A to find in Worksheet B. The data in Worksheet B was paste special: values. I did subtotals on the original data and wanted to see the subtotals without the detail this is why I pasted special as values. . Even when I change the format in Worksheet B to match in Worksheet A, it doesnt work. The data in both worksheets are in Text but the VLookup still doesnt work. When I retype the values(names) in the first column of the data range, it works. This may be fine for a few names but not a couple of hundred. Does anyone know how to resolve this? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
V Lookup and Paste Special
Well, that indicates that you do not have an exact match - maybe you have
names in your table with spaces before or after them (which are difficult to see, but which you do not type when you enter the name directly). You can apply the TRIM function to your names in a helper column to remove spurious spaces, then fix the values and then paste them back over the original names to cure this problem. If the names came from an HTML source (e.g. web-site) then you might have some non-breaking spaces in there (character code 160), which TRIM will not remove - you can use Find/Replace to get rid of those. Hope this helps. Pete "Manny" wrote in message ... Pete, The issue isn't trying to reference the subtotals of names in two seperate worksheets. What is the issue is trying to reference a name in one sheet to another sheet. Earlier I mentioned that the first column (which is a list of names) of the Range_Lookup was pasted special as values. When I retype these names, the function works, however, I am attempting to identify the solution so I wont have to retype hundreds of names. So this means the function itself is correct its just that the way the data in the cells are contained return a #N/A error. "Pete_UK" wrote: You can obtain your subtotals in a separate sheet by means of the SUMIF function. Assume your names are in column A of both sheets, and in SheetA you have the names once only (unique list) but there may be several of each in SheetB. Assume you have some numbers in column B of SheetB which you want sub-totalled in SheetA. Put this formula in B1 of SheetA: =SUMIF(SheetB!A:A,A1,SheetB!B:B) and copy down. The names in SheetB do not have to be in any particular order. Hope this helps. Pete On Mar 27, 2:12 pm, Manny wrote: I'm doing a VLookup function in Worksheet A to find in Worksheet B. The data in Worksheet B was paste special: values. I did subtotals on the original data and wanted to see the subtotals without the detail this is why I pasted special as values. . Even when I change the format in Worksheet B to match in Worksheet A, it doesnt work. The data in both worksheets are in Text but the VLookup still doesnt work. When I retype the values(names) in the first column of the data range, it works. This may be fine for a few names but not a couple of hundred. Does anyone know how to resolve this? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
V Lookup and Paste Special
Thanks Pete, the TRIM function worked.
"Pete_UK" wrote: Well, that indicates that you do not have an exact match - maybe you have names in your table with spaces before or after them (which are difficult to see, but which you do not type when you enter the name directly). You can apply the TRIM function to your names in a helper column to remove spurious spaces, then fix the values and then paste them back over the original names to cure this problem. If the names came from an HTML source (e.g. web-site) then you might have some non-breaking spaces in there (character code 160), which TRIM will not remove - you can use Find/Replace to get rid of those. Hope this helps. Pete "Manny" wrote in message ... Pete, The issue isn't trying to reference the subtotals of names in two seperate worksheets. What is the issue is trying to reference a name in one sheet to another sheet. Earlier I mentioned that the first column (which is a list of names) of the Range_Lookup was pasted special as values. When I retype these names, the function works, however, I am attempting to identify the solution so I wont have to retype hundreds of names. So this means the function itself is correct its just that the way the data in the cells are contained return a #N/A error. "Pete_UK" wrote: You can obtain your subtotals in a separate sheet by means of the SUMIF function. Assume your names are in column A of both sheets, and in SheetA you have the names once only (unique list) but there may be several of each in SheetB. Assume you have some numbers in column B of SheetB which you want sub-totalled in SheetA. Put this formula in B1 of SheetA: =SUMIF(SheetB!A:A,A1,SheetB!B:B) and copy down. The names in SheetB do not have to be in any particular order. Hope this helps. Pete On Mar 27, 2:12 pm, Manny wrote: I'm doing a VLookup function in Worksheet A to find in Worksheet B. The data in Worksheet B was paste special: values. I did subtotals on the original data and wanted to see the subtotals without the detail this is why I pasted special as values. . Even when I change the format in Worksheet B to match in Worksheet A, it doesnt work. The data in both worksheets are in Text but the VLookup still doesnt work. When I retype the values(names) in the first column of the data range, it works. This may be fine for a few names but not a couple of hundred. Does anyone know how to resolve this? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
V Lookup and Paste Special
Glad to hear it, Manny - thanks for feeding back.
Pete "Manny" wrote in message ... Thanks Pete, the TRIM function worked. "Pete_UK" wrote: Well, that indicates that you do not have an exact match - maybe you have names in your table with spaces before or after them (which are difficult to see, but which you do not type when you enter the name directly). You can apply the TRIM function to your names in a helper column to remove spurious spaces, then fix the values and then paste them back over the original names to cure this problem. If the names came from an HTML source (e.g. web-site) then you might have some non-breaking spaces in there (character code 160), which TRIM will not remove - you can use Find/Replace to get rid of those. Hope this helps. Pete "Manny" wrote in message ... Pete, The issue isn't trying to reference the subtotals of names in two seperate worksheets. What is the issue is trying to reference a name in one sheet to another sheet. Earlier I mentioned that the first column (which is a list of names) of the Range_Lookup was pasted special as values. When I retype these names, the function works, however, I am attempting to identify the solution so I wont have to retype hundreds of names. So this means the function itself is correct its just that the way the data in the cells are contained return a #N/A error. "Pete_UK" wrote: You can obtain your subtotals in a separate sheet by means of the SUMIF function. Assume your names are in column A of both sheets, and in SheetA you have the names once only (unique list) but there may be several of each in SheetB. Assume you have some numbers in column B of SheetB which you want sub-totalled in SheetA. Put this formula in B1 of SheetA: =SUMIF(SheetB!A:A,A1,SheetB!B:B) and copy down. The names in SheetB do not have to be in any particular order. Hope this helps. Pete On Mar 27, 2:12 pm, Manny wrote: I'm doing a VLookup function in Worksheet A to find in Worksheet B. The data in Worksheet B was paste special: values. I did subtotals on the original data and wanted to see the subtotals without the detail this is why I pasted special as values. . Even when I change the format in Worksheet B to match in Worksheet A, it doesnt work. The data in both worksheets are in Text but the VLookup still doesnt work. When I retype the values(names) in the first column of the data range, it works. This may be fine for a few names but not a couple of hundred. Does anyone know how to resolve this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
'paste special', 'paste link' formatting transfer | Excel Discussion (Misc queries) | |||
PASTE LINK option not available when I select PASTE SPECIAL to link an image in Excel to a Word document. | Links and Linking in Excel | |||
In Excel: add a Paste-Special Option to paste IN REVERSE ORDER. | Excel Worksheet Functions | |||
Paste and Paste Special command are not enabled in Excel | Excel Worksheet Functions |