Quantities of random values
 

Hi all,

I am working on a quote template for doors and I would like to be able to count combinations of information so that the information is displayed on a separate sheet.

The information is based on door type, door height and door width.

Example of quote page...

SC 2340 920
SCE 2340 920
SC 2340 920
FD 2040 920
FD 2040 920

I would like the quantities sheet to recognise repeated combinations and count how many times they repeat as well as displaying the repeated combinations

Example of desired quantity page...

SC 2340 920 2
SCE 2340 920 1
FD 2040 920 2

Here are the problems that I face.
- If I try to use the height column as a criteria range in count or sum functions it calculates the total height value of all doors combined (ie. 2040 x 2 = 4080)
- Pivot tables have the same issue with creating a total height value rather than a total of the amount of times a combination repeats
- The information in the door type, door height and door width columns is completely random so setting a value in the formula isn't an option (I don't believe)
- I've yet to find a method for having a new sheet display, count and sort data into a customised setout

Any help with this would be much appreciated as I'm starting to feel that it's not possible.

Quantities of random values
 

Hi all,

I am working on a quote template for doors and I would like to be
able to count combinations of information so that the information is
displayed on a separate sheet.

The information is based on door type, door height and door width.

Example of quote page...

SC 2340 920
SCE 2340 920
SC 2340 920
FD 2040 920
FD 2040 920

I would like the quantities sheet to recognise repeated combinations
and count how many times they repeat as well as displaying the
repeated combinations

Example of desired quantity page...

SC 2340 920 2
SCE 2340 920 1
FD 2040 920 2

Here are the problems that I face.
- If I try to use the height column as a criteria range in count or
sum functions it calculates the total height value of all doors
combined (ie. 2040 x 2 = 4080)
- Pivot tables have the same issue with creating a total height value
rather than a total of the amount of times a combination repeats
- The information in the door type, door height and door width
columns is completely random so setting a value in the formula isn't
an option (I don't believe)
- I've yet to find a method for having a new sheet display, count and
sort data into a customised setout

Any help with this would be much appreciated as I'm starting to feel
that it's not possible.

Why not add a Qty column instead of listing multiple instances? Better
yet, why not use product IDs...

SC-2340-920
SCE-2340-920
FD-2040-920
SC-2340-920
FD-2040-920

...and use COUNTIF()?...

SC-2340-920 2
SCE-2340-920 1
FD-2040-920 2

...so on your quantities sheet in col A...

SC-2340-920
SCE-2340-920
FD-2040-920

...and in col B

=COUNTIF(Sheet1!A:A,DoorID)

...where DoorID is a col-absolute, row-relative local scope defined name
that refs col A. Put DoorIDs on the quote sheet...

in cols B,C,D:
SC,2340,920

in col A
=B1&"-"&C1&"-"&D1

...so it auto-IDs based on cell values enter (or selected if using DV
dropdowns)!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion