How to look through a workbook for a combo of cells that does not appear in worksheet

[RollerRagerMD]

Sorry for the tortured title, I couldn't think of a good way to write it.

I have a worksheet titled "Warehouse." I have a workbook titled "Load." Within Load, there are several invoice records, one per row. The invoice record includes these four attributes: OriginCity, OriginState, OriginZip, and OriginWarehouse.

In the Warehouse worksheet, I have each unique instance of: OriginWarehouse, OriginCity, OriginState, and OriginZip. Each instance also has a WarehouseID which is simply a number in ascending order:

87 Soutwest Moulding DALLAS TN 37745
88 Alexandria Molding WILKES BARRE GA 31408
89 Balterm BALTIMORE OK 73107

Occasionally, the Load workbook gets updated with new records. Occasionally, those records include a new warehouse that isn't in the Warehouse worksheet.

Is there a way to have Excel go through the Load workbook and check if there are any new warehouses (based on the name, city, state, and zip) that are not included in the Warehouse worksheet, and then add those warehouses to the Warehouse worksheet?

[Leith Ross]

Hello RollerRagerMD,

If the warehouses are listed in ascending order then wouldn't a new warehouse then be one with a number higher than the last one shown the "Warehouse" sheet?

[RollerRagerMD]

Well that's the thing. In the "Load" workbook, the warehouses do not have a warehouse ID. Here's an example of a record from that table.

Please Login or Register  to view this content.

When I add records with a new combos of OriginWarehouse, OriginCity, and OriginState, I'd like to be able to have Excel go through and add those new combos into the Warehouse book.

[Leith Ross]

Hello RollerRagerMD,

Can you post an example workbook? I take it that a warehouse is always associated with the same city, state, and zip?

[RollerRagerMD]

Yes, it is. Some warehouses have multiple presences but the idea is that each combination of a warehouse, city, state, and zip is unique. I'll post a sample of both books.

"Load" is the example of the main sheet with all of the data, including occasional new warehouses.

"Warehouse" is the example of the Warehouse sheet where each unique warehouse is assigned an ID. I'm trying to get new warehouses from Load into Warehouse automagically.

Load.xlsx
Warehouse.xlsx

[Leith Ross]

Hello RollerRagerMD,

I see, the numbers are assigned as the warehouse location is added to the sheet. I thought the number was an actually warehouse id number. Is there a possibility of duplicate listings?

[RollerRagerMD]

Do you mean are there duplicate instances of the warehouses? In Load.xlsx, yes. Most of the warehouses are used multiple times. When I first compiled the list for Warehouse.xlsx, I think I used the "find duplicates" function or something along those lines to organize them.

If you mean "could I have duplicates in the Warehouse.xlsx file," ideally I wouldn't but it could be done. I'd like to have one record for each unique warehouse so that any shipments from that warehouse all have the same WarehouseID.

[Leith Ross]

Hello RollerRagerMD,

On the "Warehouse" sheet, you have 3 different warehouse listings in Charleston, SC and with the same ZIP. The only difference is the warehouse name. These names don't appear on the "Load" sheet or do they?