#1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7,247
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match two values Anna Wood Excel Worksheet Functions 2 February 10th 09 09:32 PM
how can we get unique values in match function for same match key. Xcel[_2_] Excel Worksheet Functions 11 December 7th 07 09:13 PM
Match Values in Rows with Partial Values in Columns ryguy7272 Excel Worksheet Functions 3 August 8th 07 05:14 PM
How do I match values in one spreadsheet to values in another? David Excel Worksheet Functions 2 August 15th 06 03:30 PM
How to match values in one column to values in another? trib Excel Worksheet Functions 3 July 25th 06 08:30 AM


All times are GMT +1. The time now is 07:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"