Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to extract information from one table to another one
Question:
For the example below, if one would extract the Food information from Table 2 and put it to the corresponding Food column in Table 1, whats the best way to do this? Is there a way without using macro? The expecting result for the example below Area Food AA apple BB orange CC cherry DD banana My real data has more than 10,000 rows. Thus I would very much appreciate your help. Omics Example: Table 1 Area Food AA BB CC DD Table 2 Area Food Store AA apple x AA apple y AA apple z DD banana 3 DD banana m BB orange 1 CC cherry 10 FF potato v GG peanut s GG peanut a HH fish 6 BB orange j EE tomato l EE tomato 8 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to extract information from one table to another one
Uytkownik "Omics" napisa w wiadomoci
... Question: For the example below, if one would extract the "Food" information from Table 2 and put it to the corresponding "Food" column in Table 1, what's the best way to do this? Is there a way without using macro? The expecting result for the example below Area Food AA apple BB orange CC cherry DD banana My real data has more than 10,000 rows. Thus I would very much appreciate your help. I assume that Table1 and Table2 are separate worksheets in the same workbook, and Area and Food are columns A and B in these worksheets. In Table1 column Food cell B1 try this (when A1 ie AA) =VLOOKUP(A1, Table1!A:B;2;FALSE) and then copy that formula down below This works fime when there is one Food for one Area. When there is more than one Food for one Area (ie apple and pear for AA) - formula returns that value which is first founded (when first apple is in row 24 and first pear in column 36 - formula returns apple). -- ============================== Janusz Pawlinka |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to extract information from one table to another one
Thanks. Tried it but it said formula error. Omics "Janusz Pawlinka" wrote: U¿ytkownik "Omics" napisa³ w wiadomo¶ci ... Question: For the example below, if one would extract the "Food" information from Table 2 and put it to the corresponding "Food" column in Table 1, what's the best way to do this? Is there a way without using macro? The expecting result for the example below Area Food AA apple BB orange CC cherry DD banana My real data has more than 10,000 rows. Thus I would very much appreciate your help. I assume that Table1 and Table2 are separate worksheets in the same workbook, and Area and Food are columns A and B in these worksheets. In Table1 column Food cell B1 try this (when A1 ie AA) =VLOOKUP(A1, Table1!A:B;2;FALSE) and then copy that formula down below This works fime when there is one Food for one Area. When there is more than one Food for one Area (ie apple and pear for AA) - formula returns that value which is first founded (when first apple is in row 24 and first pear in column 36 - formula returns apple). -- ============================== Janusz Pawlinka |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to extract information from one table to another one
You'll need to use either commas or semi-colons, depending on what you use
as your list separator (specfied in Windows Regional Options), but not a mixture of the two. :-( -- David Biddulph "Omics" wrote in message ... Thanks. Tried it but it said formula error. Omics "Janusz Pawlinka" wrote: Uytkownik "Omics" napisa w wiadomoci ... Question: For the example below, if one would extract the "Food" information from Table 2 and put it to the corresponding "Food" column in Table 1, what's the best way to do this? Is there a way without using macro? The expecting result for the example below Area Food AA apple BB orange CC cherry DD banana My real data has more than 10,000 rows. Thus I would very much appreciate your help. I assume that Table1 and Table2 are separate worksheets in the same workbook, and Area and Food are columns A and B in these worksheets. In Table1 column Food cell B1 try this (when A1 ie AA) =VLOOKUP(A1, Table1!A:B;2;FALSE) and then copy that formula down below This works fime when there is one Food for one Area. When there is more than one Food for one Area (ie apple and pear for AA) - formula returns that value which is first founded (when first apple is in row 24 and first pear in column 36 - formula returns apple). -- ============================== Janusz Pawlinka |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to extract information from one table to another one
Uytkownik "David Biddulph" <groups [at] biddulph.org.uk napisa w
wiadomoci ... You'll need to use either commas or semi-colons, depending on what you use as your list separator (specfied in Windows Regional Options), but not a mixture of the two. :-( Of course. Thanks for help and explaining. I use polish version of Excel (with semi-colons as separators), but I must 'translate' formulas from polish to 'normal' (I mean english/US) version to show its here. In polish version of Excel formula from my last e-mail looks like this: =WYSZUKAJ.PIONOWO(A1; Table1!A:B; 2; FASZ) It is a bit confusing (stupid) for me that I cannot use english names of formulas in cells in worksheets in polish version of Excel. The rather that I have to use english names of formulas when I write macros in Excel PL. Thanks again -- ============================== Janusz Pawlinka |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to extract information from one table to another one
Thanks so much, Janusz and David! It finally worked out by using following
formula: =VLOOKUP('1'!A1, '2'!A:B,2,FALSE) Omics "Janusz Pawlinka" wrote: U¿ytkownik "David Biddulph" <groups [at] biddulph.org.uk napisa³ w wiadomo¶ci ... You'll need to use either commas or semi-colons, depending on what you use as your list separator (specfied in Windows Regional Options), but not a mixture of the two. :-( Of course. Thanks for help and explaining. I use polish version of Excel (with semi-colons as separators), but I must 'translate' formulas from polish to 'normal' (I mean english/US) version to show its here. In polish version of Excel formula from my last e-mail looks like this: =WYSZUKAJ.PIONOWO(A1; Table1!A:B; 2; FA£SZ) It is a bit confusing (stupid) for me that I cannot use english names of formulas in cells in worksheets in polish version of Excel. The rather that I have to use english names of formulas when I write macros in Excel PL. Thanks again -- ============================== Janusz Pawlinka |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extract Information to another worksheet | Excel Worksheet Functions | |||
How do I extract information from these websites onto Excel? | Excel Discussion (Misc queries) | |||
Extract information from the list | Excel Worksheet Functions | |||
Extract information from worksheet | Excel Discussion (Misc queries) | |||
Please help: Extract some information from a cell | Excel Discussion (Misc queries) |