I've been set a fairly big project at work and I need to rearrange the
data in an excel sheet. I'll try and explain it as simply as
possible.

example format at the moment:

Name course result
Brian geog A
Brian maths B
Brian phys C
Brian Sport C
Tom maths B
Tom geog A


There are roughly 5000 rows with 300 seperate names.

format required:

Name geog maths phys
Brian A B C
Tom A A

Each student doesnt do the same amount of courses, so there will be
blanks (like tom doing phys in example). To make my life even more
difficult there is lots of data in the source file I have that doesnt
need to be entered onto the final sheet (like brian's sports result in
example). at the moment I have the source file sorted alphabetically.
What's the best approach to solving this? Any ideas?

I'm assuming that Name course result are each in separate tables?

Then create a pivot table. Info on pivot tables can be found he
http://www.cpearson.com/excel/pivots.htm
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


" wrote:

I've been set a fairly big project at work and I need to rearrange the
data in an excel sheet. I'll try and explain it as simply as
possible.

example format at the moment:

Name course result
Brian geog A
Brian maths B
Brian phys C
Brian Sport C
Tom maths B
Tom geog A


There are roughly 5000 rows with 300 seperate names.

format required:

Name geog maths phys
Brian A B C
Tom A A

Each student doesnt do the same amount of courses, so there will be
blanks (like tom doing phys in example). To make my life even more
difficult there is lots of data in the source file I have that doesnt
need to be entered onto the final sheet (like brian's sports result in
example). at the moment I have the source file sorted alphabetically.
What's the best approach to solving this? Any ideas?

Sorry, the first line of my response should read: I'm assuming that Name
course result are each in separate COLUMNS?
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Dave F" wrote:

I'm assuming that Name course result are each in separate tables?

Then create a pivot table. Info on pivot tables can be found he
http://www.cpearson.com/excel/pivots.htm
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


" wrote:

I've been set a fairly big project at work and I need to rearrange the
data in an excel sheet. I'll try and explain it as simply as
possible.

example format at the moment:

Name course result
Brian geog A
Brian maths B
Brian phys C
Brian Sport C
Tom maths B
Tom geog A


There are roughly 5000 rows with 300 seperate names.

format required:

Name geog maths phys
Brian A B C
Tom A A

Each student doesnt do the same amount of courses, so there will be
blanks (like tom doing phys in example). To make my life even more
difficult there is lots of data in the source file I have that doesnt
need to be entered onto the final sheet (like brian's sports result in
example). at the moment I have the source file sorted alphabetically.
What's the best approach to solving this? Any ideas?

criterias:
Column E: holds names (starting with E2)
F1: holds geog
G1: holds maths
H1: holds phys

In F2:
=IF(ISNA(MATCH(1,(Name=$E2)*(course=F$1),0)),"",IN DEX(result,MATCH(1,(Name=$E2)*(course=F$1),0)))

ctrl+shift+enter, not just enter
copy across and down as far as needed


"Dave F" wrote:

Sorry, the first line of my response should read: I'm assuming that Name
course result are each in separate COLUMNS?
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


"Dave F" wrote:

I'm assuming that Name course result are each in separate tables?

Then create a pivot table. Info on pivot tables can be found he
http://www.cpearson.com/excel/pivots.htm
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.


" wrote:

I've been set a fairly big project at work and I need to rearrange the
data in an excel sheet. I'll try and explain it as simply as
possible.

example format at the moment:

Name course result
Brian geog A
Brian maths B
Brian phys C
Brian Sport C
Tom maths B
Tom geog A


There are roughly 5000 rows with 300 seperate names.

format required:

Name geog maths phys
Brian A B C
Tom A A

Each student doesnt do the same amount of courses, so there will be
blanks (like tom doing phys in example). To make my life even more
difficult there is lots of data in the source file I have that doesnt
need to be entered onto the final sheet (like brian's sports result in
example). at the moment I have the source file sorted alphabetically.
What's the best approach to solving this? Any ideas?

Thank you both for help. As you may've guessed I'm not great with
excel. I've been ploughing through online helpsites but it's going
over my head a little. Yes they are in separate columns. so in my
example:

a1 = name, a2= course, a3=result
b1= Brian, b2= geog, b3= A

etc

and I want, eventually on a new sheet but I can copy across, so for
this start on h1 which is out of the way of the columns in the source
data (it goes up to F).

h1=name, i1=geog, j1=phys, k1=maths .... and columns continue for
each new course title
h2= brian, i2=A, j2=C, k2=B ... and these columns
contain the results from (column a3,b3 etc)

I'm not going to be in office until next wednesday. Maybe it'd be a
good idea to get a sample sheet from the real data and post a link to
it?



On 4 Apr, 16:22, Teethless mama
wrote:
criterias:
Column E: holds names (starting with E2)
F1: holds geog
G1: holds maths
H1: holds phys

In F2:
=IF(ISNA(MATCH(1,(Name=$E2)*(course=F$1),0)),"",IN DEX(result,MATCH(1,(Name=*$E2)*(course=F$1),0)))

ctrl+shift+enter, not just enter
copy across and down as far as needed



"Dave F" wrote:
Sorry, the first line of my response should read: I'm assuming that Name
course result are each in separate COLUMNS?
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

"Dave F" wrote:

I'm assuming that Name course result are each in separate tables?

Then create a pivot table. Info on pivot tables can be found he
http://www.cpearson.com/excel/pivots.htm
--
A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

" wrote:

I've been set a fairly big project at work and I need to rearrange the
data in an excel sheet. I'll try and explain it as simply as
possible.

example format at the moment:

Name course result
Brian geog A
Brian maths B
Brian phys C
Brian Sport C
Tom maths B
Tom geog A

There are roughly 5000 rows with 300 seperate names.

format required:

Name geog maths phys
Brian A B C
Tom A A

Each student doesnt do the same amount of courses, so there will be
blanks (like tom doing phys in example). To make my life even more
difficult there is lots of data in the source file I have that doesnt
need to be entered onto the final sheet (like brian's sports result in
example). at the moment I have the source file sorted alphabetically.
What's the best approach to solving this? Any ideas?- Hide quoted text -

- Show quoted text -