Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Best technique to perform multiple calculations ?
Hi,
Little help please, with best technique to perform multiple calculations. A1 FileNum Customer (TotalWorkOrders by Customer) --------------------------------------------------- A2 04555501 Acme A3 04555502 Acme A4 4555503 Acme A5 04123401 BBB A6 04123402 BBB A7 03111101 XYZ A8 31111101 xyz I'm trying to calculate; the total number of work-orders by customer, where a workorder can have multiple projects associated with it. The work-order must be extracted or derived from the FileNum column The 8 digit FileNum is made up of three components. 04-5555-01 (year-workorder-project) This is where I need help, can I do this in one array formula?; 1. custom format? to add leading zeros to those without. 2. Extract work-order by Triming leading## and trailng## ,sometimes### 3. Count equal workorders only once 4. Sum the number of work-orders conditional upon customer I know how to use the functions individually, but am confused as to grouping them in one formula. eg. Trim/Count/Sum IF "criteria1"="criteria2" grateful for any ideas on best practice, bobd |
#2
|
|||
|
|||
=COUNTIF(C1:C1000,"Acme")
=COUNTIF(C1:C1000,"BBB"), etc. You can use Data/Filter/Advanced filter to get a unique list of your customers, then , assuming this list starts in F2: =COUNTIF($C$1:$C$1000,F2) and fill down to the last customer. Bob Umlas, MVP FYI, I'll be leading a LIVE 1-hour FREE webinar on tips & tricks on January 27 from 4-5 PM est. It's done from your computer. To find out more & register, go to http://www.iil.com, click on the yellowish rectangle on the left "Try a free webinar", click the link for Microsoft Tips & Tricks. Maybe I'll "see" you there! wrote in message oups.com... Hi, Little help please, with best technique to perform multiple calculations. A1 FileNum Customer (TotalWorkOrders by Customer) --------------------------------------------------- A2 04555501 Acme A3 04555502 Acme A4 4555503 Acme A5 04123401 BBB A6 04123402 BBB A7 03111101 XYZ A8 31111101 xyz I'm trying to calculate; the total number of work-orders by customer, where a workorder can have multiple projects associated with it. The work-order must be extracted or derived from the FileNum column The 8 digit FileNum is made up of three components. 04-5555-01 (year-workorder-project) This is where I need help, can I do this in one array formula?; 1. custom format? to add leading zeros to those without. 2. Extract work-order by Triming leading## and trailng## ,sometimes### 3. Count equal workorders only once 4. Sum the number of work-orders conditional upon customer I know how to use the functions individually, but am confused as to grouping them in one formula. eg. Trim/Count/Sum IF "criteria1"="criteria2" grateful for any ideas on best practice, bobd |
#3
|
|||
|
|||
Bob,
thanks for your reply and look forward to your webcast. Wasn' t able to get your suggestion to work, Have not had much luck with CountIf function. Won't this function count duplicate work orders? What if I I copy the filenum column to new column called workorder, filter out workorder number , and then create columns by customer then use =COUNTIF(C1:C1000,"Acme") Is that what you meant? bobd |
#4
|
|||
|
|||
Perhaps something along these lines may be of some help ..
Assume the table below is in Sheet1, cols B and C, data from row2 down FileNum Customer 04555501 Acme 04555502 Acme 4555503 Acme 04123401 BBB 04123402 BBB 03111101 XYZ 3111101 xyz [corrected] We'll use 3 empty cols to the right Put in D2: =IF(B2="","",LEFT(MID(TEXT(B2,"00000000"),3,99),4) ) (Col D extracts the work orders [WO] from col B) Put in E2: =C2&D2 Put in F2: =IF(E2="","",IF(COUNTIF($E$2:E2,E2)1,"",ROW())) Select D2:F2, fill down to say, F1000, to cover expected data in cols B and C (Cols E & F will identify unique Cust - WO and assign these uniques with arbitrary row#s in col F) In Sheet2 ------------- List across in A1:C1 : Cust, WO, Total WO Put in A2: =IF(ISERROR(SMALL(Sheet1!$F:$F,ROWS($A$1:A1))),"", INDEX(Sheet1!C:C,MATCH(SMA LL(Sheet1!$F:$F,ROWS($A$1:A1)),Sheet1!$F:$F,0))) Copy A2 across to B2 Put in C2: =IF(B2="","",COUNTIF(Sheet1!D:D,B2)) Select A2:C2, fill down to C1000 to cover the same range size in Sheet1 Cols A and B will extract the unique list of Cust and associated WO, col C returns the total (count) of the WOs in col B For the sample data in Sheet1, you'll get: Cust WO Total WO Acme 5555 3 BBB 1234 2 XYZ 1111 2 (rest are blanks: "") -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- wrote in message oups.com... Hi, Little help please, with best technique to perform multiple calculations. A1 FileNum Customer (TotalWorkOrders by Customer) --------------------------------------------------- A2 04555501 Acme A3 04555502 Acme A4 4555503 Acme A5 04123401 BBB A6 04123402 BBB A7 03111101 XYZ A8 31111101 xyz I'm trying to calculate; the total number of work-orders by customer, where a workorder can have multiple projects associated with it. The work-order must be extracted or derived from the FileNum column The 8 digit FileNum is made up of three components. 04-5555-01 (year-workorder-project) This is where I need help, can I do this in one array formula?; 1. custom format? to add leading zeros to those without. 2. Extract work-order by Triming leading## and trailng## ,sometimes### 3. Count equal workorders only once 4. Sum the number of work-orders conditional upon customer I know how to use the functions individually, but am confused as to grouping them in one formula. eg. Trim/Count/Sum IF "criteria1"="criteria2" grateful for any ideas on best practice, bobd |
#5
|
|||
|
|||
Many thanks Max, The code you provided works wonderfully. I never would have figured that on my own. I'll spend tonight trying to understand the logic behind it. thanks again, great stuff bobd |
#6
|
|||
|
|||
You're welcome, Bob !
Thanks for the feedback .. -- Rgds Max xl 97 --- GMT+8, 1° 22' N 103° 45' E xdemechanik <atyahoo<dotcom ---- wrote in message oups.com... Many thanks Max, The code you provided works wonderfully. I never would have figured that on my own. I'll spend tonight trying to understand the logic behind it. thanks again, great stuff bobd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
calculations from data in other worksheets | Excel Worksheet Functions | |||
XML / parent with multiple children and with multiple children | Excel Discussion (Misc queries) | |||
How do I avoid saving multiple Excel/Wordfiles for versioning purp | Excel Discussion (Misc queries) | |||
multiple entries | Excel Worksheet Functions | |||
Count rows based on multiple criteria | Excel Worksheet Functions |