Formulas to emulate advanced filter feature

[scottyd]

Hey guys,
I recently worked on an excel project for my old boss and created a working template only to learn that Office:Mac does not support the VBA macros i wrote...I cannot figure out to achieve my goal w/o using an advanced data filter command. This shees will be used multiple times per day, so i need to write a formula to accomplish the goals. as running the data filter manually four times per sheet is to cumbersome.

here's what im trying to do: the shop is creating cabinet doors, so there will be fifty or so doors in an order with a variety of lengths and widths. Im trying to write a sheet that will sum the lengths and width and order and sum them from longest to shortest for each part.

I've been able to sort the list longest to shortest, and sum the number of each lengths and width. but in doing so i have list that has longest to shortest widths with duplicate entries IE:
10,9,8,8,7,6,6,6,5. I used the advanced filter function to copy the list to another location and copy unique entries only IE 10,9,8,8,7,6,6,6,5 --> 10,9,8,7,6,5
any ideas on how i might complete this operation using a formula?


Second:

Im using a sum-match formula to sum the number of doors that have a particular length and width. What ends up happening is that i get a double counted list. in the above example i will get the sum of doors with a width of 8 twice and 6 three times. i was able to use and AND greater then adder to my forumla so that i would only get the sum for the first unique value in the list. Then i used the advanced filter with a condition of greater then zero and extracted only the "real" sums from the count list. Again, im stumped as to how i might accomplish this w/o the advanced filter feature.

its a really long post, my apologies!

complete example

5 doors, three are a unique size.

quantity 2 10X10
quantity 1 8X10
quantity 1 8X8
quantity 1 6X8

number length width
2 10 10
1 8 10
1 8 8
1 6 8


how to list lengths and widths from largest to shortest and total each unique length and width....

im currently using the LARGE function to order the pieces longest to shortest (this creates a list with duplicate entries as 10 is the first and second largest width in the list)

and how to sum each unique w/o double counting or having a list with zeros....


for those of you who made it to the bottem of this post: THANKS for sticking with me!

[JBeaucaire]

To get a unique list would require the use of

1) an array formula, which can cause a performance hit if the range being evaluated it too big
2) a "helper" column added to the data to create an index that increments at each new value, then you can pull the items into your "unique list" elsewhere by simply grabbing each indexed value

For instance, assuming you are wanting unique values from column A, in column B at B2 you could enter this formula:

=IF(ISNUMBER(MATCH($A2, $A$1:$A1, 0)), N($B1), N($B1)+1)

Copy that formula down and you will get a series of numbers, each new number represents a new unique value.

Then in another column or sheet you index column A using the values in column B. So, in AA1 you might put this:

=IF(MAX(B:B)>ROW(A1), "", INDEX($A:$A, MATCH(ROW(A1), $B:$B, 0))

Copy that cell down and your unique list is created.

[JBeaucaire]

Thanks for the feedback.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[scottyd]

Jerry,
Im going to light a roman candle in your name!
i ended up using a vlookup to solve the count problem, your solution for filtering out the data worked great!
thanks again!