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?
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
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.
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
Are the invoices numbers are really same in your case...?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Using Pivot Table and the appropriate filters combined with the Field Setting of "Show Items with no data" will provide your invoice(s) defined.
 
Upvote 0
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) ?
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top