Combining data from multiple sheets (VSTACK, etc.)

Hey all... I'm still a bit of a novice when it comes to Excel's newest dynamic functions, so I'm sure there's an answer here I just haven't figured out yet.

I have two worksheets of similar, but not exactly, structured data. My goal is to combine the data into a single "consolidated" worksheet and, ultimately, to pluck certain columns from each of the source worksheets to form this "child" table.

I've put together a simple example in the attached spreadhseet.

Note that in the 2022 and 2023 tables the data is structured the same, albeit with different #s of rows. Evan Brown appears in both tables. The goal is merge these tables into one, but have no duplicates ... and then sort them in a particular order. SORT(UNIQUE(VSTACK( does this perfectly. That's Example #1.

But in Example #2, the data isn't quite the same ... the 2023 table doesn't include any badge data, so even though Mr. Brown is in both tables, his entire record is not the same in 2022 and 2023. VSTACK still works, but UNIQUE is considering the entire record, and thus duplicates Mr. Brown.

QUESTION #1 -- How to get UNIQUE to consider column 1 (Name) only.

In Example 3, we get close to my true data set, in that the two parent tables are structured differently. While they share two columns, they each have a unique column. Still needing to combine these two tables here, but VSTACK isn't going to do in anymore, as it puts "Committee Room" from 2023 in the same column as "Badge #" from 2022.

QUESTION #2 -- How do you combine data from two tables with different structures?

If Question #1 can be answered successfully, then one approach is to manually restructure the data in the parent tables so it has the same structure, and then use VSTACK to pull it to a consolidated table. But not having to take this step would be preferable.

The reason I liked VSTACK to pull the entire table is the ability to sort off of another column other than the primary (in this case the primary column is Name, but we are sorting on Badge#). If we VSTACK just name and then use formulas to bring over the other data, this gets around the structure isssue, but causes problems sorting the VSTACK array by anything other than itself. So, final question...

QUESTION #3 -- How do you sort a table sourced from tables with different structures, especially when the primary key is an array?

This is Example 4, where VSTACK is used only to pull the Name and subsequent columns are pulled with formulas. But this requires that the lookup formula either a) be in hierarchal order when the data exists in both tables (column "Name") or point only to the specific parent table when the data doe not (columns "Badge #" and "Committee Room"), which isn't particular consistent or simple. And the only way I can sort it (AFAIK) is within the array itself (Name) which isn't the goal. Although I tried SORTBY vs. SORT, I could not come up with a way to get it to sort correctly. (In this simple example, getting it to sort by badge # and putting all the blanks at the beginning or end would suffice).

Many thanks!

I think I've done what you are asking for...

Formula:

Please Login or Register  to view this content.

and

Formula:

Please Login or Register  to view this content.

Booth in the attached file.

Originally Posted by Gregb11

Excellent formulas by DJunqueira. I think there might have been a copy/paste issue though for the first formula.[/CODE]

Tks Gregb11, I have a hard time translating my formulas with the new functions, the app does not translate them.

Formula:

Please Login or Register  to view this content.

You have done the right fix, tks again.

Thanks all. More database functions to unpack. I'm going to have to study this answer a bit.

Sometimes the challenge of posting in this formula is to try an provide an example in the simplest terms possible, and then find an answer works perfectly with the sample data but isn't directly adaptable to the actual data, which is far larger and more complex. This is the case here. Whereas in my sample file, the first two columns are the same in the source tables, that's not the case in the actual data -- and neither are the number of columns equal in each table. So, even if I adapted the VSTACK portion of the formula to, say, columns 16 and 17, where they are in table 1, in table 2 they are columns 18 and 19 ... or perhaps columns 18 and 21.

Thus it seems like the theory of using CHOOSECOLS imbedded within XLOOKUPS is the ultimate answer, and omitting the efficient VSTACK shortcut in your answer. The problem I'm having now is when using a VSTACK inside an HSTACK,the data spills vertically from the first cell, rendering all data in the first column (other than the first cell) as #N/A, even though the data in the second column populates correctly. So, I am not adapting your HSTACK / XLOOKUP / CHOOSECOLS combination correctly.

I'm not sure how to better represent the data than the actual file itself. To make this easier, I did remove a handful of worksheets containing a second, more complicated dataset for which this process will need to be repreated (this time with three source tables instead of two), but If I can figure this out with what's in the second attached file, I *should* be good to go. Please ignore the fact that the columns colored in green within each sub-table all happened to be in the same column distance (columns 15-18). This is a coincidence for this dataset only and is NOT the case in the next dataset (again, likely rendering that using VSTACK to pull multiple columns is likely not possible).

Futher, the end result of the formula you posted sorts by the first column, but I was hoping for a solution that enables sorting by a different array. Maybe the real data in my file is where I should have started. Sorry for the rework.

Source Data: USCB_Counties, USGS Counties
Target: Consolidated_Counties

In Consolidated, the idea is to bring the records from the source data to the target based on the unique GNIS codes. As data may be different for the same GNIS code USCB_ and USGS_, the hierarchy is USCB first, USGS second (aka if found in USCB, stop, else look in USGS). Further, the order of the resulting table should, ideally, be sorted first by STCd then by Name.

So, the original VSTACK is sitting in th GNIS column, but I cannot figure out how to use HSTACK to bring the resulting columns over based on the GNIS column, especially when the GNIS data in the source file may not be in the same column place (again they are in this dataset, but that is not universal).

Does this make sense?

In layman terms:
* For the second column of the first row, lookup GNIS 0023901 in USCB. If you find it, bring over ISOCd and place it in the State column. If you don't find it, look in USGS and do the same. If it is in neither, return nothing ("").
* Repeat for all other columns.
*Repeat for all other rows.
*When done, sort the table first by STCd then by Name.

Clearly I can do this by cutting-and-pasting the GNIS from the two source files and removing duplicates, and bring over all remaining data via lookups. This is far easier than what I'm asking, but I'm trying to learn how to do it dynamically via formulas instead as, while this dataset may not change, good practice is rarely cut-and-pasting.

Thanks for your continued help.

A VBA option

Please Login or Register  to view this content.