Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match 2 values
Hi
I have 1 table with data received from a customer. There I have Storenumber, Articlenumber and units in stock. I wan't to match this data with a table with a list of articlenumbers and how many units the need to reorder. Table 1 looks like this Custno Artno Units 101 201 3 101 202 2 101 203 9 102 201 1 102 202 2 102 204 5 103 205 3 103 201 5 103 202 3 Table 2 looks like this Custno Artno Units 101 201 101 203 102 201 102 203 103 201 103 203 I want the result to look like this Custno Artno Units 101 201 3 101 203 9 102 201 1 102 203 0 103 201 5 103 203 0 If there are no value of a spesific article I wan't 0 returned. Is this possible? Vlookup and match only generates errors Eirik |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match 2 values
Suppose you have the below data in D:F in the same sheet where you have Table1
Custno Artno Units 101 201 x 101 203 x 102 201 x 102 203 x 103 201 x 103 203 x replace x with the below formula in F2 and copy that down as required =SUMPRODUCT(--($A$1:$A$100=D2),--($B$1:$B$100=E2),--($C$1:$C$100)) If this post helps click Yes --------------- Jacob Skaria "Lupus" wrote: Hi I have 1 table with data received from a customer. There I have Storenumber, Articlenumber and units in stock. I wan't to match this data with a table with a list of articlenumbers and how many units the need to reorder. Table 1 looks like this Custno Artno Units 101 201 3 101 202 2 101 203 9 102 201 1 102 202 2 102 204 5 103 205 3 103 201 5 103 202 3 Table 2 looks like this Custno Artno Units 101 201 101 203 102 201 102 203 103 201 103 203 I want the result to look like this Custno Artno Units 101 201 3 101 203 9 102 201 1 102 203 0 103 201 5 103 203 0 If there are no value of a spesific article I wan't 0 returned. Is this possible? Vlookup and match only generates errors Eirik |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match 2 values
Hi
The data are located in 2 worksbooks. Book1 and Book2. I need the data units from Book1 inserted into Book2. Book1 contains about 50 000 rows of data. Tried to add Book1! to the formula but excel came back with an error. =SUMPRODUCT(--(book1!$A$1:$A$50000=D2),--(book1!$B$1:$B$100=E2),--($C$1:$C$100)) I inserted this into C2 Custno=A, Artno=B, Units=C in both workbooks Hope this additional info is helpfull. Eirik "Jacob Skaria" wrote: Suppose you have the below data in D:F in the same sheet where you have Table1 Custno Artno Units 101 201 x 101 203 x 102 201 x 102 203 x 103 201 x 103 203 x replace x with the below formula in F2 and copy that down as required =SUMPRODUCT(--($A$1:$A$100=D2),--($B$1:$B$100=E2),--($C$1:$C$100)) If this post helps click Yes --------------- Jacob Skaria "Lupus" wrote: Hi I have 1 table with data received from a customer. There I have Storenumber, Articlenumber and units in stock. I wan't to match this data with a table with a list of articlenumbers and how many units the need to reorder. Table 1 looks like this Custno Artno Units 101 201 3 101 202 2 101 203 9 102 201 1 102 202 2 102 204 5 103 205 3 103 201 5 103 202 3 Table 2 looks like this Custno Artno Units 101 201 101 203 102 201 102 203 103 201 103 203 I want the result to look like this Custno Artno Units 101 201 3 101 203 9 102 201 1 102 203 0 103 201 5 103 203 0 If there are no value of a spesific article I wan't 0 returned. Is this possible? Vlookup and match only generates errors Eirik |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match 2 values
Assume the following: Your Table 1 is in C6:E14. You second table is in C20:D25. Enter the following formula in E20 and fill down to E25. =SUMPRODUCT(--($C$6:$C$14=C20),--($D$6:$D$14=D20),$E$6:$E$14) This will return the sum of the values in E6:E14 where the corresponding value in C6:C14 is equal to the value in C20 and the corresponding value in D6:D14 is equal to the value in D20. Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Mon, 8 Jun 2009 06:16:01 -0700, Lupus wrote: Hi I have 1 table with data received from a customer. There I have Storenumber, Articlenumber and units in stock. I wan't to match this data with a table with a list of articlenumbers and how many units the need to reorder. Table 1 looks like this Custno Artno Units 101 201 3 101 202 2 101 203 9 102 201 1 102 202 2 102 204 5 103 205 3 103 201 5 103 202 3 Table 2 looks like this Custno Artno Units 101 201 101 203 102 201 102 203 103 201 103 203 I want the result to look like this Custno Artno Units 101 201 3 101 203 9 102 201 1 102 203 0 103 201 5 103 203 0 If there are no value of a spesific article I wan't 0 returned. Is this possible? Vlookup and match only generates errors Eirik |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match 2 values
In the other workbook try the below
with A2 = 101 B2 = 201 =SUMPRODUCT(--([Book1]Sheet1!$A$1:$A$50000=A2),--([Book1]Sheet1!$B$1:$B$50000=B2),--([Book1]Sheet1!$C$1:$C$50000)) (formula in one line) If this post helps click Yes --------------- Jacob Skaria "Lupus" wrote: Hi The data are located in 2 worksbooks. Book1 and Book2. I need the data units from Book1 inserted into Book2. Book1 contains about 50 000 rows of data. Tried to add Book1! to the formula but excel came back with an error. =SUMPRODUCT(--(book1!$A$1:$A$50000=D2),--(book1!$B$1:$B$100=E2),--($C$1:$C$100)) I inserted this into C2 Custno=A, Artno=B, Units=C in both workbooks Hope this additional info is helpfull. Eirik "Jacob Skaria" wrote: Suppose you have the below data in D:F in the same sheet where you have Table1 Custno Artno Units 101 201 x 101 203 x 102 201 x 102 203 x 103 201 x 103 203 x replace x with the below formula in F2 and copy that down as required =SUMPRODUCT(--($A$1:$A$100=D2),--($B$1:$B$100=E2),--($C$1:$C$100)) If this post helps click Yes --------------- Jacob Skaria "Lupus" wrote: Hi I have 1 table with data received from a customer. There I have Storenumber, Articlenumber and units in stock. I wan't to match this data with a table with a list of articlenumbers and how many units the need to reorder. Table 1 looks like this Custno Artno Units 101 201 3 101 202 2 101 203 9 102 201 1 102 202 2 102 204 5 103 205 3 103 201 5 103 202 3 Table 2 looks like this Custno Artno Units 101 201 101 203 102 201 102 203 103 201 103 203 I want the result to look like this Custno Artno Units 101 201 3 101 203 9 102 201 1 102 203 0 103 201 5 103 203 0 If there are no value of a spesific article I wan't 0 returned. Is this possible? Vlookup and match only generates errors Eirik |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match two values | Excel Worksheet Functions | |||
how can we get unique values in match function for same match key. | Excel Worksheet Functions | |||
Match Values in Rows with Partial Values in Columns | Excel Worksheet Functions | |||
How do I match values in one spreadsheet to values in another? | Excel Worksheet Functions | |||
How to match values in one column to values in another? | Excel Worksheet Functions |