excel wizards pls help

Post by **X-R-Cist** » Wed Nov 02, 2005 4:02 am

i have 2 rows of data i need sorting...
ill explain the situation
one row is job numbers, the other is the driver number (couriers)
when a driver gets a job that is not his he will call it back, if he is being a smart ass which they all are they will call back the job several times and it will record it. so if a driver has only received 1 wrong job and calls it back 10 times, it will show up as 10 jobs.
obviously this throws out the data and we need it to be accurate.
now say i send job 12345 to driver 5 and he sends it back, then i send the same job to driver 10 and he calls it back, then i send it to driver 12 and he doesnt send it back, i need that to count that 2 jobs have been sent back
the spread sheet looks sort of like this

job number driver number
12345................... 5
12345.................. 10
99999.................. 15
99999................. 15
99999................. 15
88888................. 14

as you can see there are 2 duplicates, and 3 unique job numbers

the final result i need to know how many drivers sent a unique job back
in the above example driver 15 sent 1, 5 sent 1 10 sent 1 and 14 sent 1.

now my list is massive and cant spend the time going through every single one manually and need a quick fix formula, or pivot table or something to quickly sort it out.
ive probably confused all of you so if you dont get something let me know

Post by **mjrpes** » Wed Nov 02, 2005 4:24 am

AFAIK, Excel is not the application of choice for a problem like this. Access would do the job just fine. With Access, you run the data through 2 queries. The first would delete duplicates, and the second would give you count totals. If you want I can send you an Access file with these queries.

Post by **X-R-Cist** » Wed Nov 02, 2005 4:32 am

now why didnt i think of that. im always arguing that excel can do anything...let me down

yeah if you can send me those queries that would help me out a lot.

Post by **mjrpes** » Wed Nov 02, 2005 4:38 am

I'll can just post it here for ya

http://ciole.net/quake3/job_driver.mdb

Post by **Sanction** » Wed Nov 02, 2005 7:05 am

Your columns woul have to be flopped so Driver Number is on the far left.

Unique Records
1. Select the two columns
2. Select Data > Filter > Advanced Filter
3. Check the unique records
This will overwrite the current columns, you could also have it create the columns at a different location

Count
4. Select a Cell to create a new column where you are consolidating
5. Select Data > Consolidate
Follow the setup I have in the 6. pic