R
rajltd
Can anyone tell me the macro or some formula that can change the data in
sheet1 to appear as in sheet2
Sheet1
ABC
In UK
Grade Year Maths Science
1 1991 40 45
2 1992 50 55
3 1993 60 65
4 1994 70 75
5 1995 80 85
6 1996 90 95
7 1997 40 45
8 1998 50 55
9 1999 60 65
10 2000 70 75
11 2001 80 85
12 2002 90 95
XYZ
In USA
Grade Year Maths Science
1 1991 42 44
2 1992 52 54
3 1993 62 64
4 1994 72 74
5 1995 82 84
6 1996 92 94
7 1997 42 44
8 1998 52 54
9 1999 62 64
10 2000 72 74
11 2001 82 84
12 2002 92 94
PQR
In Australia
Grade Year Maths Science
1 1991 41 43
2 1992 51 53
3 1993 61 63
4 1994 71 73
5 1995 81 83
6 1996 91 93
7 1997 41 43
8 1998 51 53
9 1999 61 63
10 2000 71 73
11 2001 81 83
12 2002 91 93
Sheet2
Grade Year Maths Science School
1 1991 40 45 ABC
2 1992 50 55 ABC
3 1993 60 65 ABC
4 1994 70 75 ABC
5 1995 80 85 ABC
6 1996 90 95 ABC
7 1997 40 45 ABC
8 1998 50 55 ABC
9 1999 60 65 ABC
10 2000 70 75 ABC
11 2001 80 85 ABC
12 2002 90 95 ABC
1 1991 42 44 XYZ
2 1992 52 54 XYZ
3 1993 62 64 XYZ
4 1994 72 74 XYZ
5 1995 82 84 XYZ
6 1996 92 94 XYZ
7 1997 42 44 XYZ
8 1998 52 54 XYZ
9 1999 62 64 XYZ
10 2000 72 74 XYZ
11 2001 82 84 XYZ
12 2002 92 94 XYZ
1 1991 41 43 PQR
2 1992 51 53 PQR
3 1993 61 63 PQR
4 1994 71 73 PQR
5 1995 81 83 PQR
6 1996 91 93 PQR
7 1997 41 43 PQR
8 1998 51 53 PQR
9 1999 61 63 PQR
10 2000 71 73 PQR
11 2001 81 83 PQR
12 2002 91 93 PQR
Infact, I will be using this on an ongoing basis... and its not just 3
schools, it will be loads of them in different part of the world....
lots of data which cannot be cut and pasted into another sheet... need
something to change the database format
The thing is, I get this data in form of a set for single school(ABC
school, etc.), but the better part is, the variables in the first
column is always the same(grade).... The only thing that changes is
marks for maths, science, etc...
I think, I will have to set up a vector, search in that verctor for
grades and then list the data along with school name in the last
column...
I have sent the excel workbook (lookup.xls) as attachment for your
reference.
Thanks
Raj
+----------------------------------------------------------------+
| Attachment filename: lookup.xls |
|Download attachment: http://www.excelforum.com/attachment.php?postid=349674|
+----------------------------------------------------------------+
sheet1 to appear as in sheet2
Sheet1
ABC
In UK
Grade Year Maths Science
1 1991 40 45
2 1992 50 55
3 1993 60 65
4 1994 70 75
5 1995 80 85
6 1996 90 95
7 1997 40 45
8 1998 50 55
9 1999 60 65
10 2000 70 75
11 2001 80 85
12 2002 90 95
XYZ
In USA
Grade Year Maths Science
1 1991 42 44
2 1992 52 54
3 1993 62 64
4 1994 72 74
5 1995 82 84
6 1996 92 94
7 1997 42 44
8 1998 52 54
9 1999 62 64
10 2000 72 74
11 2001 82 84
12 2002 92 94
PQR
In Australia
Grade Year Maths Science
1 1991 41 43
2 1992 51 53
3 1993 61 63
4 1994 71 73
5 1995 81 83
6 1996 91 93
7 1997 41 43
8 1998 51 53
9 1999 61 63
10 2000 71 73
11 2001 81 83
12 2002 91 93
Sheet2
Grade Year Maths Science School
1 1991 40 45 ABC
2 1992 50 55 ABC
3 1993 60 65 ABC
4 1994 70 75 ABC
5 1995 80 85 ABC
6 1996 90 95 ABC
7 1997 40 45 ABC
8 1998 50 55 ABC
9 1999 60 65 ABC
10 2000 70 75 ABC
11 2001 80 85 ABC
12 2002 90 95 ABC
1 1991 42 44 XYZ
2 1992 52 54 XYZ
3 1993 62 64 XYZ
4 1994 72 74 XYZ
5 1995 82 84 XYZ
6 1996 92 94 XYZ
7 1997 42 44 XYZ
8 1998 52 54 XYZ
9 1999 62 64 XYZ
10 2000 72 74 XYZ
11 2001 82 84 XYZ
12 2002 92 94 XYZ
1 1991 41 43 PQR
2 1992 51 53 PQR
3 1993 61 63 PQR
4 1994 71 73 PQR
5 1995 81 83 PQR
6 1996 91 93 PQR
7 1997 41 43 PQR
8 1998 51 53 PQR
9 1999 61 63 PQR
10 2000 71 73 PQR
11 2001 81 83 PQR
12 2002 91 93 PQR
Infact, I will be using this on an ongoing basis... and its not just 3
schools, it will be loads of them in different part of the world....
lots of data which cannot be cut and pasted into another sheet... need
something to change the database format
The thing is, I get this data in form of a set for single school(ABC
school, etc.), but the better part is, the variables in the first
column is always the same(grade).... The only thing that changes is
marks for maths, science, etc...
I think, I will have to set up a vector, search in that verctor for
grades and then list the data along with school name in the last
column...
I have sent the excel workbook (lookup.xls) as attachment for your
reference.
Thanks
Raj
+----------------------------------------------------------------+
| Attachment filename: lookup.xls |
|Download attachment: http://www.excelforum.com/attachment.php?postid=349674|
+----------------------------------------------------------------+