How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Richard Your Own Question
Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 36526
Experience:  Over 15 year experience resolving Microsoft Office Issues
Type Your Microsoft Office Question Here...
Richard is online now
A new question is answered every 9 seconds

If I have a spreadsheet with 3 rows of data about a student,

Customer Question

If I have a spreadsheet with 3 rows of data about a student, with most of the fields in the 3 rows being repetitive, can I combine these 3 rows to display as one row that displays all of the non-repetitive data, by creating a pivot table? If so, how do I do this. My example is releated to a college report produced by a query that checks a database for students in danger of failing. Of course, if there is a student that is danger in multiple classes, it displays all 3 in the spreadsheet, repeating the personal information(name, number, address, etc.) in each row and listing the courses. The users want all 3 courses to be displayed in one row, along with one copy of the personal info. It would look like this:

name address Crn Subj Crs Num Crs title Instructor (then start again with next course on same line)
Submitted: 4 years ago.
Category: Microsoft Office
Expert:  Rachel-Mod replied 4 years ago.
I'm a moderator for this topic. I've been working hard to find a professionals to assist you with your Question , but sometimes finding the right professionals can take a little longer than expected.

I wonder whether you're ok with continuing to wait for an answer. If you are, please let me know and I will continue my search. If not, feel free to let me know and I will cancel this question for you.

Thank you!
Customer: replied 4 years ago.

I can wait a little longer.

Expert:  Rachel-Mod replied 4 years ago.
Thank you for your patience. We will continue the search for a professional for you.
Expert:  Richard replied 4 years ago.
Thank you for your question, my name is Richard.

Yes you can and you can Group within the Pivot Table.

Select all the data, and then create a Pivot Table on the data,

It should Group this (removing the duplicates) within the chart.

If not, upload the file so I can look at it directly.

- Click the following link:
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"
Customer: replied 4 years ago.

I highlighted the data and then did insert, pivot table and created a pivot table and chose some basic fields. It does appear to group it by student, but it's not in a very readable format and I don't know enough about pivot tables to make it readable. It also has the big blank box that says "Drop column fields here" and "Drop data items here". I don't know what to do with that. Can you please give me the step by step on how to turn it into a readable report. The file number is XXXXX Thanks!

Expert:  Richard replied 4 years ago.

Start with unticking all but one field to add to the report.

This you see under Choose Fields to Add to Report.

Only add the field that you want to display, this will not repeat.

This is the main field, then drag additional fields to the right of it for Calculations on it.

Are you able to do this?

Customer: replied 4 years ago.

Yes. I started with the id field, which is displayed in the 1st column on the left. Then I dragged the course title field into the "drop data items here" block. It creates another column just to the right of the id column, but the new column takes a heading of "Total" and just displays a count of the number of courses each student is failing. How do I get it to display the course title(s)(plural for those failing multiple courses) all on one row?

Expert:  Richard replied 4 years ago.
Can you download this file with pivot table.xls

Is this what you require?

Customer: replied 4 years ago.

Can't download it. When I put the mouse on the link, it changes color to orange and doesn't give me the pointer to click on the link.

Expert:  Richard replied 4 years ago.
Can you try this link please

Let me know if you have any difficulties please

Customer: replied 4 years ago.

That's not what I'm looking for. If you go back to the original spreadsheet and look at the first 2 lines. The student's name isXXXXX and he's failing 2 courses(ENGL 102 and PECS 110). I want the pivot table to combine these 2 lines into one line that lists all of his personal information, then lists both courses(with other info about each course) back to back, reading from left to right, on the same line with his personal info. I want only one line per student, even if they're failing multiple classes. If it can't be done by a pivot table, it could be done using MSAccess if you know how.

Expert:  Richard replied 4 years ago.
Ok, what you want is Database functionality, not a Spreadsheet or a Pivot Table.

I made a Database in Access for you, the issue is your data needs cleaning up.

You can review your data imported from the excel files in the Tables in my Database file.

And the Main Form shows the records linked. Though you will see blanks as the data in Excel has blanks.

You can download the file here

If you clean up the data, you can view each student and their linked records.

