Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transferring data from column to another without VLOOKUP
Ok, so this is going to be a bit difficult to explain, AND I'm a newbie to
functions. I've got one spreadsheet with an array of data that looks like this. spreadsheet A CL 45,000 CH 300 DE 8,884 As you can guess, the letters are in one column and the numbers in another. spreadsheet B BB 0 BZ 0 CL 0 CM 0 CN 0 DA 0 DE 0 So what I would like to do is copy the info from spreadsheet A to spreadsheet B. I know that VLOOKUP can do this for me but generates an error in spreadsheet B for every letter code that isn't in spreadsheet A. I'm trying to avoid this and have the number values appear for letter codes in spreadsheet B that are in spreadsheet A and a 0 for letter codes that aren't in spreadsheet A. Can anybody help? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transferring data from column to another without VLOOKUP
Hi GP6,
You can still use the VLOOKUP function. You just have to include an error trap. For example: IF(ISERROR(Your VLOOKUP formula),0,Your Lookup Formula) This will result in 0 (zero) if the VLOOKUP wants to return an error. If the VLOOKUP result is ok, then its result is displayed. Regards - Dave |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transferring data from column to another without VLOOKUP
Another way is SUMPRODUCT:
=SUMPRODUCT(--('Spreadsheet A'!$A$1:$A$3=A1),'Spreadsheet A'!$B$1:$B$3) "goodpunk6" wrote: Ok, so this is going to be a bit difficult to explain, AND I'm a newbie to functions. I've got one spreadsheet with an array of data that looks like this. spreadsheet A CL 45,000 CH 300 DE 8,884 As you can guess, the letters are in one column and the numbers in another. spreadsheet B BB 0 BZ 0 CL 0 CM 0 CN 0 DA 0 DE 0 So what I would like to do is copy the info from spreadsheet A to spreadsheet B. I know that VLOOKUP can do this for me but generates an error in spreadsheet B for every letter code that isn't in spreadsheet A. I'm trying to avoid this and have the number values appear for letter codes in spreadsheet B that are in spreadsheet A and a 0 for letter codes that aren't in spreadsheet A. Can anybody help? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transferring data from column to another without VLOOKUP
Anyway to make this more "click friendly?"
"sb1920alk" wrote: Another way is SUMPRODUCT: =SUMPRODUCT(--('Spreadsheet A'!$A$1:$A$3=A1),'Spreadsheet A'!$B$1:$B$3) "goodpunk6" wrote: Ok, so this is going to be a bit difficult to explain, AND I'm a newbie to functions. I've got one spreadsheet with an array of data that looks like this. spreadsheet A CL 45,000 CH 300 DE 8,884 As you can guess, the letters are in one column and the numbers in another. spreadsheet B BB 0 BZ 0 CL 0 CM 0 CN 0 DA 0 DE 0 So what I would like to do is copy the info from spreadsheet A to spreadsheet B. I know that VLOOKUP can do this for me but generates an error in spreadsheet B for every letter code that isn't in spreadsheet A. I'm trying to avoid this and have the number values appear for letter codes in spreadsheet B that are in spreadsheet A and a 0 for letter codes that aren't in spreadsheet A. Can anybody help? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transferring data from column to another without VLOOKUP
I'm not sure what you mean. What will you be clicking?
"goodpunk6" wrote: Anyway to make this more "click friendly?" "sb1920alk" wrote: Another way is SUMPRODUCT: =SUMPRODUCT(--('Spreadsheet A'!$A$1:$A$3=A1),'Spreadsheet A'!$B$1:$B$3) "goodpunk6" wrote: Ok, so this is going to be a bit difficult to explain, AND I'm a newbie to functions. I've got one spreadsheet with an array of data that looks like this. spreadsheet A CL 45,000 CH 300 DE 8,884 As you can guess, the letters are in one column and the numbers in another. spreadsheet B BB 0 BZ 0 CL 0 CM 0 CN 0 DA 0 DE 0 So what I would like to do is copy the info from spreadsheet A to spreadsheet B. I know that VLOOKUP can do this for me but generates an error in spreadsheet B for every letter code that isn't in spreadsheet A. I'm trying to avoid this and have the number values appear for letter codes in spreadsheet B that are in spreadsheet A and a 0 for letter codes that aren't in spreadsheet A. Can anybody help? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transferring data from column to another without VLOOKUP
"goodpunk6" wrote: Anyway to make this more "click friendly?" Huh? Dave. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with transferring data in one column to multiple columns. | New Users to Excel | |||
transferring amount to particular month column | Excel Worksheet Functions | |||
how to setup transferring data from row to column | Setting up and Configuration of Excel | |||
How to setup transferring data from row to column? | Excel Discussion (Misc queries) | |||
vlookup...transferring info from 1 sheet to another.... | Excel Worksheet Functions |