Data Comparison And Filter
I have a list of products which I drop ship – each product has a unique SKU but this covers the whole product and not the options. For example, a red shirt would be D1234 but a yellow car would also be D1234.
My suppliers publish an CSV file daily which lists their current stock situation – they, however, list their products with a different SKU per option so a red shirt would be D1234RED and a yellow car would be D1234YELLOW and so on. The supplier spreadsheet has three columns – SKU, description and qty. CUrrently there are 2400 line items on the supplier spreadsheet.
The saving grace is that the SKU schema I use for my products is similar to the supplier schema i.e in the shirts example above they both begin with D1234, for example. This is common across all the product lines, however, not all the SKU’s are one letter and then 4 numbers but this is reflected in my schema as well so, either way, they will be format on both columns.
In total I sell 172 unique products from this supplier so I have a spreadsheet with the 172 SKU’s as a single column and I then need a way of using my spreadsheet as a master list to filter out all the items on the suppliers spreadsheet which I don’t sell and therefore don’t need to be aware of any stock problems.
When this is completed I should then be left with approximately 300 items on the supplier spreadsheet which I will then manually check every few days for stock issues i.e. only the items which I sell.
I have no preference how this is achieved – if it can be done in Excel, Access great or if it can be achieved via PHP/MySQL it makes no difference to me but it must be able to scale and it has to be simple to use.
Basically all I want to have to do is to take two csv files and somehow compare and filter them and get an output list which shows the SKU, description and qty for each item and its variants for all the items I stock.