If you have any difficulties at all, please do not hesitate to let me know so I can assist you further

Thank you
Expert:  Richard replied 4 years ago.

Where you able to review the Database I made for you?

Customer: replied 4 years ago.

Yes, but unless it's just a matter of me not knowing how to use it, I don't think it solves my problem. When I open it, I see the 2 forms and I can put in an id # XXXXX the fmain form and it looks up some information for the student. If I open the other form, it displays the courses and id, but it still has multiple lines per student. Is there something I'm supposed to do to make it display all the information for a student on one single line?


This all comes from MSAccess macros and queries that pull this data from oracle tables and generate the report in Access. I think you may be on the right path by separating the data into 2 tables and then querying the two tables so that it generates 1 line of output per student, even if that student is failing(or in danger of failing) more than 1 class, but I need to know how to put this in the original query, so when the user runs it at midterm, it automatically does all the steps to produce the output they are looking for. Let me know if you need to see any of the queries or macros that we run.

Expert:  Richard replied 4 years ago.
You only open the Main form, the other form is linked inside the main form.

As I indicated you first need to Clean Up the data in the Tables, by removing empty rows, columns ect.

You just copy the data into the Tables in Access to do this.

Customer: replied 4 years ago.

I apologize for my lack of experience with Access, but I'm not understanding what I'm supposed to do to see a report. When I open fmain, it's just a blank form with the fields id, etc., off to the left hand side. Am I supposed to key in an id# XXXXX pull up information for that student? Thanks for your patience as i try to learn this.
Expert:  Richard replied 4 years ago.
It is no problem.

You use the Navigation buttons at the bottom.

They are like Play buttons to go through the record.

You also see the record number.

You see this at the bottom?

Customer: replied 4 years ago.

Yes. When I go to the 2nd record(Josh Pittman), it displays 2 lines for him with fields ID1,Campus,id,AS(blank),CL,GST(Blank),Private,Action(Blank),PS1(Blank) and part of PS2(Blank). Are you saying that if i remove the empty columns, that it will pull the rest of Josh's information onto 1 row(or record), instead of spreading it over 7 or 8 records? If so, how do I delete a column that's blank?


As a side note, I'm not sure that i will be able to remove these fields from the users report, b/c even though they may be empty columns for the students selected for this semester, students selected in subsequent semesters may have information attached to their records that belongs in these empty fields. Thanks!!

Expert:  Richard replied 4 years ago.
It is the empty Rows.

Look in the Tables in Access, You will see the Data like Excel, except each row needs to be complete.

You can delete the rows within the Table there.

Let me know how you go please.

Customer: replied 4 years ago.

I went into the Grades table and removed the blank columns. Do I have to go into the Names table and remove the rows that only have information in 1 column?. For Josh Pittman in the names table, I need to manually delete 6 rows?

Expert:  Richard replied 4 years ago.
Correct, you just want one name.

As the ID of the Student links to Multiple Rows in the other Table (based on the ID).

So one Name in Name and in the other Table you can have the multiple courses and information.

Customer: replied 4 years ago.

Even after I delete the empty rows, it still displays the courses in multiple rows for any student that's failing multiple classes. They want the courses displayed on the same line if a student has more than one.
Also, Would my user have to go thru and perform all these manual deletes every time they run the macros and queries that generate the Access report?
Expert:  Richard replied 4 years ago.
You have the user enter the data directly into Access, you should not be using Excel for your data entry work, this is why you have the problem.

It is going to display a course per row, this is how databasing works, as each course is its own record.

Why is this an issue?

Customer: replied 4 years ago.
Relist: Other.
The expert wasn't really listening to my problem and what I was trying to do. i kept having to explain it over and over.
Expert:  Richard replied 4 years ago.
I am, you are not understanding how databasing works, this is the issue.

Can you carefully look at my last statement,

Again, provide me the database so I can look at it.

You are not explaining over and over, you are not noting what I am trying to tell you.

Expert:  Richard replied 4 years ago.
Please let me know
Expert:  Richard replied 4 years ago.

Related Microsoft Office Questions