Returning Multiple Rows Matching the Same Value and Skipping Rows that Don't

KKathol

Below is what I'm trying to do and I hope someone can help me figure out the best way to do this.

Here is my source sheet, Proposals:

I would like a formula to look for any "Won" values from the Status column in the source sheet and return them into the Project Name column of my destination sheet (below).

I would like the result in my destination sheet, Project Wins Master List, to be a list of only the proposals that have been "Won". So, I also need to have something in the formula to skip any rows that do not have the "Won" value in the Status column.

If we add more rows to the source sheet, is there a way to also search those new rows and add them to the destination sheet if they are "Won"?

Jason Tarpinian

I gave out plenty of candy, and had enough leftover for myself to enjoy 😊

If you have Datamesh, build a report filtered to "Won" and do a Copy+Add to the target sheet. If not, follow my fun steps below!

Since you want to add new data to the target sheet, you'll need an intermediate sheet. The reason being, is you can't just say the first project that is "Won" is the first row on the source. Example: Row 5 is awarded "Won" in the morning, and is now on your target sheet as row 1, but in the afternoon Row 2 is awarded as "Won". But the problem is you already started tracking data in row 1 that is now aligned with the wrong project. So you can't just say give me the n'th project won from the source. Here's the workaround I've used.

On your source sheet, create an automation that copies row to "Intermediate Sheet" triggered when the "Status" changes to "Won". You never need to look at this intermediate sheet after it's built, it is solely there to make sure that the project order never changes. That way when you look up data from your target sheet, Row 5 (in my example above) is always going to be project #1/row 1 no matter what.

On your target sheet, you'll need a couple helper columns. Use the system column # Auto number (I call "Row ID") and then build this formula in a "Row #" column =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)

Now you can pull in your n'th project names using the INDEX/COLLECT with the Row #. It is important that the "Project Name" pulls in from the intermediate sheet, because you want to make sure the n'th "Project Name" is always the same. =IFERROR(INDEX(COLLECT([Project Name (Intermediate)]:[Project Name (Intermediate)], [Status (Intermediate)]:[Status (Intermediate)], "Won"), [Row #]@row), "")

To pull in any other data from the source sheet, use a similar INDEX/COLLECT formula, but this time pull from the actual source, not the intermediate, so you have the live data. =INDEX(COLLECT([Project Number (Source)]:[Project Number (Source)], [Project Name (Source)]:[Project Name (Source)], [Project Name (Target)]@row), 1)

Now you have Project Names and other source data on a 2nd sheet, and you can add all the additional columns that you'd like to track along with that. If projects ever need to be removed from the target sheet, the process would be to delete the row on the Target & the Intermediate sheet to make sure that all of your data stays aligned.

Jason Tarpinian

Could you either just make a report that is filtered to wins, or an automation that copies a row over when the "Status" changes to Won?

There is a way to do it with formulas, but I don't have enough time to write that out currently as I need to get ready for my trick or treaters! Just @ me tomorrow if you'd like me to write out the formula answer if the above doesn't work.

KKathol

Hi @Jason Tarpinian, thank you! Hope trick or treating was a hit!

The only reason a report won't work in this situation is that we want to add additional columns of information once a project is won.

We also don't want to pull whole rows over to the new sheet because the source sheet contains information that we don't need or want on the destination sheet with project wins data.

We could probably figure out a roundabout way to make one of those options work, but ideally, we'd like to have a formula that only pulls over the information we want into a new sheet.

Jason Tarpinian

I gave out plenty of candy, and had enough leftover for myself to enjoy 😊

If you have Datamesh, build a report filtered to "Won" and do a Copy+Add to the target sheet. If not, follow my fun steps below!

Since you want to add new data to the target sheet, you'll need an intermediate sheet. The reason being, is you can't just say the first project that is "Won" is the first row on the source. Example: Row 5 is awarded "Won" in the morning, and is now on your target sheet as row 1, but in the afternoon Row 2 is awarded as "Won". But the problem is you already started tracking data in row 1 that is now aligned with the wrong project. So you can't just say give me the n'th project won from the source. Here's the workaround I've used.

On your source sheet, create an automation that copies row to "Intermediate Sheet" triggered when the "Status" changes to "Won". You never need to look at this intermediate sheet after it's built, it is solely there to make sure that the project order never changes. That way when you look up data from your target sheet, Row 5 (in my example above) is always going to be project #1/row 1 no matter what.

On your target sheet, you'll need a couple helper columns. Use the system column # Auto number (I call "Row ID") and then build this formula in a "Row #" column =MATCH([Row ID]@row, [Row ID]:[Row ID], 0)

Now you can pull in your n'th project names using the INDEX/COLLECT with the Row #. It is important that the "Project Name" pulls in from the intermediate sheet, because you want to make sure the n'th "Project Name" is always the same. =IFERROR(INDEX(COLLECT([Project Name (Intermediate)]:[Project Name (Intermediate)], [Status (Intermediate)]:[Status (Intermediate)], "Won"), [Row #]@row), "")

To pull in any other data from the source sheet, use a similar INDEX/COLLECT formula, but this time pull from the actual source, not the intermediate, so you have the live data. =INDEX(COLLECT([Project Number (Source)]:[Project Number (Source)], [Project Name (Source)]:[Project Name (Source)], [Project Name (Target)]@row), 1)

Now you have Project Names and other source data on a 2nd sheet, and you can add all the additional columns that you'd like to track along with that. If projects ever need to be removed from the target sheet, the process would be to delete the row on the Target & the Intermediate sheet to make sure that all of your data stays aligned.

KKathol

@Jason Tarpinian this is working beautifully. Thank you very much for sharing your knowledge!

Jason Tarpinian

No problem, glad to help!