Good afternoon,
I apologise in advance for not knowing some terminology that may be helpful in the following request.
The information I am trying to gather is formatted similar to the following: Colomn A- Customer number, Column B- list of all services the customer has. So, multiple instances of the account number occur depending on how many services the customer has. The problem I am running into is that for several services, there is a "sub-service" if you will (listed in column B as well) that affects the way the main service is handled. For instance, customer 1000 has Service 1. This one is easy and works well. However, Customer 1001 has Service 1 and Service A. The way we report our service is to report all Service 1 customers where Service A is not present. If Service A is present, exclude the instance of Service 1 for that customer as it is counted in the Service A count. What gets rather complicated is there are approximately 80 main services that are reported on (only 1 sub-service though) and I am working with a very large data sheet (approximately 180k rows and 110 columns).
I know the best answer is to reduce my data set size but that is an undertaking that involves several people and time that I unfortunately don't have right at the moment and I don't have the computing power to quickly decrease the size of the data sheet I am working with. So what I am hoping for help with is a very efficient formula or set of formulas to help accomplish the reporting necessary.
In an earlier rendition, I used a customer index to accomplish this by doing the following: copy and paste customer number Column into new sheet and remove duplicates (still left with approximately 40k unique IDs), countif the customer number occurred with Service A corresponding giving me a 1 next to any customer number with service A, now go back to master file and vlookup each line against this index so now anytime an account number that has Service A is shown a helper column displays Service A on each row, now I used sumifs to add all instances of service 1 where Service A did not occur on the same row. The problem is that even running this on a server, I am still getting massively bogged down anytime I calculate. I expect quite poor performance just due to the sheer size of the file but am hoping there may be a more efficient way to achieve the end result that won't rely on looking at the information so many times.
Please let me know if I can provide any more information.
Thanks!
Zane
I apologise in advance for not knowing some terminology that may be helpful in the following request.
The information I am trying to gather is formatted similar to the following: Colomn A- Customer number, Column B- list of all services the customer has. So, multiple instances of the account number occur depending on how many services the customer has. The problem I am running into is that for several services, there is a "sub-service" if you will (listed in column B as well) that affects the way the main service is handled. For instance, customer 1000 has Service 1. This one is easy and works well. However, Customer 1001 has Service 1 and Service A. The way we report our service is to report all Service 1 customers where Service A is not present. If Service A is present, exclude the instance of Service 1 for that customer as it is counted in the Service A count. What gets rather complicated is there are approximately 80 main services that are reported on (only 1 sub-service though) and I am working with a very large data sheet (approximately 180k rows and 110 columns).
I know the best answer is to reduce my data set size but that is an undertaking that involves several people and time that I unfortunately don't have right at the moment and I don't have the computing power to quickly decrease the size of the data sheet I am working with. So what I am hoping for help with is a very efficient formula or set of formulas to help accomplish the reporting necessary.
In an earlier rendition, I used a customer index to accomplish this by doing the following: copy and paste customer number Column into new sheet and remove duplicates (still left with approximately 40k unique IDs), countif the customer number occurred with Service A corresponding giving me a 1 next to any customer number with service A, now go back to master file and vlookup each line against this index so now anytime an account number that has Service A is shown a helper column displays Service A on each row, now I used sumifs to add all instances of service 1 where Service A did not occur on the same row. The problem is that even running this on a server, I am still getting massively bogged down anytime I calculate. I expect quite poor performance just due to the sheer size of the file but am hoping there may be a more efficient way to achieve the end result that won't rely on looking at the information so many times.
Please let me know if I can provide any more information.
Thanks!
Zane
