Sol Kawage
#help do I know any #excel wizards? I have a simple-ish problem and no idea how to solve it, but I suspect it's very easy if you know how:
I have several sheets with code lists on one column and quantities on the other, and I need to merge them into one sheet with the first column of codes, and then the quantities from each of the other sheets in columns.
The caveat is that the code lists in each sheet list might not be 100% the same.
Feb 27 at 10:35amWeb
Show threadKilian EvangFeb 27

@sol Not an Excel wizard, but in database terms, what you want to do sounds like an OUTER JOIN, so that might be a useful search term. This user seems to be asking a very similar question:

https://superuser.com/questions/1023123/how-to-simulate-a-full-outer-join-in-excel

How to simulate a full outer join in Excel?

Let's assume I have some data in Excel (and not in a real database). In one sheet, I have data, where one column functions as the ID, and I have made sure that the values in this column are unique....

Super User
Show threadSol KawageFeb 27
@texttheater ooh we’re getting somewhere thank you!!!! ☺️
Show threadRichard McDonaldFeb 27

@sol not sure if it’s the most efficient way, but you can use the UNIQUE function in order to create a combined list of all of your codes in the left hand columns. Once you have that, you can then set up a VLOOKUP in each cell of your combined table in order to find the relevant values in your original three tables. If the empty values return errors, you might also want to include something like an IF statement combined with ISERROR in order to make everything look neater.

Hopefully that helps!

Show threadSol KawageFeb 27
@richard thank you! I'm going to try this method.... I like it because it seems it would update the merged sheet if the content of the split ones changes.
Show threadRichard McDonaldFeb 27
@sol yeah, it should do. You can also future proof it a bit by extending the number of rows that the UNIQUE and VLOOKUPS are referencing, so if your original tables get taller, your final table will still work.
Show threadGaspar TorrieroFeb 27
@richard @sol
May I suggest the newer and easier XLOOKUP() i/o vlookup?
Show threadDan Kletter 🥑Feb 27
@sol Power Query to combine (it’s like a cross join) everything in the workbook. Any time you add data to the sheets or a new sheet, it will automatically update the combined output. https://www.youtube.com/watch?v=zxNq2mISfIc
Combine Multiple Excel Sheets with a Transform Sample Sheet in Power Query

YouTube
Show threadSol KawageFeb 27
@soundclamp oooohhhhh THANK YOU! wizard has come forth :)
Show threadDan Kletter 🥑Feb 27
@sol Oh yeah, of course!