Can I pull in information from one sheet to another when there are multiple matches?

tmd1993

I have a budget tracking process I would like some help setting up. See steps below:

1. Employees fill out a budget request form for office events. On this form, they state the name of the event they are hosting, which office they represent, and how much they are requesting from a pot of funds. This is stored in the Primary Sheet. I have this working without issue.
2. Here is where I need help: I would then like the Event Name to automatically be pulled into a Secondary Sheet, where I can then add in the dollar amount they were approved for based on the event. I set up calculations to track from there how much funds we have left by office. The problem I am running into is figuring out how to pull over all the data from Primary sheet to Secondary sheet. So far I have:
   1. Created a helper column of our office locations to use as the "unique identifier" in the Secondary Sheet, and I am also using office location as a 'parent' column so we can see how much spend comes from each office.
   2. I then used an Index/Match function so I can pull over the event name and other important information from the Primary Sheet. However, while Event 1 for an office appears, I cannot get Event 2 to pull over. To my understanding, this is because Match/Index can only return the first value that matches the criteria. This is a problem because multiple people from each office can request funds, I need to be able to pull over Event 1 that happened in Chicago and on a separate line also pull in Chicago's Event 2.
      1. (I am emphasizing separate line because Index/Join won't work for me if it will all end up on the same cell/row)

In sum, I want to pull information from my Primary sheet into my Secondary sheet, but I need to do this for data that will have multiple matches. Attaching an example of what I mean, if helpful. Any tips?

SS Help.docx

Georgie

Hi @tmd1993,

Index/Match functions require an identifier that’s unique to each row, and that unique identifier must be present in both the source and destination sheets. As you’ve seen, since you’re using the location as the unique identifier, but the location appears multiple times in the primary sheet, you’re only getting the first row returned. 

We can use additional helper columns and formulas in order to create unique references and pull in the relevant rows for each location. Here’s how I’d do this:

Add 3 helper columns in your Primary Sheet, as follows:

• Auto: Auto-number column
• Row ID: Text/number with column formula below
  • =MATCH(Auto@row, Auto:Auto, 0)
• Child number: Text/number with column formula below
  • =COUNTIFS(Location:Location, @cell = Location@row, [Row ID]:[Row ID], @cell <= [Row ID]@row)

Your primary sheet should then look similar to this:

Add 4 helper columns in your Secondary Sheet, as follows:

• Auto: Auto-number column
• Row ID: Text/number with column formula below
  • =MATCH(Auto@row, Auto:Auto, 0)
• Parent: Text/number with column formula below
  • =PARENT([Primary Column]@row)
• Child number: Text/number with column formula below
  • =IF(Parent@row <> "", RANKEQ([Row ID]@row, COLLECT([Row ID]:[Row ID], Parent:Parent, Parent@row), 1))

Once all your helper columns have been set up and you’ve saved both sheets, create an “Event” column in the secondary sheet so that you can create a column formula (separate to the column where your locations are listed). Use the following formula in the Event column:

• =IFERROR(INDEX(COLLECT({Sheet 1 | Event}, {Sheet 1 | Location}, PARENT([Primary Column]@row), {Sheet 1 | Child number}, [Child number]@row), 1), "")

You’ll need to create the cross-sheet references (the sections in curly brackets {}) as you create the formula - each of them will be the corresponding column in the primary sheet, and you can rename your references as desired. Your secondary sheet should then look similar to below:

You can find out more about INDEX(COLLECT) and other combinations of functions here: Formula combinations for cross sheet references. 

I’d also recommend taking a look at the following two Community threads which use some of the formulas I’ve used in this solution:

• Child Auto Numbering per Parent Row
• Number of duplicates

Does that work for you?

Georgie

Erin Horiuchi Green

Hello @tmd1993 ,

It sounds like your Primary sheet is functioning as intended.

Your Secondary sheet challenges sound two-fold:

1) Identify unique Event by Office Location.

