search two tables for criteria to populate third table

Hi 'gator,

You wrote: "as long as the invoice DOES NOT contain certain invoice items."

But in your example, you showed two invoices containing the same 'certain invoice item.'

In practice, will you be omitting invoices which contain one specific item, or will you be omitting invoices which contain one or more items from a list of several items?

Regards,

Barry

Hi ufgatorvet,

I have created two numbers table from our client database

Please reply with a small (anonymous) sample of your client database. I am having trouble understanding how you created Table 1 and Table 2.

Is your client database anything like this?

What are the most important columns?

What is your overall aim?

Regards,

Ian.

Hi,

I think something like this will do what you want:

Add a filter column to Table 1 with this formula in E2, copied down:

=COUNTIFS(Table 2::A,A2,Table 2::B,E$1)

The value in E$1 must be spelled exactly like the invoice item you want to use to exclude invoices.

Then filter on 0 in column E. Select the cells, command c to copy, click once in a cell in a new table and Edit > Paste Formula results.

SG

If you don't like the manual copy-pasting and want the Results table to generate and update automatically, here's a slightly fancier solution:

The formula in E2, filled down, is:

=IF(COUNTIFS(Table 2::A,A2,Table 2::B,E$1)>0,"",MAX(E$1:E1)+1)

This increments a counter each time it sees there are no matches for ITEM-NO and invoice number in Table 2.

Then you can use that counter to fill in the Results table with this formula, input into A2, and filled right and down:

=INDEX(Table 1::A,MATCH(ROW()−1,Table 1::$E,0))

The INDEX MATCH combination is simply a form of lookup. Here it looks for a match of the row number the formula is on (minus 1 because there is one Header Row) in column E of Table 1 and returns the value from column A on that same row. When you fill it right it returns the value from column B, column C, etc.

To hide the red warning triangles in cells that are out of range because there are more rows in the Results than there are matches you can wrap this formula in IFERROR, like this:

=IFERROR(INDEX(Table 1::A,MATCH(ROW()−1,Table 1::$E,0)),"")

SG

Hi Jim,

Did you see my solution above? It gives the result you want:

Add an extra column in your Table 1. Put XX in the header, and this formula in F2 and fill down.

=IF(COUNTIFS(Table 2::D,A2,Table 2::H,F$1)>0,"",MAX(F$1:F1)+1)

This is a "counter" that increments each time it sees there is no match on XX and that invoice number in Table 2.

In Table 2 add a column, and put this formula in H2, and fill down:

=LEFT(F2,2)

In the 'Results' table, the formula in A2, filled right to B2, and down, is:

=INDEX(Table 1::A,MATCH(ROW()−1,Table 1::$F,0))

The formula in C2, filled down is:

=INDEX(Table 1::E,MATCH(ROW()−1,Table 1::$F,0))

As described above, wrap those two formulas in IFERROR if you want to hide the red warning triangles when you get out of range.

Keep in mind that, unlike Excel, Numbers is not designed to handle large datasets. It's likely to get sluggish.

SG

Glad to hear you got it working on your end. Good luck with your project, and thanks for the green tick!

SG