Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Strip Flat File Table into less entries
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Strip Flat File Table into less entries
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,R OWS($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$100 0,$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? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Strip Flat File Table into less entries
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,R OWS($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$100 0,$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? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Strip Flat File Table into less entries
Excel 2007 PivotTable
No formulas needed. Utility for further processing unknown, but it sure looks appetizing. http://c0444202.cdn.cloudfiles.racks.../12_25_09.xlsx |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Strip Flat File Table into less entries
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 :( |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Strip Flat File Table into less entries
Hi thanks for that, I think you might have misunderstood the challenge, my
fault if so. I don't need a count it is just that a nested table was presented in a clumsy way i.e. Level 3 | Level 2 | Level 1 which led to lots of duplication since I just wanted Level 1 Level 2 Level 3 All I did was this Fruit | Citrus | Orange Fruit | Citrus | Lemon Meat | Beef | Filet Meat | Beef | Pork Vegetable | Green | Broccoli Vegetable | Green | Lettuce Vegetable | Orange | Carrot and copy partial columns down and then got rid of dupes! Thanks though! Merry Christmas! "Max" wrote: 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 :( |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Strip Flat File Table into less entries
Aha, thought I did read your original posting ok, and you did give a
depiction of the desired results. But maybe you changed your mind somewhere along the way. cheers -- Max Singapore --- "msnyc07" wrote: Hi thanks for that, I think you might have misunderstood the challenge, my fault if so. I don't need a count it is just that a nested table was presented in a clumsy way i.e. Level 3 | Level 2 | Level 1 which led to lots of duplication since I just wanted Level 1 Level 2 Level 3 All I did was this Fruit | Citrus | Orange Fruit | Citrus | Lemon Meat | Beef | Filet Meat | Beef | Pork Vegetable | Green | Broccoli Vegetable | Green | Lettuce Vegetable | Orange | Carrot and copy partial columns down and then got rid of dupes! Thanks though! Merry Christmas! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to convert a Pivot Table into a Flat File format? | Excel Discussion (Misc queries) | |||
Excel how do I add a flat 10.00 in a column with 10 entries | Excel Worksheet Functions | |||
how do I convert an excel file to a flat text file | Excel Discussion (Misc queries) | |||
Convert excel file to flat text file | Excel Discussion (Misc queries) | |||
Convert excel file to flat text file | Excel Discussion (Misc queries) |