Help Combining Data from separate sources

*Edit, I have found that Power Query did what I needed, in case anyone else comes across this thread. If anyone knows a better way, feel free to post, it would be much appreciated to get other ideas.


Hi All,

I need some assistance with a project at work. I have 3+ sets of data I need to compare for analysis. The only common information among the data sets are the IDs of each item (listed as "H###########" where # is a number - example H14236023101.

The biggest issue for comparison is that the sets of data don't all have the same numbers, so I want to filter out only the information that matches between the first 2 of these data sets, and where applicable, supplement it with the 3rd.

The first data set has fewer items, but the first 10 items in that list are not on the second, larger, data set. The third set is just additional data that is useful but not required for the comparison, which is why I want to supplement where applicable, and not just directly compare (does this makes sense??).

I'd like to be able to import all the information of an item into a table that has the relevant comparisons side by side (so some formula or macro I can work designate columns based on particular data).

And if possible, have a separate table that will import all the samples that do not have a duplicate in the comparative table, along with just a note stating which data set they originate (does not require the rest of the data).

I hope this all makes sense to someone who can help me haha.

Thanks for reading