Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Random Values | Excel Worksheet Functions | |||
Need to generate random values from a list | Excel Discussion (Misc queries) | |||
Random values from a Triangular Distribution | Excel Programming | |||
counting unique values and matching to quantities | Excel Worksheet Functions | |||
Select a random value from a column of values | Excel Programming |