1

Context:

I have a huge spread-sheet such that rows/records are considered "jobs," where columns are aspects of the job like location. One such column P, titled File-Name, that lists the pdf-file-names of invoices. This contains duplicates as some jobs are combined into one invoice. EX: 6022.pdf

I have another spread-sheet, Invoice Submission Summary, whose only purpose is to behave as a report to grab information from the Jobs-spread-sheet.

The way I go about doing this is by obtaining a list of all the file-names of invoices in the invoice-folder on my Windows computer. Then pasting them into a range on the Invoice Submission Summary spread-sheet (a separate spreadsheet from the jobs one). I then use a filter-formula that uses the pasted-range as criteria for the P column in the Jobs-spread-sheet.

Problem:

In the Invoice Submission Summary spread-sheet I have to reference every cell individually in the pasted pdf invoice-name range inside of the filter formula. ( Because of this it is 1800+ characters long now and it's also very slow.

Goal:

I want to rewrite the formula to be less complex, and not require to be updated every time I paste a longer list. Or find an alternative way to accomplish generating a report for printing purposes from a different spread-sheet.

Improve this question
1
  • If you need more help at this point, a model spreadsheet with a not-huge set of data is called for.
    – user79865
    Jul 29, 2016 at 20:52

1 Answer 1

Reset to default
3

To filter based on comparison against a large array, I would put that array into the spreadsheet (say, column Z, or somewhere on another sheet) and then use the filter based on match function:

=filter(A2:P, match(P2:P, Z1:Z, 0))

Here, match returns a positive number when a cell value is found in column Z, and returns #N/A! otherwise. The filter interprets positive numbers as "yes, keep this row" and errors as "no, do not keep".

Improve this answer
3
  • I tried this and it is having a mismatch that can only be resolved by inserting enough rows in the "Invoice Submission Summary" sheet to match the # of rows in the "Jobs" sheet. Ver Batum error: "FILTER has mismatched range sizes. Expected row count: 8239. column count: 1. Actual row count: 1, column count: 1." When I add enough rows to equal 8239 it works.
    – Xzila
    Jul 29, 2016 at 20:15
  • Perhaps I didn't understand your question, but no padding should be needed to apply Filter. There are three ranges here: what to filter, the column by which to filter, and the range with acceptable values. The first two must have the same number of rows, the third can be of any size. And the reason for the first two to have the same number of rows is that you accept or reject a row based on an entry in that row.
    – user79865
    Jul 29, 2016 at 20:19
  • I just think the match formula is not array friendly for some reason. I changed it to a vlookup & it worked. I just realized a problem is that the invoice-file-names do contain duplicates ... they are not entirely unique, because some jobs are combined on the same invoice. :(
    – Xzila
    Jul 29, 2016 at 20:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.