Search code examples
excelexcel-formulaexcel-2007vlookupworksheet-function

Grouping rows by area codes

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.

Solution

  • 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.)