0

I have a table of customers to which my company ships products. The problem is that these customers need to be sorted by their area codes, so that the products can be sent to the appropriate shipping companies (we have two partner companies that ship to certain parts of the country). Each company sent us a list of area code numbers to which they can ship and I need to divide the Excel sheet into two sheets, each containing the customers with the area codes compatible with the respective company.

I tried to solve this problem with VLOOKUP function, but it only works on individual row basis, and I need a solution that will find all rows that contain a number from the specified group of area codes.

Another way would be IF function that would put a True or False (one IF function for each company) value in new column and then I could sort by that value, and copy the data into a new sheet. This approach would work, but the IF function would be extremely long and hard to control.

Can you suggest a way to solve this problem?

Edit to incorporate details provided via Comment:

Presently I have about 5,000 rows but in future it might be more though I doubt over 10,000 rows.

Improve this question
5
  • 1
    Index Match? Pivot Table? Filtering the table? A bit of custom VBA? Have you tried any of these things? Without an idea of the data, it's hard to see how big a problem you have.
    – Skip Intro
    Commented May 24, 2013 at 9:21
  • Well the data shouldn't be important, it contains personal data of the customer, and among that data is one column containing area code number. I need to find all the rows in which the value of the area code column exists in a certain range
    – NDraskovic
    Commented May 24, 2013 at 9:26
  • If you have 50 rows of data or a thousand or a million makes a difference to the solution.
    – Skip Intro
    Commented May 24, 2013 at 9:31
  • In this case there are 5000 rows, in future it might be more, but I don't think it will go over 10 000 rows per sheet
    – NDraskovic
    Commented May 24, 2013 at 9:36
  • Unless you are specifically after a code solution, this question does not belong here. It should be asked at SuperUser instead. Without a data sample and an expected result it's pretty hard to recommend an approach. I have a feeling that it can be achieved with sorting and dynamic ranges, though. I'll flag this to be moved to SuperUser, where it belongs.
    – teylyn
    Commented May 24, 2013 at 10:21

1 Answer 1

Reset to default
1

A VLOOKUP seems very promising, of the kind =VLOOKUP($B2,F:G,1,0) in C2 copied across and down as required, with a layout as below:

SO16731095 example

This does not group as you say you require (but do you really need to?) because it seems possible some locations will be served by both shippers. You might resolve this by flagging those rows where both are viable and then by sorting to split into three groups (Shipper1 only, Shipper2 only, both) before transferring the ranges as desired.

Edit in response to OPs comment

If you can be certain there is no overlap between Shippers, a single column with this formula, say in E2copied down, might be preferable: 

=IF(ISERROR(MATCH(B2,F:F,0)>0),"Shipper2","Shipper1")

and would not routinely show #N/A. (This assumes no area is outside the range of both shippers.)

Improve this answer
3
  • Hm, is it possible to check the result of the VLOOKUP function, and if it returns value, the IF function should write "Shipper1", and if it returns #N/A, it should write "Shipper2". Is this possible, and how would I define the #N/A value in the IF function?
    – NDraskovic
    Commented May 24, 2013 at 10:41
  • There are no overlaps because we got the area codes for one company, and the other ships everything else (meaning that the Shipper1 can only dispatch certain area codes, and Shipper2 can dispatch all, but we use him only for the products Shipper1 can't dispatch). Thanks for your answer, I will try to implement it, and see if it does the job
    – NDraskovic
    Commented May 24, 2013 at 11:08
  • 1
    I understand perfectly, and your answer helped me (I just finished the separation, using your method). I know that I didn't provide you with much information, but we have a very strict rule about preserving private informations about our clients, so I have to describe the best I can, without using the data I'm working with. Anyway, thank you for your answer, it solved my problem and saved me a lot of trouble.
    – NDraskovic
    Commented May 24, 2013 at 12:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.