How to change columns into headers?

[stan255]

Hi, I'm new here so I apologize for my question's lack of clarity.
I want to:
1. Move my C columns to be a header column
2. Move the D column to be under the same C column
3. Remove any duplicate A and B column after moving column C and D
I think its better if I post a picture of what I'm trying to accomplish.

 

[smallxyz]

Hi, I'm new here so I apologize for my question's lack of clarity.
I want to:
1. Move my C columns to be a header column
2. Move the D column to be under the same C column
3. Remove any duplicate A and B column after moving column C and D
I think its better if I post a picture of what I'm trying to accomplish.

The picture is blurred. Another upload?

 

[stan255]

Here's another upload. Is this better?

Direct link to image here: http://s21.postimg.org/mi8oczd9j/excelquestion.png

 

[MarcelBeug]

Assuming:
Column A is sorted in ascending order
Columns A&B are whole numbers
Column B < 100

G2: =SMALL(IF(FREQUENCY(100*$A$2:$A$15+$B$2:$B$15,100*$A$2:$A$15+$B$2:$B$15),$A$2:$A$15),ROWS(G$2:G2))

[B]H2:[/B] =INDEX($B$2:$B$15,SMALL(IF(FREQUENCY(100*$A$2:$A$15+$B$2:$B$15,100*$A$2:$A$15+$B$2:$B$15),ROW($B$2:$B$15)-ROW($B$2)+1),ROWS(H$2:H2)))

I2: =SUMPRODUCT(($A$2:$A$15=$G2)*($B$2:$B$15=$H2)*ISNUMBER(SEARCH(I$1,$C$2:$C$15))*$D$2:$D$15)

H2 is an array formula, to be confirmed with Ctrl+Shift+Enter
Copy I2 to J2
Copy G2 thru J2 down.

 

[stan255]

Hey Marcel, appreciate the help.
I tried out your solution and here's what I got.

There are 2 things that I'm actually looking for here.
1. There is only degree 1. I'm actually looking to add in all degrees for each school.
For school 1, there is degree 1 and degree 3. For school 2, there is degree 3 and degree 5.

2. Is it possible for Excel to detect column C and match it according to the column?
Some schools have bachelor's degree so the column for that school for bachelor's degree should be 0, etc.

This is such a complicated task because the main file has over 17 degree types.

 

[skywriter]

Cross posted.

 

[MarcelBeug]

You probably didn't confirm H2 with Ctrl+Shift+Enter.

 

[Peter_SSs]

In your actual file you appear to have duplicate combinations of school|degree|level with different values in the graduate column. How do expect those rows to be dealt with in the final table?

Example:

Excel Workbook
	A	B	C	D
447	100751	3.0104	Bachelor's degree	18
448	100751	5.0102	Bachelor's degree	10
449	100751	5.0102	Master's degree	4
450	100751	5.0102	Bachelor's degree	3
451	100751	5.0107	Bachelor's degree	0
Sheet2

 

[Ken Cowen]

Have you tried using a Pivot Table?
Ken

Hey Marcel, appreciate the help.
I tried out your solution and here's what I got.

There are 2 things that I'm actually looking for here.
1. There is only degree 1. I'm actually looking to add in all degrees for each school.
For school 1, there is degree 1 and degree 3. For school 2, there is degree 3 and degree 5.

2. Is it possible for Excel to detect column C and match it according to the column?
Some schools have bachelor's degree so the column for that school for bachelor's degree should be 0, etc.

This is such a complicated task because the main file has over 17 degree types.

 

[MarcelBeug]

Seems to be solved already on the other forum. See post #6.