Conditional Transposing

rooirokbokkie

New Member
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
ABCDE
 2346756

<tbody>
</tbody>
 General chemistry 172

<tbody>
</tbody>
50
 2346756

<tbody>
</tbody>
 Calculus 158

<tbody>
</tbody>
64
2346756
 Linear algebra 161

<tbody>
</tbody>
59
2346756Physics 11662
2346756
 Electricity 122

<tbody>
</tbody>
36
 2346756

<tbody>
</tbody>
Accounting49
 2346756

<tbody>
</tbody>
Mathematics68
 2346756

<tbody>
</tbody>
Geography48
3389657Electricity 12256
3389657General chemistry 17243
3389657
 Physics 158

<tbody>
</tbody>
62
3389657
 Materials science 113

<tbody>
</tbody>
23
3389657Mathematics67
3389657Chemistry56
3389657Accounting87
3389657History45

<tbody>
</tbody>

To here?

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

<tbody>
</tbody>

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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

Replies
11
Views
358
Replies
3
Views
213
Replies
1
Views
540
Replies
9
Views
526
Replies
4
Views
2K

1,221,185
Messages
6,158,406
Members
451,490
Latest member
desktopace

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back