Strip Flat File Table into less entries

  • Thread starter Thread starter msnyc07
  • Start date Start date
M

msnyc07

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?
 
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
 
Hey thanks for all that. I did paste in and nothing happened, tried typing by
hand and get a "Formula contains an error" though :(
 
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!
 
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
 
Back
Top