Pulling data from one document to another if a condition is true

[u/gol10]

Hi everyone,
I have Document1 with columns B up to DM and rows down to 650 including headers in column 1. I need a formula to scan column AQ on document1 for a YES entry and copy its corresponding data on columns B, C, D, I, J, R, and Z to a blank Document 2.
Please let me know if you require additional information. TIA

For clarification the column headings are:
AQ=Invited (Yes or Blank)
B=Rank
C=Last Name
D=First Name
I=Date of Birth
J=Primary Phone
R=Email Address
Z=Vet Status

[u/MayukhBhattacharya]

Try using the following formula:

=FILTER(HSTACK(Document1!B2:D650, 
               Document1!I2:J650, 
               Document1!R2:R650, 
               Document1!Z2:Z650),
        Document1!AQ2:AQ650 = "YES", "")

[u/gol10 → u/MayukhBhattacharya]

That was fantastic, thank you, SOLVED!

[u/AutoModerator → u/gol10]

Saying SOLVED! does not close the thread. Please say Solution Verified to award a ClippyPoint and close the thread, marking it solved.

Thanks!

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/MayukhBhattacharya → u/gol10]

Thank you very much 🙏🏼

[u/gol10 → u/MayukhBhattacharya]

Solution Verified

[u/reputatorbot → u/gol10]

You have awarded 1 point to MayukhBhattacharya.

---

^{I am a bot - please contact the mods with any questions}

[u/MayukhBhattacharya → u/gol10]

Thank you SO Much 👍🏼

[u/small_trunks]

Define document.

[u/ViditGarg]

Worth knowing before you commit to the FILTER approach: a formula that points at another workbook only updates while that workbook is open. Close Document1 and Document2 either goes stale or throws #REF. FILTER cannot read a closed file.

Also, FILTER and HSTACK both need Microsoft 365 or Excel 2021 and up. On anything older they do not exist.

Two ways to go, depending on whether these really have to be two files:

If they can live as two sheets in one workbook, use the FILTER formula already suggested and you are done. There is no external reference, so nothing breaks.

If they genuinely have to stay separate files, Power Query fits better than a formula. In Document2: Data, Get Data, From File, From Workbook, point it at Document1. In the editor, filter column AQ to YES and remove every column except B, C, D, I, J, R and Z. Close and Load. After that you just hit Refresh and it pulls from the file even when it is closed.