2) Aggregate Budget vs Spend by Office Location.

Have you thought of using the Pivot App? Learning Track Pivot App

This is not the only option.

Primary

Pivot App Setup

Test New Entry Pivot App Update Response SLA

tmd1993

https://community.smartsheet.com/discussion/comment/474773#Comment_474773

Erin, thank you so much for taking the time to walk through that process with me. Unfortunately, my company has not purchased the extra license to use Pivot and I cannot make a business case to do so for this one purpose. I see you mentioned that this is not the only option — is there any other that comes to mind I can dive into?

Georgie

Hi @tmd1993,

Index/Match functions require an identifier that’s unique to each row, and that unique identifier must be present in both the source and destination sheets. As you’ve seen, since you’re using the location as the unique identifier, but the location appears multiple times in the primary sheet, you’re only getting the first row returned. 

We can use additional helper columns and formulas in order to create unique references and pull in the relevant rows for each location. Here’s how I’d do this:

Add 3 helper columns in your Primary Sheet, as follows:

• Auto: Auto-number column
• Row ID: Text/number with column formula below
  • =MATCH(Auto@row, Auto:Auto, 0)
• Child number: Text/number with column formula below
  • =COUNTIFS(Location:Location, @cell = Location@row, [Row ID]:[Row ID], @cell <= [Row ID]@row)

Your primary sheet should then look similar to this:

Add 4 helper columns in your Secondary Sheet, as follows:

• Auto: Auto-number column
• Row ID: Text/number with column formula below
  • =MATCH(Auto@row, Auto:Auto, 0)
• Parent: Text/number with column formula below
  • =PARENT([Primary Column]@row)
• Child number: Text/number with column formula below
  • =IF(Parent@row <> "", RANKEQ([Row ID]@row, COLLECT([Row ID]:[Row ID], Parent:Parent, Parent@row), 1))

Once all your helper columns have been set up and you’ve saved both sheets, create an “Event” column in the secondary sheet so that you can create a column formula (separate to the column where your locations are listed). Use the following formula in the Event column:

• =IFERROR(INDEX(COLLECT({Sheet 1 | Event}, {Sheet 1 | Location}, PARENT([Primary Column]@row), {Sheet 1 | Child number}, [Child number]@row), 1), "")

You’ll need to create the cross-sheet references (the sections in curly brackets {}) as you create the formula - each of them will be the corresponding column in the primary sheet, and you can rename your references as desired. Your secondary sheet should then look similar to below:

You can find out more about INDEX(COLLECT) and other combinations of functions here: Formula combinations for cross sheet references. 

I’d also recommend taking a look at the following two Community threads which use some of the formulas I’ve used in this solution:

• Child Auto Numbering per Parent Row
• Number of duplicates

Does that work for you?

Georgie

Erin Horiuchi Green

Hello @Georgie ,

I am trying to replicate your solution because its creative and I want to continue learn new approaches.

Parent only generated after manually creating child rows in the Primary Column; yellow highlights.

I get an error with your formula for Child Number. Am I missing something?

Suggested =IF(Parent@row <> "", RANKEQ([Row ID]@row, COLLECT([Row ID]:[Row ID], Parent:Parent, Parent@row), 1))

I tried completing the IF formula further, but still get the #BLOCKED error.

Amended =IF(Parent@row <> "", RANKEQ([Row ID]@row, COLLECT([Row ID]:[Row ID], Parent:Parent, Parent@row), 1),"")

Georgie

Hi @Erin Horiuchi Green,

That's strange - I can't see any reason why that wouldn't work in your sheet. If you copied and pasted the formula, try typing it out to see if that resolves the issue.

Georgie

tmd1993

https://community.smartsheet.com/discussion/comment/474820#Comment_474820

@Georgie thank you SO much. This worked perfectly! I was not even close so you are a life saver :)

Georgie

@tmd1993,

Happy to help - that one took me a while, so I'm glad to hear it worked for you!

Georgie