Match data in rows

[jdlr]

I have data that contains student test scores, along with the students' names and a student ID number. Each time the students take this test, a new data file is generated. For example, I will have a file for Quarter 1, Quarter 2 and so on. The problem is that each time this test is administered, there may be a student who, for example, took the test Quarter 1, but not Quarter 3. The other part to this is that each data file contains 14 columns of data. I really only need to match up four or five of these columns. I understand that I could delete the unnecessary columns.

Is there a way to match a key field, such as the student ID, to align data in rows? In the example below, I would like to align the data for student "1" so their "SS" values align in a row, not a column. The other columns (X,Y,Z) represent the other data that is included. As you can see, there are students who have taken this test different numbers of times, represented by the different numbers in the "Name" column.

Name SS X Y Z
1 797 8.1 59 54.8
1 824 9.9 70 61
2 640 4.6 13 26.3
3 779 7.4 52 51.1
4 673 5.1 20 32.3
4 672 5.1 19 31.5
5 566 3.7 5 15.4
5 564 3.6 4 13.1
5 605 4.1 7 18.9
6 574 3.8 5 15.4
7 631 4.5 12 25.3
7 630 4.5 11 24.2

Although I am trying to learn to write scripts, this tool would not be one I could use. Thank you in advance for reading what may be a very confusing post!

[Noah101]

Yes you can Jd,

in each row just use formula so I'm assuming their is a raw data paste somewhere? it would be easier if you could upload an excel file that mirrors your example, just make sure all private data is removed , try to use mock data.

Noah

[FDibbins]

A few notes...
1. if this data is being imported, more often than not, imported values come in as text, and need to be converted back to numbers/values
2. It is almost always better to keep all data on 1 sheet, and run extracts from that. So, if you keep getting new data (as opposed to repeat data with some new scores in it), it would be better to put the data 1 set below the previous.

With that said, I think you could probably get away with an extract table using a (relatively) simple index/match function
To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[jdlr]

FDibbins and Noah101,

Thank you for replying. I have attached a sample file. Although I have deleted some fields,everything is formatted as it was in the original upload.

[bebo021999]

Is it are you looking for?

[jdlr]

Yes! I neglected to mention that the actual file will have about 600 rows of data. If this does not matter, then I would love to know how you formatted the data so it appeared as it did. Thank you.