Bring data from source sheet in to separate columns in data sheet

I have a source sheet with >250 rows with each row having a unique [Criteria]. We assign [Status] and [Approval] status to each row that changes in a regular basis.

Demo of source sheet:

Given the number of rows, I would like to improve visibility of the changing status's in new sheet that will bring in the [Criteria] and [Approval] based upon the status assignment, as per the demo "structured sheet" below.

Demo of "structured sheet" (Desired state)

The [Criteria] e.g. "Row 1" can change from "Status 1", to Status 2, 3, 4 and so I am looking for any ideas to bring in the [Criteria] from the source sheet into the associated status columns bases upon the [Status] assignments.

All of the rows in [Criteria] in source sheet are unique.

Grateful for any ideas,

Protonsponge

Find more posts tagged with

Accepted answers

Paul Newcome

You should be able to use an INDEX/COLLECT to bring the "Row #" over then an INDEX/MATCH to bring the approval status over.

You will need a helper column (called "Number" in this example), and you would pre-fill this column starting with 1 in row 1 and going on down until you have enough numbers to cover as many rows as you think you will need. I would build this with enough rows to cover every "Row #" being in the same Status, and I would also suggest a buffer.

If your source sheet has 250 rows, I would go to at least 300. You can go further than that if you think you need the scale, and there are other solutions that can be explored to manage getting more rows added if you start running out.

Once you have the [Number] column set up, you would use an INDEX/COLLECT to bring over the "Row #" data.

=IFERROR(INDEX({Source Sheet Criteria Column}, {Source Sheet Status Column}, @cell = "Status 1"), Number@row), "")

Then an INDEX/MATCH to get the Approval on over.

=IFERROR(INDEX({Source Sheet Approval Column}, MATCH([Status 1]@row, {Source Sheet Criteria Column}, 0)), "")

You will need to update each of the formulas for the other statuses, but that is as simple as changing "Status 1" to "Status 2" in the first formula and [Status 1]@row to [Status 2]@row in the second formula.

All comments

Paul Newcome

You should be able to use an INDEX/COLLECT to bring the "Row #" over then an INDEX/MATCH to bring the approval status over.

Once you have the [Number] column set up, you would use an INDEX/COLLECT to bring over the "Row #" data.

=IFERROR(INDEX({Source Sheet Criteria Column}, {Source Sheet Status Column}, @cell = "Status 1"), Number@row), "")

Then an INDEX/MATCH to get the Approval on over.

=IFERROR(INDEX({Source Sheet Approval Column}, MATCH([Status 1]@row, {Source Sheet Criteria Column}, 0)), "")

Protonsponge

@Paul Newcome - thank you very much, I was thinking use of INDEX/COLLECT might give me a spread of data across the sheet based matching unique IDs across both sheet and so grateful for this upskilling.

Thank you very much, added to my knowledge bank!

Paul Newcome

Happy to help. 👍️

Although I am not sure where the COLLECT function went. I specifically remember typing it in there.

Here is a corrected first formula:

=IFERROR(INDEX(COLLECT({Source Sheet Criteria Column}, {Source Sheet Status Column}, @cell = "Status 1"), Number@row), "")

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.