Question about rolling up data from multiple sheets into a new sheet

KEdler

Hi,

Let me start by saying I am not that great with formulas but can usually figure things out. I have a new project I am working on and think I can use some expert suggestions.

In this project, I have a source sheet containing contact information which is being consistently updated for approximately 270 organizations. I have another sheet, built from entering data via a form that contains specific activities each organization undertakes. On a monthly basis, I would like to create a seamless way to merge the two forms to more or less creates a sheet I can use for mail merge.

I am doing these activities in the .gov environment and really cannot ask for any add-ons to help with this work. My main worry is making sure I can get these sheets combined with current data on a rolling basis.

For reference, first sheet has about 270 rows and 30 columns. The second sheet has an undefined number of rows and 26 columns. Yes, there are two fields that contain a unique identifier (they are not currently linked but that is because I have exploring all options.)

Any help is appreciated.

Jake Gustafson

So you've got two sheets that have new and updated data added to them, so you've got to have something dynamic. I believe where @Mike TV was going is that you'd use those common fields to leverage INDEX/MATCH to populate the additional columns.

Where I'm struggling is where do you start? If you create a 3rd sheet, then how are you populating it with new records and keeping them updated? I think if you added the 30 columns (or what you need of those 30) to the 2nd sheet with 27 columns, that would attach your dynamic contact info to the unique tasks.

The way I think about INDEX/MATCH is as follows:

=INDEX(<what value I want to show up in my column>, MATCH(<the value I want to look up>, <the range I'm looking up that previous value in>, 0))

So, this might be close to what you're going to need to do, applying it to many columns.

=INDEX({Chair E-mail Address Sheet 2}, MATCH([Grantee Code]@row, {Grantee Code Sheet 1), 0))

This would populate the Chair E-mail Address from Sheet 2 for the Grantee Code listed on the row in Sheet 1.

Mike TV

@KEdler

Can I get a look at the column names you have on both sheets? I don't need to see any data in any rows, just column header names. Also, can you point out which columns on the 2 sheets would have the exact same data in their columns? I'm sure there's a way we can build a 3rd sheet to copy row and index match and merge data between the two.

KEdler

Thanks for responding. I was driving and could not respond sooner. There are two common fields, the Name of the Organization and the Grantee Code.

Here is the columns for the first form

And this is the second. I hope you can read this. Any help would be appreciated.

Thanks

Jake Gustafson

So you've got two sheets that have new and updated data added to them, so you've got to have something dynamic. I believe where @Mike TV was going is that you'd use those common fields to leverage INDEX/MATCH to populate the additional columns.

Where I'm struggling is where do you start? If you create a 3rd sheet, then how are you populating it with new records and keeping them updated? I think if you added the 30 columns (or what you need of those 30) to the 2nd sheet with 27 columns, that would attach your dynamic contact info to the unique tasks.

The way I think about INDEX/MATCH is as follows:

=INDEX(<what value I want to show up in my column>, MATCH(<the value I want to look up>, <the range I'm looking up that previous value in>, 0))

So, this might be close to what you're going to need to do, applying it to many columns.

=INDEX({Chair E-mail Address Sheet 2}, MATCH([Grantee Code]@row, {Grantee Code Sheet 1), 0))

This would populate the Chair E-mail Address from Sheet 2 for the Grantee Code listed on the row in Sheet 1.