Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Switching Subcategories into Categories and Vice Versa
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Switching Subcategories into Categories and Vice Versa
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. wrote in message oups.com... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Switching Subcategories into Categories and Vice Versa
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Switching Subcategories into Categories and Vice Versa
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 " wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Switching Subcategories into Categories and Vice Versa
... Debra Dalgleish's
contribution to Chip Pearson's website: Think it's Jon Peltier's site, not Chip's <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Switching Subcategories into Categories and Vice Versa
wrote
.. 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 copypasting 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,(TRI M(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 Singapore http://savefile.com/projects/236895 xdemechanik --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Switching Subcategories into Categories and Vice Versa
Perhaps a quick sample to illustrate:
http://cjoint.com/?eriPkbmLUD conorfinnegan_wks.xls -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Switching Subcategories into Categories and Vice Versa
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 "Max" wrote: ... Debra Dalgleish's contribution to Chip Pearson's website: Think it's Jon Peltier's site, not Chip's <g -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|