Strip Flat File Table into less entries
in [Functions]
|
Prev: XMAS
Next: Excel 2007 AutoSaves
From: msnyc07 on 25 Dec 2009 16:50 I have a table I found that is essentially a 'hierarchical' table but laid out in such a way I can't really use it w/o further processing. Essentially it is currently something like this Type | Type Code | Subtype | SubType Code | SubSubType | SubSubType Code I really just want to end up with Type | Type Code | Parent Code So I might have Fruit | 100000 | Citrus | 100100 | Orange | 1001001 Fruit | 100000 | Citrus | 100100 | Lemon | 1001002 Fruit | 100000 | Melon | 100200 | Cantelope | 100201 Fruit | 100000 | Melon | 100200 | Honeydew | 100202 Meat | 200000 | Beef | 200100 | Tenderloin | 200101 Meat | 200000 | Beef | 200100 | Brisket | 200102 Meat | 200000 | Pork | 200200 | Bacon | 200201 and I want Fruit | 100000 | Melon | 100200 | 100000 Cirtus | 100100 | 100000 Orange | 100101| 100100 Lemon | 100102 | 100100 Cantelope | 100201 | 100200 Honeydew | 100202 | 100200 Meat | 200000 Beef | 200100 | 200000 Pork | 200200 | 200000 Tenderloin | 200101 | 200100 Brisket | 200102 | 200100 Bacon | 200201 | 200200 Is there an easy function I could use to process the sheet thusly?
From: Max on 25 Dec 2009 18:28 Here's one formulas construct which gets you all the way to Rome ... Assume your source data as posted is in Sheet1, in A2:F2 down In another sheet, In A2: =OFFSET(Sheet1!$A$2,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3)*2) In B2: =OFFSET(Sheet1!$B$2,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3)*2) Copy A2:B2 down to exhaust the data (until zeros are returned). Cols A and B transforms the source data in Sheet1 neatly into 2 cols (and ideal for downstream pivot analysis. You just need to put in the col headers into A1:B1) In D2: =IF(A2=0,"",IF(COUNTIF(A$2:A2,A2)>1,"",ROW())) In E2: =IF(ROWS($1:1)>COUNT(D:D),"",INDEX(A:A,SMALL(D:D,ROWS($1:1)))) Copy E2:D2 down. Col E will return the list of uniques (fruits) from col A Put this in F2, array-enter ie press CTRL+SHIFT+ENTER to confirm the formula: =IF($E2="","",IF(COLUMNS($A:A)>COUNTIF($A$2:$A$1000,$E2),"",INDEX($B$2:$B$1000,SMALL(IF($A$2:$A$1000=$E2,ROW($1:$999)),COLUMNS($A:A))))) Copy F2 across to I2 (say) -- you should copy across by as many cols required to cover the max expected number of corresponding figs per unique fruit -- fill down. That should round it up and deliver exactly the final results that you seek in cols E to I (hide away/minimize cols A to D). You should modify the ranges in the expression in F2 to suit the actual extents (I catered for 999 rows. Use the smallest range which is large enough). Success? Celebrate it, hit the YES below -- Max Singapore --- "msnyc07" wrote: > I have a table I found that is essentially a 'hierarchical' table but laid > out in such a way I can't really use it w/o further processing. Essentially > it is currently something like this > > Type | Type Code | Subtype | SubType Code | SubSubType | SubSubType Code > > I really just want to end up with > > Type | Type Code | Parent Code > > So I might have > > Fruit | 100000 | Citrus | 100100 | Orange | 1001001 > Fruit | 100000 | Citrus | 100100 | Lemon | 1001002 > Fruit | 100000 | Melon | 100200 | Cantelope | 100201 > Fruit | 100000 | Melon | 100200 | Honeydew | 100202 > Meat | 200000 | Beef | 200100 | Tenderloin | 200101 > Meat | 200000 | Beef | 200100 | Brisket | 200102 > Meat | 200000 | Pork | 200200 | Bacon | 200201 > > and I want > Fruit | 100000 | > Melon | 100200 | 100000 > Cirtus | 100100 | 100000 > Orange | 100101| 100100 > Lemon | 100102 | 100100 > Cantelope | 100201 | 100200 > Honeydew | 100202 | 100200 > Meat | 200000 > Beef | 200100 | 200000 > Pork | 200200 | 200000 > Tenderloin | 200101 | 200100 > Brisket | 200102 | 200100 > Bacon | 200201 | 200200 > > Is there an easy function I could use to process the sheet thusly?
From: msnyc07 on 25 Dec 2009 18:46 Hey thanks for all that. I did paste in and nothing happened, tried typing by hand and get a "Formula contains an error" though :( "Max" wrote: > Here's one formulas construct which gets you all the way to Rome ... > Assume your source data as posted is in Sheet1, in A2:F2 down > In another sheet, > In A2: > =OFFSET(Sheet1!$A$2,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3)*2) > In B2: > =OFFSET(Sheet1!$B$2,INT((ROWS($1:1)-1)/3),MOD(ROWS($1:1)-1,3)*2) > Copy A2:B2 down to exhaust the data (until zeros are returned). Cols A and B > transforms the source data in Sheet1 neatly into 2 cols (and ideal for > downstream pivot analysis. You just need to put in the col headers into A1:B1) > > In D2: > =IF(A2=0,"",IF(COUNTIF(A$2:A2,A2)>1,"",ROW())) > In E2: > =IF(ROWS($1:1)>COUNT(D:D),"",INDEX(A:A,SMALL(D:D,ROWS($1:1)))) > Copy E2:D2 down. Col E will return the list of uniques (fruits) from col A > > Put this in F2, array-enter ie press CTRL+SHIFT+ENTER to confirm the formula: > =IF($E2="","",IF(COLUMNS($A:A)>COUNTIF($A$2:$A$1000,$E2),"",INDEX($B$2:$B$1000,SMALL(IF($A$2:$A$1000=$E2,ROW($1:$999)),COLUMNS($A:A))))) > Copy F2 across to I2 (say) -- you should copy across by as many cols > required to cover the max expected number of corresponding figs per unique > fruit -- fill down. That should round it up and deliver exactly the final > results that you seek in cols E to I (hide away/minimize cols A to D). You > should modify the ranges in the expression in F2 to suit the actual extents > (I catered for 999 rows. Use the smallest range which is large enough). > Success? Celebrate it, hit the YES below > -- > Max > Singapore > --- > "msnyc07" wrote: > > I have a table I found that is essentially a 'hierarchical' table but laid > > out in such a way I can't really use it w/o further processing. Essentially > > it is currently something like this > > > > Type | Type Code | Subtype | SubType Code | SubSubType | SubSubType Code > > > > I really just want to end up with > > > > Type | Type Code | Parent Code > > > > So I might have > > > > Fruit | 100000 | Citrus | 100100 | Orange | 1001001 > > Fruit | 100000 | Citrus | 100100 | Lemon | 1001002 > > Fruit | 100000 | Melon | 100200 | Cantelope | 100201 > > Fruit | 100000 | Melon | 100200 | Honeydew | 100202 > > Meat | 200000 | Beef | 200100 | Tenderloin | 200101 > > Meat | 200000 | Beef | 200100 | Brisket | 200102 > > Meat | 200000 | Pork | 200200 | Bacon | 200201 > > > > and I want > > Fruit | 100000 | > > Melon | 100200 | 100000 > > Cirtus | 100100 | 100000 > > Orange | 100101| 100100 > > Lemon | 100102 | 100100 > > Cantelope | 100201 | 100200 > > Honeydew | 100202 | 100200 > > Meat | 200000 > > Beef | 200100 | 200000 > > Pork | 200200 | 200000 > > Tenderloin | 200101 | 200100 > > Brisket | 200102 | 200100 > > Bacon | 200201 | 200200 > > > > Is there an easy function I could use to process the sheet thusly?
From: Herbert Seidenberg on 25 Dec 2009 18:53 Excel 2007 PivotTable No formulas needed. Utility for further processing unknown, but it sure looks appetizing. http://c0444202.cdn.cloudfiles.rackspacecloud.com/12_25_09.xlsx
From: Max on 25 Dec 2009 19:14
Best thing to give you is a working example: http://cjoint.com/?mAbiANHCdV Success, finally? Thump the air, hit the YES below -- Max Singapore --- "msnyc07" wrote: > Hey thanks for all that. I did paste in and nothing happened, tried typing by > hand and get a "Formula contains an error" though :( |