0

I was trying to use Query on this, but read that query doesn't work well in a column with mixed data. What I have is a financial spreadsheet where Col3 either has the words DEBIT or a check number. What I want to do is pull into separate spreadsheet certain columns (Col2,Col3,Col7,Col14,Col15) when Col3 = a five digit number but only when Col2 (date column) is between April 1, 2024 and April 30, 2024. Is there an easy way to do this?

Extra credit if you can also show me how I can pull the same columns from the same date range when the check number has less than five letters (outgoing checks are always five digits and incoming checks are less than five, so I could easily filter into different spreadsheets check income and check debits..)

Any help would be much appreciated!

2
  • Sample data: docs.google.com/spreadsheets/d/… Sample outcome: docs.google.com/spreadsheets/d/…
    – Salt_Tea
    Commented Apr 30 at 22:42
  • 1
    1. External links can provide additional context but questions should be self-contained wherever possible so they continue to be useful to future readers after the external references no longer work. 2. Comments are used to request clarification and are temporary. Relevant information should always be an edit to the question.
    – Blindspots
    Commented Apr 30 at 23:35

1 Answer 1

-1
=CHOOSECOLS(
    FILTER(
      A:O, REGEXMATCH(""& C:C, "\d{5}"),
      B:B > "4/1/2024" * 1), B:B <= "4/30/2024" * 1)
    2, 3, 7, 14, 15)
  1. FILTER returns an array of rows from Column A to O based on the following conditions:
    1. Column C must contain a 5-digit number, tested using REGEXMATCH.
    2. The date in Column B must be within the month of April 2024.
  2. CHOOSECOLS returns only the identified columns from the array.

Notes

CHOOSECOLS  FILTER  REGEXMATCH

  1. REGEXMATCH expects strings but the cheque numbers may be number-formatted. To avoid errors an empty string is prepended to the values in Column C to coerce any existing numbers to text: ""& C:C.
  2. Column B contains date formatted numbers. If hardcoding the comparison dates in the formula as strings, as was done in the formula above, the date strings are multiplied by 1 to coerce them to numbers, eg. "4/1/2024" * 1. This is equivalent to wrapping them in the DATEVALUE function but with less typing: DATEVALUE("4/1/2024" )=("4/1/2024" * 1). If, instead of hardcoded values you use references to cells containing dates, coercing the dates to numbers is unnecessary. for example,
=CHOOSECOLS(
    FILTER(
      A:O, REGEXMATCH(""& C:C, "\d{5}"),
      B:B > P1, B:B <= Q1)
    2, 3, 7, 14, 15)
2
  • 1
    Thank you very much for this! Does this work with importrange? I need to be able to pull the data from a tab in another spreadsheet.
    – Salt_Tea
    Commented Apr 30 at 22:19
  • That might change things. You should try it out and if you run into any issues ask a new question about that. You can link back to this one for context if helpful. Be sure to note in your question any other details that might be useful, such as whether it is your spreadsheet or someone else's and the rough number of rows in the target spreadsheet, and of course a sample of the formula that isn't working.
    – Blindspots
    Commented Apr 30 at 23:41

Your Answer

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

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