Moving cells around for database import

[saxmann]

Hello,

I am a medium-level Excel user. However, I primarily use Excel to prepare data to be imported into a FileMaker database. I have a grade report from my university's reporting system that is in PDF format. I am able to open that PDF in Word, then copy and paste it into Excel and it keeps the formatting consistent. However, the data isn't in a suitable format for FileMaker. I'm pretty bad at script programming in Excel but can tweak an existing script okay.

The report is a grade report listing all students in my unit in a given semester. It is divided into sections by student. There is a variable amount of rows per student. The only data I need are the Student ID (always a 9-digit number), Section Number (always a 5-digit number), Midterm Grade, and Final Grade. Ideally, a script would search for the first 9-digit number, copy it, and paste it in a separate sheet, go to the first sheet and copy each Section Number, Midterm, and Final Grade for each section and paste it into the second sheet with the Student ID in the first column. Then, do the same thing with the next student.

All I need is to have rows with the Student ID, Section Number, Midterm Grade, and Final Grade all in a separate sheet. I don't know if I can script looking for data based on field length and paste it before those cells or not. Attached is an example of what the report looks like then on the second sheet, what I need.

I don't know if this is medium or difficult to do since there is a variable amount of rows per student. Any help is appreciated. Thanks!

Keith


example 1.xlsx

[MarvinP]

Hi saxman and welcome to the forum,

Going from Filemaker to PDF and then to Excel seems iffy to me. Can't you export the data from Filemaker to Excel directly as a start?
https://stackoverflow.com/questions/...her%20than.MER.

The second part of your question depends a lot on what the data looks like. But see:
https://www.exceldemy.com/split-exce...the%20keyboard.
to see if any of these methods fit your problem.

[saxmann]

The original data comes from the university database which is proprietary. That's in PDF format. FileMaker is the final destination which is why I need the Student ID on the same row as the Section Number, Midterm, and Final Grade. Thanks!

[MarvinP]

Hi saxman,

Because you said "script", I'm assuming you are talking about Power Query.

If you save your file and then create a Powe Query Script to pull in that file's data is your start.
Then you need to create a new conditional column that will only copy the ID: if it has one in column 1 into this new column.
Then the big step is to "Fill Down", which will copy those IDs down until it finds another.
Filter our all the null and Section in that column and remove all the columns you don't need.

Here is my work on your file. You will need to save it to your own hard drive and replace the script path to where you saved it.
Hope this helps.
ps - there are a lot of ways to get your result using Power Query.
Student Grades Reduced.xlsx

[saxmann]

Hello MarvinP,

Fantastic! Thanks for your help and pointing me in the right direction. I've never used Power Query before and it seems like the tool I was needing. Thanks!

[beyond Excel]

Greetings to all.
You can also try the following:

Sub Macro_8() 
Dim a, Q&, i&, R&, b 
Application.ScreenUpdating = False 
a = Range("P1", Cells(Rows.Count, 1).End(xlUp)(2)): Q = UBound(a) 
R = 1: i = 1 
a(R, 1) = "ID": a(R, 2) = "Section": a(R, 3) = "Midterm Grade": a(R, 4) = "Final Grade" 
Do While i < Q 
  Do While a(i, 9) <> "Section": i = 1 + i: Loop 
  b = Split(a(i - 2, 1), ": ")(1) 
  Do 
    i = 1 + i: If a(i, 9) = "" Then Exit Do 
    R = 1 + R 
    a(R, 1) = b: a(R, 2) = a(i, 9): a(R, 3) = a(i, 13): a(R, 4) = a(i, 15) 
  Loop 
Loop 
[w1].Resize(R, 4) = a: [w1].Resize(R, 4).Columns.AutoFit 
End Sub