Switching Subcategories into Categories and Vice Versa

[conorfinnegan]

I know this is over my head and I am not sure Excel is the right
program, but I have had luck in the past with this group and figured
someone more knowledgable than me would know if it is or isn't
possible. What I have is some Categories that are made up of
subcategories and there is data associated with these subcategories,
such as # of people in each of these subcategories under the
categories. What I want to do is make the subcategories the categories
and the categories the subcategories under these new categories with
the appropriate data attached to the categories. Let me explain.

I have data like that below.

Soccer team (column A)
(column B has the grade and column c has the # of kids)
3rd grade - 10 people
4th grade - 20 people
7th grade - 15 people

Baseball team (column A)
(column B has the grade and column c has the # of kids)
4th grade - 5 people
7th grade - 10 people

I know want to run a formula in excel that will automatically give me
the following results.

3rd grade
Soccer team - 10 people

4th grade
Soccer team - 20 people
Baseball team - 5 people

7th grade
Soccer team - 15 people
Baseball team - 10 people

***note this is not the actual data I am flipping but it would work the
same exact way. It wouldn't be a big deal except that I have many many
pages of this and it takes me a couple hours to manipulate this.

Thanks in advance for the help. Please steer me in the right
direction.

 

[Barb Reinhardt]

You could probably do this with a pivot table. I'd explain it more, but I'm
pretty tired and will probably mess up the explanation. Someone else may
be able to help there.

 

[conorfinnegan]

I know I could do this with a pivot table but didn't know if there were
actual formulas that I could possibly run. Thanks for the suggestion
though.

 

[Ron Coderre]

I'm fairly certain a Pivot Table will easily handle your request.

Here's how to start:
<Data><Pivot Table>
Use: Excel
Select your data
Click the [Layout] button

ROW:
Drag the Team field here
Drag the Grade field below Team
Drag the Members field below the Grade field

DATA: Drag the People field here
If it doesn't list as Count of Members...dbl-click it and set it to Count
Click [OK]

Select where you want the Pivot Table...and you're done!

That will list each Grades and Members by Team and the count of Members.

To switch to the second view
Click the Pivot Table Wizard button
Click the [Layout] button
Drag the Grade field to above the Team field

Now you have Teams and Members listed by Grade

For an excellent tutorial on Pivot Tables, see MVP Debra Dalgleish's
contribution to Chip Pearson's website:
http://peltiertech.com/Excel/Pivots/pivotstart.htm

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro

 

[Max]

... Debra Dalgleish's

contribution to Chip Pearson's website:

Think it's Jon Peltier's site, not Chip's <g>

 

[Max]

.. but didn't know if there were
actual formulas that I could possibly run.

Perhaps a close-fit formulas approach ?

Assume source data is in sheet: X,
within A2:C100, eg:

Soccer 3rd grade 10 people
Soccer 4th grade 20 people
Soccer 7th grade 15 people
Baseball 4th grade 5 people
Baseball 7th grade 10 people
etc

Col A = sports
Col B = grade
Col C = people

(Data is assumed all text, placed in X
via sequential copy>pasting from all sheets.
Order of pasting is immaterial.)

Then in another sheet: Y,

Assume listed across in B1, C1, D1, ... are the grades:
3rd grade, 4th grade, 5th grade

and listed down in A2, A3, ... are the sports:
Soccer, Baseball, ..

Put in B2, array-enter (press CTRL+SHIFT+ENTER):
=IF(ISNA(MATCH(1,(TRIM(X!$A$2:$A$100)=TRIM($A2))*(TRIM(X!$B$2:$B$100)=TRIM(B
$1)),0)),"",INDEX(TRIM(X!$C$2:$C$100),MATCH(1,(TRIM(X!$A$2:$A$100)=TRIM($A2)
)*(TRIM(X!$B$2:$B$100)=TRIM(B$1)),0)))

Copy B2 across and fill down to populate

Since we're doing text matching here, to increase robustness,
TRIM(..) has been wrapped everywhere

 

[Max]

Perhaps a quick sample to illustrate:
http://cjoint.com/?eriPkbmLUD
conorfinnegan_wks.xls

 

[Ron Coderre]

YIKES! Absolutley correct, Max! ...and *Peltier* is right in the beginning of
the URL I posted: (http://peltiertech.com/Excel/Pivots/pivotstart.htm)

My apologies for the mix-up.

***********
Regards,
Ron

XL2002, WinXP-Pro