Help with moving multiple columns and checking alignment

**TheMegs** · 06-22-2012, 09:52 AM

Hi there-

I work with school data, based with standards and all that jazz. What i need help with is the following:

The data that I get is: Sheet3.xlsx

What the data needs to look like is: Sheet4.xlsx

Essentially, i need to be able to move and align all the columns to each other, so that each student's individual data stays with each student, but moves to the different columns. I've tried using a transpose macro, but it doesn't work quite right, because occasionally a student has only a few out of the 4 scores required. If there is no score, it does not leave me a blank (which is what it needs to be) but the data just goes to the next record.

can anyone help me?

If you need me to explain further, please let me know, and I can try to explain better.

Thanks!

-TheMeg

**TheMegs** · 06-22-2012, 10:44 AM

bump for views

**jaslake** · 06-22-2012, 02:55 PM

Hi TheMeg

Try the code in the attached...let me know of issues.

**jeffreybrown** · 06-22-2012, 05:54 PM

Hi TheMegs,

I was working on this earlier, but haven't had internet access for quite some time. Anyway, I see John has provided an excellent solution using VBA, but my VBA talents are not even close but I would like to at least post what I came up with.

I used some of the same methods, but I did it the manual way.

Data tab >> Advanced

Advanced Filter

Action: Copy to another location
List range: Before!$A:$B
Copy to: Before!$G$1
Check >> Unique records only
OK

Copy C2:C5
Right click cell I1 >> paste special >> Transpose

I2 array entered formula copied right and down

Please Login or Register  to view this content.

IMPORTANT
This is an array formula
Enter the formula >> press F2 then >> CTRL + SHIFT + ENTER
If entered correctly, the formula will be enclosed in {brackets}
Do not enter the {brackets} manually

Again not as quick as John's VBA, but for me, quicker than I could have come up with his VBA solution