Excel many to many

[cdiogo]

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?

 

[Kemmuniemans]

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]

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]

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 :

=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]

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

 

[cdiogo]

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 :

=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]

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]

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]

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]

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) ?