combining different types of data from two tables

[Lenalex5]

Hello,

I have two spreadsheets exported from different databases. SheetA has two columns: studentID and studentName. SheetB also has two columns: studentID and awardsEarned.

I want to combine these for a final deliverable using studentID as a matching key. I want studentName and awardsEarned to be in one column because I am pasting this into a document and handing it off for printing.

My deliverable needs to have a column that combines the two datasets like:

student 1

- award x

- award y

- award z

student 2

 - award x

 -award y

Further, I want to re-arrange the data so that I can have it by Award and then by student:

Award 1

- student 1

- student 2

Award 2

 - student 1

etc.

This is more of relational database operation as as I understand it. I used to do this is FileMaker but cannot use it any longer. I want to find a way to do this in excel so I can create a process that does not require a database backend. Am I asking too much?

[#2]

@Lenalex5 Power Pivot (PP) will probably work for you. Add the two tables to the Data Model (DM). Create a relationship between the studentID in the Awards table (the many-side) and the studentID in the Student table (the one-side). Now you can create a pivot table based on both tables as demonstrated in the attached file.

If your data needs cleaning-up, you might want to use Power Query (PQ) first. Load clean data from PQ into the DM and do the steps as mentioned above, or merge the two tables in PQ (resembling Excel's VLOOKUP) and load a smaller table back to Excel, listing all student names for all awards given. Also demonstrated int he attached file.