Excel many to many

cdiogo

New Member
Joined
Sep 24, 2015
Messages
5
How can I in Excel, resolve this question about this table:


Invoice Number | Item | Product | Value
1001 | 1 | potatoes | 100
1001 | 2 | tomatoes | 150
1002 | 1 | potatoes | 100
1003 | 1 | water | 50
1004 | 1 | potatoes | 100
1004 | 2 | onions | 120


If I ask the questions:


Which an how many of these invoices have no potatoes or tomatoes ?
the answer should be, in this case: invoice 1003 / number = 1


How can I find it in Excel? Is there a formula for this question?
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Kemmuniemans

Board Regular
Joined
Feb 25, 2013
Messages
57
I'd just use autofilter on this and as a filter for the product set: <> Potatoes and <> tomatoes

If you don't know how to use autofilter, just google it.
 

cdiogo

New Member
Joined
Sep 24, 2015
Messages
5
I'd just use autofilter on this and as a filter for the product set: <> Potatoes and <> tomatoes

If you don't know how to use autofilter, just google it.


---
Hi, thanks for quick answer, but it doesn't work properly.
If I filter for products which are not "coffee" or "water", the result gives all the items that are not those. But the problem is that I'm asking for invoices. And it gives me back the items of the same invoices that have coffee and water...
I want to now the Invoices with no Water and no Coffee...(not items but invoices) which can have 1 or more items...
 

Kemmuniemans

Board Regular
Joined
Feb 25, 2013
Messages
57
You could try adding a column in the end with formula's which checks if on the invoice there is a line (or even how many lines there are which contain potatoes or tomatoes.)

In Excel 2007 and onwards would be along the lines of this (taking your example into account, when I Put it Columns A to D and Rows 1 to 7 :

Code:
=CountIF($A$2:$A$7;A2)-CountIFS($A$2:$A$7;A2;$C$2:$C$7;"<>tomatoes";$C$2:$C$7;"<>potatoes")

This will give the count of rows with current invoice number - Count of rows with current invoice number and where the item is not potatoes and not tomatoes.
So if on the invoice there is an item containing tomatoes or potatoes, the value of the formula will be positive.
If no tomatoes or potatoes on the invoice the value will be 0.

So filter the data on the formula column to be 0.
 

proficient

Well-known Member
Joined
Apr 10, 2012
Messages
716
Office Version
  1. 2016
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Are the invoices numbers are really same in your case...?
 

cdiogo

New Member
Joined
Sep 24, 2015
Messages
5
You could try adding a column in the end with formula's which checks if on the invoice there is a line (or even how many lines there are which contain potatoes or tomatoes.)

In Excel 2007 and onwards would be along the lines of this (taking your example into account, when I Put it Columns A to D and Rows 1 to 7 :

Code:
=CountIF($A$2:$A$7;A2)-CountIFS($A$2:$A$7;A2;$C$2:$C$7;"<>tomatoes";$C$2:$C$7;"<>potatoes")

This will give the count of rows with current invoice number - Count of rows with current invoice number and where the item is not potatoes and not tomatoes.
So if on the invoice there is an item containing tomatoes or potatoes, the value of the formula will be positive.
If no tomatoes or potatoes on the invoice the value will be 0.

So filter the data on the formula column to be 0.

Hello,

I think I get ur idea, but there's one thing I don't understand...why point to A2 ? (wich will be cell with '1001') ?
And also, the answer to my question is Invoice 1003 so the number o count is 1 not 0
 

cdiogo

New Member
Joined
Sep 24, 2015
Messages
5

ADVERTISEMENT

Are the invoices numbers are really same in your case...?

The invoices can have multiples items ( is the typical case ONE to MANY):

invoice 1001 has item 1 and item 2 (potatoes and tomatoes)
 

Kemmuniemans

Board Regular
Joined
Feb 25, 2013
Messages
57
Because you have to put in the formula for every row, the formula I gave would be put into Cell E2.
This formula can be dragged down, so in cell E5, the formula would be the same, except the A2 would be A5.
In E5, the result of the formula will be 0. So if you filter the table on the formula column to only show rows for which the formula gives 0, you get your desired rows.

If you just want the count of invoices that have no tomatoes and potatoes on it, then you need another formula, but that's not what you asked for.
 

SpillerBD

Well-known Member
Joined
Jul 2, 2014
Messages
2,707
Using Pivot Table and the appropriate filters combined with the Field Setting of "Show Items with no data" will provide your invoice(s) defined.
 

cdiogo

New Member
Joined
Sep 24, 2015
Messages
5
Using Pivot Table and the appropriate filters combined with the Field Setting of "Show Items with no data" will provide your invoice(s) defined.

I don't know if it's possible.
The only way I imagine it can be possible is by using PowerPivot and relating the invoice with the products as "one to many" and then go for a filter...

Can u show me how do u solve this with only a simple pivot table (no add ins) ?
 

Watch MrExcel Video

Forum statistics

Threads
1,119,277
Messages
5,577,177
Members
412,773
Latest member
ely6678
Top