ASP.NET, SQL Server, WPF, C#, VB.NET

Eclipse wrote: 
 
Here is how I would do this.... 
 
With your data in columns A-C, create defined names as follows (assume 
 
Sheet1 and header row in row 1): 
 
NmList  refers to:  =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1) 
 
SubjList  refers to:  =OFFSET(NmList,0,1) 
 
GradeList  refers to:  =OFFSET(NmList,0,2) 
 
NmSubMatch  refers to:  =NmList&"|"&SubjList 
 
In F1:H1, I had the unique subjects as headers 
 
In E2 and down, I had the names of the students. 
 
Select cell F2 and insert the following name 
 
NameSubj   refers to:  =Sheet1!$E2&"|"&Sheet1!F$1 
 
In the body of the table, I used the following formula 
 
=INDEX(GradeList,MATCH(NameSubj,NmSubMatch,0)) 
 
for all entries. 
 
I formatted the cells as General;General;   (note the second semicolon) 
 
to allow for grades not filled in to remain blank (the formula will 
 
return a zero). 
 
Entries not found will return a #N/A error unless you change the 
 
formula to something like: 
 
=IF(ISNA(MATCH(NameSubj,NmSubMatch,0)),"",INDEX(GradeList,MATCH(NameSubj,NmSubMatch,0))) 
 
or, if you define the following name 
 
MatchNameSubj  refers to:  =MATCH(NameSubj,NmSubMatch,0) 
 
You get the following result 
 
=IF(ISNA(MatchNameSubj),"",INDEX(GradeList,MatchNameSubj)) 
 
There are simpler ways of doing this, but this is flexible and dynamic. 
 
HTH, 
 
Jay

Eclipse wrote: 
 
Hi, 
 
If the first intersection in your crosstab is cell F2, corresponding to 
 
Peter (cell E2) and Math (cell F1), select that cell (make it the 
 
active cell. 
 
Then go to Insert > Name > Define 
 
call the name 'NameSubj' and use the above in the refers to box.  The 
 
defined name is used so that the end formula need not be array-entered. 
 
Adjust the function definition to suit your actual data.  Just make 
 
sure that the column reference is anchored for the names and the row 
 
reference is anchored for the subjects. 
 
Also note, that since you are in the active sheet, you do not need to 
 
enter the sheet names in the named formula. 
 
=$E2&"|"&F$1 
 
should suffice. 
 
Regards, 
 
Jay

Hi, 
 
I have some data arrnaged this way: 
 
Name    Subject   Grade 
 
Peter     Math         A 
 
Peter     English      B 
 
Peter     Science      C 
 
Sally      English      A 
 
Sally      Math          D 
 
How can i re-arrange them to give a crosstab them this in excel? 
 
Name  English   Math  Science 
 
Peter      A            B        C 
 
Sally       A            D 
 
etc? 
 
I tried Pivot tables but that only seem to give you the count. 
 
Hope you can help.

Hi Jay, 
 
Thanks for that.  I am not sure what this line means.. 
 
I have defined the rest and inserted the formula in the appropriate places, 
 
but F2 isthe body of the table. Can you help further? 
 
Here's what I have so far.. 
 
A              B             C             D             E 
 
F                G             H 
 
1  Name Subject Grade      English      Math   Science 
 
2  Peter Math A  Peter #VALUE! #VALUE! #VALUE! 
 
3  Peter English B  Sally #VALUE! #VALUE! #VALUE! 
 
4  Peter Science D 
 
5  Sally Math A 
 
6  Sally English A 
 
7  Sally DT C 
 
Thanks 
 
Seb