Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Charleswdowd
 
Posts: n/a
Default Summarizing field quantities for specific records


A file that I work with represents work done by cable installers. Each
row represents a work order (Job). Many of the fields represent
customer information and tech information but most of the fields
represent specific two character work codes that the techs bill. The
quantity of work codes billed is located in the cell where the record
and field intersect.

Example:

Acct # Tech # Address Work Order Number date Work Code 1 (JF) Work
Code 2 (JA) Work Code 3 (AA) Work Code 4 (AB) Work Code 5
33333 5555 17 elm 555753434 5/5/2005 6 3 0 0 1
33334 5556 18 elm 555753435 5/6/2005 0 4 0 1 2
33335 5557 19 elm 555753436 5/7/2005 0 5 0 2 3
33336 5558 20 elm 555753437 5/8/2005 9 6 0 3 4
33337 5559 21 elm 555753438 5/9/2005 10 7 0 4 5
33338 5560 22 elm 555753439 ####### 0 8 0 0 6
33339 5561 23 elm 555753440 ####### 0 9 6 0 7
33340 5562 24 elm 555753441 ####### 13 10 7 0 8
33341 5563 25 elm 555753442 ####### 14 0 8 0 0
33342 5564 26 elm 555753443 ####### 0 0 9 9 0
33343 5565 27 elm 555753444 ####### 6 0 10 10 0
33344 5566 28 elm 555753445 ####### 17 0 11 11 0


The actual file that I work with has over 200 fields and usually only 5
to 6 different work codes are ever billed on any given work order; the
file looks like a sea of empty cells or zeros. I would like to be able
to summarize which and how many work codes are on any given work order.
And determine the most common coding combinations.

Example:

Work Order CODES

555753434 JF JF JF JF JF JF JA JA JA
555753436 JA, JA, JA, JA, JA, AB, AB

Or

Work Order Codes QTY

555753434 JF 6
“ “ JA 3


Code Combo Appears this many times
JF, JF, JF & AA 1,745
JF, JF, & AA 1,002
AA 237


--
Charleswdowd
------------------------------------------------------------------------
Charleswdowd's Profile: http://www.excelforum.com/member.php...o&userid=29017
View this thread: http://www.excelforum.com/showthread...hreadid=547688

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I show all field data in a pivot table, instead of blank Alastair Scott Excel Discussion (Misc queries) 3 August 17th 05 07:08 PM
Deleting specific records [email protected] Excel Discussion (Misc queries) 6 June 22nd 05 11:35 PM
How do I display records using a key field in Excel? Vic from Texas Excel Worksheet Functions 1 March 1st 05 11:16 PM
Text manipulaion (summarizing customer records) Index function probably not good choice Paul Buob Excel Discussion (Misc queries) 1 February 1st 05 07:41 AM
Pivot Tables..I give up... Debutante Excel Worksheet Functions 4 January 21st 05 11:43 PM


All times are GMT +1. The time now is 11:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"