Conditional Transposing

[rooirokbokkie]

I used the VLOOKUP Function to combine 3 separate data sets for 5000 students into one data set. But now I'm hopelessly stuck. I need to somehow transpose the subject and the relevant mark to columns into a new sheet but only if the subject count exceeds 100. So it needs to run down column B (high school subjects) first and if there are more than 100 instances of a specific subject then it should write that subject to a new column in a new sheet and allocate the relevant marks to the students. Rinse and repeat until column B is finished. Then do the same with column D (university subjects).

So for argument's sake, in the table below only Accounting, Mathematics, Calculus 158 and General chemistry 172 had more than 100 counts and thus ended up in the second sheet.


Essentially, I'd like to know how if it's possible to go from here:

Sheet 1

A	B	C	D	E
2346756 <tbody> </tbody>			General chemistry 172 <tbody> </tbody>	50
2346756 <tbody> </tbody>			Calculus 158 <tbody> </tbody>	64
2346756			Linear algebra 161 <tbody> </tbody>	59
2346756			Physics 116	62
2346756			Electricity 122 <tbody> </tbody>	36
2346756 <tbody> </tbody>	Accounting	49
2346756 <tbody> </tbody>	Mathematics	68
2346756 <tbody> </tbody>	Geography	48
3389657			Electricity 122	56
3389657			General chemistry 172	43
3389657			Physics 158 <tbody> </tbody>	62
3389657			Materials science 113 <tbody> </tbody>	23
3389657	Mathematics	67
3389657	Chemistry	56
3389657	Accounting	87
3389657	History	45

<tbody>
</tbody>

To here?


Sheet 2

	Accounting	Mathematics	Calculus 158	General chemistry 172​
2346756​	49	68	64	50
3389657	87	67	-	43

<tbody>
</tbody>

 

[venkat1926]

when you creae data base you have to be careful no unnecessary blank rows and columns no blank cells . google search for creating a good databas .

anyhow with existing data selected:

use pivot table

table labels D
column labels A
sum of column E

the result will be like this

pivot table

*	A	B	C	D	E	F	G	H	I	J	K
1	*	*	*	*	*	*	*	*	*	*	*
2	*	*	*	*	*	*	*	*	*	*	*
3	Sum of E	Column Labels	*	*	*	*	*	*	*	*	*
4	Row Labels	Calculus 158	Electricity 122	General chemistry 172	Linear algebra 161	Materials science 113	Physics 116	Physics 158	(blank)	Grand Total	*
5	2346756	64	36	50	59	*	62	*	*	271	*
6	3389657	*	56	43	*	23	*	62	*	184	*
7	(blank)	*	*	*	*	*	*	*	*	*	*
8	Grand Total	64	92	93	59	23	62	62	*	455	*
9	*	*	*	*	*	*	*	*	*	*	*
10	*	*	*	*	*	*	*	*	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:90.4px;"><col style="width:112px;"><col style="width:90.4px;"><col style="width:142.4px;"><col style="width:117.6px;"><col style="width:136.8px;"><col style="width:76px;"><col style="width:76px;"><col style="width:50.4px;"><col style="width:77.6px;"><col style="width:64px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4