Extracting Data from A Master Spreadsheet.

[Stevie Clarke]

I have a table consisting of Student names (first column) and then several columns with subject titles. For each student (the row in my table) their subject grades are entered. A student will have a maximum of 4 entries so several columns for a given student are blank.

I would like to generate a spreadsheet/report which has the name of the student and then itemises the subjects taken and the grade achieved e.g
Name
Fred Biology A Maths B Physics C
Mary Maths B Physics C Geography B Electronics C
Tom Maths A Politics C

Can you help?

[Glenn Kennedy]

Your other post was closed. Try again...

In B2:
=IFERROR(INDEX('Master Data'!$1:$1,AGGREGATE(15,6,COLUMN('Master Data'!$B2:$I2)/('Master Data'!$B2:$I2<>""),1+INT(COLUMNS($A:A)/2))),"")

In C2:
=IFERROR(INDEX('Master Data'!$B2:$I2,MATCH(B2,'Master Data'!$B$1:$I$1,0)),"")

Select B2 AND C2. Drag across and down.

[MarvinP]

Hi Stevie,

If you have Microsoft Access, your problem becomes "How do I do a Crosstab Query". Read about them at:

https://support.office.com/en-us/art...0-2CD8484667E8

[MarvinP]

Hey Stevie,

You can also do this problem using Power Query tool of "Pivot and don't Aggregate". You will need to install the Power Query Add-in to get the newer set of tools. I did change your original data to 3 columns of Name, Subject and Grade before I used PQ to get the attached answer.

Exam Results 2018 Using PQ.xlsx

[Stevie Clarke]

Dear All

Many thanks for your suggestions; I now have a quick method to generate my report which has saved me a considerable amount of time.

Thank you

Steve

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.