duplicate an array and filter the list based on a grouping

[jedale]

I have a list of items in excel that belong to certain groups. (see
below)

--Sheet 1--
A B
1 Electrical 3.1.1
2 Electrical 3.1.2
3 Electrical 3.1.3
4 Mechanical 3.2.1
5 Mechanical 3.2.2

What I would like to do using array functions is obtain an array of
all the items within the same group on another worksheet. (see below)

--Sheet 2--
A B
1 Electrical 3.1.1
2 Electrical 3.1.2
3 Electrical 3.1.3

I already have a list of all the distinct items within sheet 1 column
A. So this part won't be a problem.

Has anybody ever attempted to try this with array functions instead of
doing it in VB. If anybody could help me with this or point me in the
right direction I would greatly appreciate it.

Thanks for any and all help on this subject.
Jeff

 

[jedale]

I noticed a typo in my last message. It should say "I already have a
list of all the distinct items within sheet 1." instead of "I already
have a list of all the distinct items within sheet 1 column A."

Thanks,
Jeff

 

[jedale]

Does anybody know if this it possible?

Thanks,
Jeff

 

[jedale]

Here is my unque item array function:
{=IF(ROWS($1:3)<=SUMPRODUCT((Listing!$H$4:$H$1261<>"")/COUNTIF(Listing!
$H$4:$H$1261,Listing!$H$4:$H$1261&"")),INDEX(Listing!$H$4:$H
$1261,SMALL(IF(Listing!$H$4:$H$1261<>"",IF(MATCH(Listing!$H$4:$H
$1261,Listing!$H$4:$H$1261,0)=ROW(Listing!$H$4:$H$1261)-ROW(Listing!$H
$4)+1,ROW(Listing!$H$4:$H$1261)-ROW(Listing!$H
$4)+1)),ROWS($1:3))),"")}

I was hoping that I could modify this function to then list the groups
of different items within an array. However, I am not familar with
all the different functions and trying to breakup the function into
parts is not working for me.

Thanks,
Jeff

 

[jedale]

Does anybody know if this is possible?

Thanks,
Jeff

 

[jedale]

Well I figured out how to do it. It is a long and tedious work
around. I would be interested in anybodies solution even if I have to
pay for it.

Thanks,
Jeff

 

[jedale]

I would much rather use a simplier solution that doesn't use multiple
cells on the spreadsheet to calculate the information I need to
breakdown the List. Has anybody seen a simple solution to this other
than using macros. I don't care if I have to pay for it, I just want
to know if a solution exists.

Thanks,
-Jeff

 

[Spiky]

I would much rather use a simplier solution that doesn't use multiple
cells on the spreadsheet to calculate the information I need to
breakdown the List. Has anybody seen a simple solution to this other
than using macros. I don't care if I have to pay for it, I just want
to know if a solution exists.

Thanks,
-Jeff

There are 4 choices as far as I can see.

1) Use multiple formulas. One formula cannot exist in multiple cells,
or create formulas in other cells.
2) Use macros. Lots of different ways.
3) Use a Filter. (which is easier to repeat when inside a macro)
4) Sort/copy/paste.

Also, DGET would likely be simpler than your array formula above.
Although it needs a criteria area to be set up, just like a Filter
does.