need help to reorganise a table with 3 columns

Joined
Jan 5, 2012
Messages
38
Hello Forum! Good afternoon! May I ask for your help to pivot a table? Or maybe flip it?
Headers are on row 1. 3 cells only, from Cell A1 to C1.
The cell A1 is a header. It says “Student Number”. The cell b1 is also a header. It says “Allowance from Mum”. Cell C1 says “allowance from Dad”.
The student number (eg 773847 and 819322 and 42900 etc etc) are listed on column A, from cell A2 down to A46. This is not fixed and it differs from file to file.
Column B and C are amount (money). So they are numbers/dollars. Example 70, 43, 24 etc etc

Is there a way to flip and pivot this so that I keep column A still showing Student number but column B is for “allowance from mum” and “allowance from dad” (column B is text/words only) and column C is the amounts (numbers/dollars)?

Thank you so so much! :)
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
Student
Allowance from Mum
Allowance from Dad
Allowance from Mum Allowance from Dad
SUM
1
10
10
Allowance from Mum Allowance from Dad
20
2
20
30
Allowance from Mum Allowance from Dad
50
3
30
20
Allowance from Mum Allowance from Dad
50
4
40
30
Allowance from Mum Allowance from Dad
70
5
50
30
Allowance from Mum Allowance from Dad
80
6
60
30
Allowance from Mum Allowance from Dad
90

<TBODY>
</TBODY>



Would this help

D2 drag down
=IF(ROWS($A1:A$2)>COUNT($B$2:$B$7),"",$B$1&" "&$C$1)
E2 drag down
=IF(ROWS($A$2:A2)>COUNT($B$2:$B$7),"",SUM(B2:C2))
Then create your Pivot table
 
Joined
Jan 5, 2012
Messages
38
Hello, thank you. Is there a way to code it into vba? because i am looking for a way to have this done many many times on several files.

thank you.



StudentAllowance from MumAllowance from DadAllowance from Mum Allowance from DadSUM
11010Allowance from Mum Allowance from Dad20
22030Allowance from Mum Allowance from Dad50
33020Allowance from Mum Allowance from Dad50
44030Allowance from Mum Allowance from Dad70
55030Allowance from Mum Allowance from Dad80
66030Allowance from Mum Allowance from Dad90

<tbody>
</tbody>



Would this help

D2 drag down
=IF(ROWS($A1:A$2)>COUNT($B$2:$B$7),"",$B$1&" "&$C$1)
E2 drag down
=IF(ROWS($A$2:A2)>COUNT($B$2:$B$7),"",SUM(B2:C2))
Then create your Pivot table
 

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
I's sure there is but I can not help.
If you did not get any respond in a day time just bump here or create new topic with "VBA" in the subject.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,840
Members
413,944
Latest member
3xc3ln00b

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top