Combining data from 3 different data sets with common ID's onto a summary sheet in specific places

%3CLINGO-SUB%20id%3D%22lingo-sub-1544144%22%20slang%3D%22en-US%22%3ECombining%20data%20from%203%20different%20data%20sets%20with%20common%20ID's%20onto%20a%20summary%20sheet%20in%20specific%20places%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1544144%22%20slang%3D%22en-US%22%3E%3CP%3Ehello%2C%20I%20have%203%20sets%20of%20data%2C%20they%20each%20have%202%20columns%2C%20one%20with%20an%20ID%20number%20and%20the%20other%20has%20a%20score%20in%20it.%20I%20want%20to%20be%20able%20to%20be%20able%20to%20pull%20the%20scores%20from%20each%20data%20set%20that%20relate%20to%20an%20ID%20(some%20ID%20numbers%20may%20only%20have%201%20or%202%20scores)%20onto%20a%20separate%20worksheet%20or%20the%20same%20one%20if%20easier.%20How%20do%20I%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1544144%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1544932%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20data%20from%203%20different%20data%20sets%20with%20common%20ID's%20onto%20a%20summary%20sheet%20in%20specific%20plac%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1544932%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F738762%22%20target%3D%22_blank%22%3E%40RichD2020%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20this%20is%20just%20a%20one-time%20thing%2C%20I'm%20going%20to%20give%20you%20a%20less%20than%20elegant%20solution.%20I'm%20sure%20there%20are%20Power%20Query%20methods%2C%20but%20my%20sense%20is%20that%20you're%20more%20or%20less%20a%20beginner%20with%20Excel%20and%20so%20Power%20Query%20may%20be%20a%20step%20too%20far.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20involves%3C%2FP%3E%3CP%3E1.%20just%20copying%20all%20the%20IDs%20into%20a%20singe%20column.%3C%2FP%3E%3CP%3E2.%20using%20the%20UNIQUE%20function%20(new%20to%20Excel%20so%20you%20do%20need%20the%20most%20current%20version%20of%20Excel)%20to%20get%20just%20a%20single%20instance%20of%20each%20ID%3C%2FP%3E%3CP%3E3.%20unnecessary%2C%20but%20I%20used%20SORT%20with%20UNIQUE%20to%20sort%20the%20IDs%20into%20order%3C%2FP%3E%3CP%3E4.%20Use%20VLOOKUP%20to%20get%20the%20scores%20from%20each%20data%20set.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAs%20I%20said%2C%20it's%20not%20the%20most%20elegant--Power%20Query%20would%20be%20that--but%20it'll%20work.%20I%20used%20your%20example%20data%20in%20the%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

hello, I have 3 sets of data, they each have 2 columns, one with an ID number and the other has a score in it. I want to be able to be able to pull the scores from each data set that relate to an ID (some ID numbers may only have 1 or 2 scores) onto a separate worksheet or the same one if easier. How do I do this?

 

 

1 Reply

@RichD2020 

 

Assuming this is just a one-time thing, I'm going to give you a less than elegant solution. I'm sure there are Power Query methods, but my sense is that you're more or less a beginner with Excel and so Power Query may be a step too far.

 

This involves

1. just copying all the IDs into a singe column.

2. using the UNIQUE function (new to Excel so you do need the most current version of Excel) to get just a single instance of each ID

3. unnecessary, but I used SORT with UNIQUE to sort the IDs into order

4. Use VLOOKUP to get the scores from each data set.

 

As I said, it's not the most elegant--Power Query would be that--but it'll work. I used your example data in the attached.