Help Combining Data from separate sources

[JonoRig]

*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

[ImranBhatti]

Would be better to upload a sample workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

Data must be desensitized and should not contain huge amount.Maximum 20 records are enough to experiment on.

[Glenn Kennedy]

Hi. You have me confused. I assumed on first reading, that you wanted to assemble a list of unique H-numbers and then build up from there. However, when I did that, i discoverd that, on your posted sample, there are 31 H-numbers and they're all different. So either amend your sample sheet, or have another go at telling us what you really want!!!

[JonoRig]

Hi Glenn,

Maybe unnecessarily so, I was attempting to avoid context, but as the data is anonymised I suppose that is rather irrelevant. so I will explain within context.

So the data is a list of all the samples of interest from 2016. I am comparing Genomic Data to Phenotypic Data. Set one is our data from Phenotype and has 306 records. Dataset two is Genomic data from Whole Genome Sequencing condensed down to the particular genes of interest for this comparative analysis and had 326 records. Dataset three is not as important, but it contains the medically relevant values of Antimicrobial Susceptibility.

As some sample do not have successful sequencing data, nor do some have successful AMR data, these two Datasets are not a perfect match, so it's not a simple task of copy and paste. The reason why you say you see 31 seperate H numbers in my "Example" is because as per ImranBhatti suggestion i have included an example of the datasets by lifting the first 10 from each document and pasting them in, they may not match, but as an example, thats not 100% relevant, although it would help.

So, as i am not familiar with formulae, Macros or any kind of programming or coding, but i have datasets too large to do manually, i was asking help with the following points.

1. A formula or Macro that can compare the H number ID's in 2 lists
2. This comparison will eliminate unique numbers (preferably importing them to a seperate list so we know what sample is missing what for further internal investigation)
3. This comparison will compile any information listed in the rest of the row into one table - listing Spp (species), ST/Sequence type, AMR results (book one), Resistance genes detected (book 2) and where relevant, AMR Values (book 3 - not important like the other two, so comparison with this set need not be eliminative))
4. The Final table should list the relevant Phenotypic result (Ery4/8, Cip 0.5 etc) side by side with the relevant Genotypic (Ery = Macrolide, Tet = Tetracyclines) etc
5. This will allow us to see if the Phenotype of the sample shown is the same as the genes detected