Best technique to perform multiple calculations ?

[[email protected]]

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

[Bob Umlas]

=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!

<[email protected]> wrote in message
news:[email protected]...
> 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
>

[[email protected]]

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

[Max]

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 <at>yahoo<dot>com
----

<[email protected]> wrote in message
news:[email protected]...
> 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
>

[[email protected]]

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

[Max]

You're welcome, Bob !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
<[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Max]

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 <at>yahoo<dot>com
----

<[email protected]> wrote in message
news:[email protected]...
> 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
>