Organising Table

[rajltd]

Can anyone tell me the macro or some formula that can change the data in sheet1 to appear as in sheet2

Thanks for help

Raj


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


I can send the excel workbook, but i dont know where to attach it for your reference.

Thanks

Raj

 

[TonyJollans]

Hi rajltd,

If it is a one-off, it's a trivial manual task, so is this something you want to do on an ongoing basis and, if so, what are the variables? The number of rows? The number of columns? The presence of blank rows? Or anything else?

Enjoy,
Tony

 

[SkipVought]

Hi,

Your table of data ought to have this structure to be, what is called in database design terms, normalized...

Grade 	Year 	Score 	School 	Subject 
1     	1991 	40    	ABC    	Maths   
2     	1992 	50    	ABC    	Maths   
3     	1993 	60    	ABC    	Maths   
4     	1994 	70    	ABC    	Maths   
5     	1995 	80    	ABC    	Maths   
6     	1996 	90    	ABC    	Maths   
7     	1997 	40    	ABC    	Maths   
8     	1998 	50    	ABC    	Maths   
9     	1999 	60    	ABC    	Maths   
10    	2000 	70    	ABC    	Maths   
11    	2001 	80    	ABC    	Maths   
12    	2002 	90    	ABC    	Maths   
1     	1991 	42    	XYZ    	Maths   
2     	1992 	52    	XYZ    	Maths   
3     	1993 	62    	XYZ    	Maths   
4     	1994 	72    	XYZ    	Maths   
5     	1995 	82    	XYZ    	Maths   
6     	1996 	92    	XYZ    	Maths   
7     	1997 	42    	XYZ    	Maths   
8     	1998 	52    	XYZ    	Maths   
9     	1999 	62    	XYZ    	Maths   
10    	2000 	72    	XYZ    	Maths   
11    	2001 	82    	XYZ    	Maths   
12    	2002 	92    	XYZ    	Maths   
1     	1991 	41    	PQR    	Maths   
2     	1992 	51    	PQR    	Maths   
3     	1993 	61    	PQR    	Maths   
4     	1994 	71    	PQR    	Maths   
5     	1995 	81    	PQR    	Maths   
6     	1996 	91    	PQR    	Maths   
7     	1997 	41    	PQR    	Maths   
8     	1998 	51    	PQR    	Maths   
9     	1999 	61    	PQR    	Maths   
10    	2000 	71    	PQR    	Maths   
11    	2001 	81    	PQR    	Maths   
12    	2002 	91    	PQR    	Maths   
1     	1991 	45    	ABC    	Science 
2     	1992 	55    	ABC    	Science 
3     	1993 	65    	ABC    	Science 
4     	1994 	75    	ABC    	Science 
5     	1995 	85    	ABC    	Science 
6     	1996 	95    	ABC    	Science 
7     	1997 	45    	ABC    	Science 
8     	1998 	55    	ABC    	Science 
9     	1999 	65    	ABC    	Science 
10    	2000 	75    	ABC    	Science 
11    	2001 	85    	ABC    	Science 
12    	2002 	95    	ABC    	Science 
1     	1991 	44    	XYZ    	Science 
2     	1992 	54    	XYZ    	Science 
3     	1993 	64    	XYZ    	Science 
4     	1994 	74    	XYZ    	Science 
5     	1995 	84    	XYZ    	Science 
6     	1996 	94    	XYZ    	Science 
7     	1997 	44    	XYZ    	Science 
8     	1998 	54    	XYZ    	Science 
9     	1999 	64    	XYZ    	Science 
10    	2000 	74    	XYZ    	Science 
11    	2001 	84    	XYZ    	Science 
12    	2002 	94    	XYZ    	Science 
1     	1991 	43    	PQR    	Science 
2     	1992 	53    	PQR    	Science 
3     	1993 	63    	PQR    	Science 
4     	1994 	73    	PQR    	Science 
5     	1995 	83    	PQR    	Science 
6     	1996 	93    	PQR    	Science 
7     	1997 	43    	PQR    	Science 
8     	1998 	53    	PQR    	Science 
9     	1999 	63    	PQR    	Science 
10    	2000 	73    	PQR    	Science 
11    	2001 	83    	PQR    	Science 
12    	2002 	93    	PQR    	Science

With this structure (which took me about 2 minutes to construct from your examples) I can do a pivot table report which I did in about 15 seconds (and I can change columns or subtotals or order in about 5 seconds)

