Hello - I need to be able to 'undo' a two way table. The data is in this
format:
Student number English Grade Maths Grade
US456 A B+
US455 A- C+

And I need it to look like this:
US456 English Grade A
US456 Maths Grade B+
US455 English Grade A-
US455 Maths Grade C+

So each grade has a separate row - other than copying and pasting hundreds
of records is there a simple way to do this?

=A2&" "&$B$1&" "&B2
where : A2 = "First Student in your table"; $B$1="English Grade" and
B2="English grade for first student and drag this cell untill end then do
the same with $C$1 for theMath Grade
Alejandro
"Snara" <(E-Mail Removed)> wrote in message
news:F224892B-F7F4-4CDB-A19C-(E-Mail Removed)...
> Hello - I need to be able to 'undo' a two way table. The data is in this
> format:
> Student number English Grade Maths Grade
> US456 A B+
> US455 A- C+
>
> And I need it to look like this:
> US456 English Grade A
> US456 Maths Grade B+
> US455 English Grade A-
> US455 Maths Grade C+
>
> So each grade has a separate row - other than copying and pasting hundreds
> of records is there a simple way to do this?

Thank you for this, I hadn't thought of doing it that way! The only thing is
I have about 20 columns with different headings and the number of columns
varies on each spreadsheet (some spreadsheets contain up to 400 student
records). This requires that I drag the formulas down the correct number of
cells then change slightly and repeat - is there a way to get excel to do
more of this for me!! Thanks again!


"Ignacio Machin ( .NET/ C# MVP )" wrote:

> =A2&" "&$B$1&" "&B2
> where : A2 = "First Student in your table"; $B$1="English Grade" and
> B2="English grade for first student and drag this cell untill end then do
> the same with $C$1 for theMath Grade
> Alejandro
> "Snara" <(E-Mail Removed)> wrote in message
> news:F224892B-F7F4-4CDB-A19C-(E-Mail Removed)...
> > Hello - I need to be able to 'undo' a two way table. The data is in this
> > format:
> > Student number English Grade Maths Grade
> > US456 A B+
> > US455 A- C+
> >
> > And I need it to look like this:
> > US456 English Grade A
> > US456 Maths Grade B+
> > US455 English Grade A-
> > US455 Maths Grade C+
> >
> > So each grade has a separate row - other than copying and pasting hundreds
> > of records is there a simple way to do this?
>
>
>

To reorganize the data, you can use the 'unpivot' technique described by
John Walkenbach:

http://j-walk.com/ss/excel/usertips/tip068.htm

Snara wrote:
> Hello - I need to be able to 'undo' a two way table. The data is in this
> format:
> Student number English Grade Maths Grade
> US456 A B+
> US455 A- C+
>
> And I need it to look like this:
> US456 English Grade A
> US456 Maths Grade B+
> US455 English Grade A-
> US455 Maths Grade C+
>
> So each grade has a separate row - other than copying and pasting hundreds
> of records is there a simple way to do this?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

That is BRILLIANT - thanks so much you have saved hours of my life!!!



"Debra Dalgleish" wrote:

> To reorganize the data, you can use the 'unpivot' technique described by
> John Walkenbach:
>
> http://j-walk.com/ss/excel/usertips/tip068.htm
>
> Snara wrote:
> > Hello - I need to be able to 'undo' a two way table. The data is in this
> > format:
> > Student number English Grade Maths Grade
> > US456 A B+
> > US455 A- C+
> >
> > And I need it to look like this:
> > US456 English Grade A
> > US456 Maths Grade B+
> > US455 English Grade A-
> > US455 Maths Grade C+
> >
> > So each grade has a separate row - other than copying and pasting hundreds
> > of records is there a simple way to do this?
>
>
> --
> Debra Dalgleish
> Contextures
> http://www.contextures.com/tiptech.html
>
>

You're welcome! Thanks for letting me know that it helped.

Snara wrote:
> That is BRILLIANT - thanks so much you have saved hours of my life!!!
>
>
>
> "Debra Dalgleish" wrote:
>
>
>>To reorganize the data, you can use the 'unpivot' technique described by
>>John Walkenbach:
>>
>> http://j-walk.com/ss/excel/usertips/tip068.htm
>>
>>Snara wrote:
>>
>>>Hello - I need to be able to 'undo' a two way table. The data is in this
>>>format:
>>>Student number English Grade Maths Grade
>>>US456 A B+
>>>US455 A- C+
>>>
>>>And I need it to look like this:
>>>US456 English Grade A
>>>US456 Maths Grade B+
>>>US455 English Grade A-
>>>US455 Maths Grade C+
>>>
>>>So each grade has a separate row - other than copying and pasting hundreds
>>>of records is there a simple way to do this?
>>
>>
>>--
>>Debra Dalgleish
>>Contextures
>>http://www.contextures.com/tiptech.html
>>
>>
>


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html