Sorting data

[steph0515]

My company uses a software package that unfortunately is of one level, meaning you can include variables but not exclude. I have been asked to find out how many customers over the past 2 years have been customers of 1 of our services but not another. The Software package Tech Support rep advised me to pull the lists of customers who have used service A and those who have used Service B (actually requires 2 queries since this service is divided into 2 departments) and to sort it in excel to find out who has only used service A.

I ran the queries within the software package and exported the results to excel. I ended up with 1 combined spreadsheet using about 24k rows. I then sorted the results by billing address and used conditional formatting to find the duplicates (those who have used Service A, those who have used Service B department 1 and Service B department 2). I then put the duplicates into another workbook in order to find out which duplicates had received ONLY Service B. This was about 5k rows of data.

The data uses individual columns for first name, last name, address 1, address 2, city, state, zip, phone 1, phone 2, cell phone, email addy and service type (service A, service B1 or service B2). I then went through the sheet of duplicates separating them with a row so that I could look at the services used for each set of the same address. (i.e. 2 Smith St had 3 issues and on separate dates called us for Serivce A or Service B1 or Service B2) I put in a new column where I typed a "q" so that I knew which ones had only been customers through the Service B1 or 2 work they requested.

This took 4 hours since I am unsure as how to sort through duplicates when each set of rows (2-7) share a billing address and have received all 3 types of work or any combination of work on 2 occasions within that previous 2 year period.

There has to be a way to do this in a few steps and in less than 4 hours! (preferably something I can do in under an hour)

ex.
Address Service Type
123 4th Street Service A
123 4th Street Service B1
123 4th Street Service B2

1313 West St Service A
1313West St Service A

15 Adams St Service B2
15 Adams St Service B1

IDEALLY I want to sort through all of the address fields and then excel will recognize that the address field was the same and look for those who have entries ONLY for Service B (either 1 or 2) and then put them on a separate sheet

So in my example 15 Adams St and 123 4th Street would end up on those who have only received Service B (either 1 or 2) work done list to then copy

[jrussell]

How about, copy the addresses (uniques only) to another sheet and then use a couple of columns of vlookups to the original sheets to find if each address has recieved a type of service. Then you can sort the resulting data by the vlookup columns to get your lists.

[steph0515]

How about, copy the addresses (uniques only) to another sheet and then use a couple of columns of vlookups to the original sheets to find if each address has recieved a type of service. Then you can sort the resulting data by the vlookup columns to get your lists.

Maybe I'm not being clear. I apologize its hard sometimes when you know what you want/need in your head :-D

Unique addresses are simple. i can sort those by service type. It is the duplicate addresses that pose a predicament. I'll be more exact. My boss was looking for all clients who have received svcs in the past 3 years. Both plumbing and HVAC; however, he wanted a list of those who have recieved HVAC services ONLY. There are 2 types of HVAC service (warranty and install). Some clients have had all 3 types of service, some have had only HVAC services and some have had only plumbing services.

Yes I can sort and whatnot, but normally this will be upwards of 1-2k entries. manually looking at the duplicates to see if 1 Elm street is a duplicate because they received both HVAC and Plumbing Svcs or ONLY HVAC or ONLY plumbing will only work if this is the only project on my plate for an entire day.

Here is what I have:

column A - address
column B - service type

each row is a different address so ex:

1 Elm St - service A
1 Elm St - service B
1 Elm St - service B1
2 West St - service B1
2 West St - service B2
3 Wood Ave - service A
3 Wood Ave - service A

I would want whatever programming to go through all of the rows and be able to say you want to keep the address 2 West St since they have received ONLY service B (either 1 or 2) as I would then take this list and add it to the unique entries.

[jrussell]

Take a look at this file. You can use a pivot table to summarize the data, then copy and paste the data, values only, to another location to sort the whole thing. You may want to rename your services - Instead of B, B1, B2, just make them all "B" for the purposes of this exercise.