Sum of Score 	     	      	Subject 	        
School       	Year 	Grade 	Maths   	Science 
ABC        	1991 	1     	40      	45      
        	1992 	2     	50      	55      
        	1993 	3     	60      	65      
        	1994 	4     	70      	75      
        	1995 	5     	80      	85      
        	1996 	6     	90      	95      
        	1997 	7     	40      	45      
        	1998 	8     	50      	55      
        	1999 	9     	60      	65      
        	2000 	10    	70      	75      
        	2001 	11    	80      	85      
        	2002 	12    	90      	95      
PQR        	1991 	1     	41      	43      
        	1992 	2     	51      	53      
        	1993 	3     	61      	63      
        	1994 	4     	71      	73      
        	1995 	5     	81      	83      
        	1996 	6     	91      	93      
        	1997 	7     	41      	43      
        	1998 	8     	51      	53      
        	1999 	9     	61      	63      
        	2000 	10    	71      	73      
        	2001 	11    	81      	83      
        	2002 	12    	91      	93      
XYZ        	1991 	1     	42      	44      
        	1992 	2     	52      	54      
        	1993 	3     	62      	64      
        	1994 	4     	72      	74      
        	1995 	5     	82      	84      
        	1996 	6     	92      	94      
        	1997 	7     	42      	44      
        	1998 	8     	52      	54      
        	1999 	9     	62      	64      
        	2000 	10    	72      	74      
        	2001 	11    	82      	84      
        	2002 	12    	92      	94

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

BTW,

With just a few point 'n' clicks, I can generate, among OTHER reports,

Average Math scores for grades 1 - 3 in Each School

Max Science scores for grade 6 in all schools

and on and on

Go Pivot Tables!

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[rajltd]

Thanks Tony and Skip,

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....

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...

Raj

 

[rajltd]

can anyone reply to this mail

 

[SkipVought]

rajtd

For the table to be useful for data analysis, it is better to be normalized. This would mean transforming the data you rceive into the normalized table format I described.

In that format, you will be able to get the information you need in a very short amount of time.

Please describe more in detail what you mean by, "set up a vector, search in that verctor for grades..."

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[rajltd]

Thanks Skip,

I want to know how u normalise the table which is in unorganised format. This was just my thought (about vector). There can be better ways of doing the normalisation, but how to normalise this table without cut-paste-copy is what i am looking for.

Thanks

 

[SkipVought]

give this a try.

Run this code with the sheet active that has the input data from all schools. The program will create a new sheet with consolidated data. Hope it works the way you need...

Sub ConsolidateInputData2()
    Dim wsThis As Worksheet, wsNew As Worksheet
    Dim lFirstRow As Long, lLastRow As Long, r1 As Long, r2 As Long, lRowNew As Long, iLastCol As Integer
    Set wsThis = ActiveSheet
    Set wsNew = Worksheets.Add
    With wsNew
        .Cells(1, 1).Value = "Grade"
        .Cells(1, 2).Value = "Year"
        .Cells(1, 3).Value = "Subject"
        .Cells(1, 4).Value = "Score"
        .Cells(1, 5).Value = "School"
    End With
    With wsThis
        With .UsedRange
            lFirstRow = .Row
            lLastRow = lFirstRow + .Rows.Count - 1
            iLastCol = .Column + .Columns.Count - 1
        End With
        r = lFirstRow
        Do While r <= lLastRow
            With .Cells(r, 1).CurrentRegion
                r1 = .Row
                r2 = r1 + .Rows.Count - 1
            End With
            lRowNew = wsNew.Cells(1, 1).CurrentRegion.Rows.Count + 1
            For iCol = 3 To 4
                'copy the grade & year
                Range(.Cells(r1 + 3, 1), Cells(r2, 2)).Copy _
                    Destination:=wsNew.Cells(lRowNew, 1)
                'copy the subject
                .Cells(r1 + 2, iCol).Copy
                wsNew.Range(wsNew.Cells(lRowNew, 3), wsNew.Cells(lRowNew + r2 - (r1 + 3), 3)).PasteSpecial
                'copy the Score
                Range(.Cells(r1 + 3, iCol), Cells(r2, iCol)).Copy _
                    Destination:=wsNew.Cells(lRowNew, 4)
                'copy School
                .Cells(r1, 1).Copy
                wsNew.Range(wsNew.Cells(lRowNew, iLastCol + 1), wsNew.Cells(lRowNew + r2 - (r1 + 3), iLastCol + 1)).PasteSpecial
                lRowNew = wsNew.Cells(1, 1).CurrentRegion.Rows.Count + 1
            Next
            r = r2 + 4
        Loop
    End With
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com