Help parsing an excel spreadsheet and filtering data

**dapdacompx** · 10-26-2010, 12:55 PM

I have an excel spreadsheet that I download from a third party server that contains a single sheet with makes, models, vins and prices of used vehicles sold in my company. I would like to create a script/macro that will take this information and filter it by the make (in column B on the USEDCARS sheet) column and copy those rows that contain that make to a separate sheet that the script will create and label it the name of the make of car. The makes of vehicles that the script needs to filter by are AUDI, BMW, MB, MINI and all other make appearing in that column are copied to a new sheet called OTHER. Then I would like this spreadsheet to compare its rows to another spreadsheet called "sorted used inv" and add any new rows no present in sorted used inv to the sorted used inv other page. Any help would be much appreciated.

**mrice** · 10-26-2010, 01:50 PM

Try this macro

Please Login or Register  to view this content.

**dapdacompx** · 10-27-2010, 02:29 PM

Does the above script handle the comparison with the original spreadsheet?

>Then I would like this spreadsheet to compare its rows to another spreadsheet >called "sorted used inv" and add any new rows no present in sorted used inv to the >sorted used inv other page. Any help would be much appreciated.

**mrice** · 10-27-2010, 04:22 PM

It doesn't.

Would be work posting a copy of the actual sheet to avoid getting it wrong.

**dapdacompx** · 10-27-2010, 05:13 PM

Basically what I need to happen is to have the newly created USEDCARS.xls file that uses your above VB script then compare itself to the UPDATEDUSEDINV.xls file sheet by sheet and then row by row . If a row (vehicle) is present in the USEDCARS.xls file but not in the UPDATEDUSEDINV.xls file then it should copy the entire row to the corresponding sheet name and an x should be placed in an additional column at the end to denote a new item. If a vehicle appears in the UPDATEDUSEDINV.xls file but not in the corresponding same name sheet in USEDCARS.xls then that row should be deleted in the UPDATEDUSEDINV.xls file only if the model of the car matches the sheet name except for the "OTHER" sheet as explained more in depth later in this post. If a vehicle appears in both files, then nothing should happen. The only exception to this is the "OTHER" sheet in the USEDCARS.xls file need to compare its rows to ALL sheets in the UPDATEDUSEDINV.xls file. For this case, if the vehicle (row) from the "OTHER" sheet of the USEDCARS.xls appears in any of the sheets of the UPDATEDUSEDINV.xls file then nothing should happen. However, if the vehicle does not appear on any of the sheets of the UPDATEDUSEDINV.xls file but does appear on the USEDCARS.xls spreadsheets "OTHER" sheet then the row shall add itself to the end of the sheet "OTHER" in the UPDATEDUSEDINV.xls file with an "x" in the last column. Lastly if the vehicle does not appear in the "OTHER" sheet of the USEDCARS.xls file only, but does appear in any of the sheets of the UPDATEDUSEDINV.xls file then it should be deleted from the sheet that it appears on in the UPDATEDUSEDINV.xls file. Please let me know if this makes sense or if you need further clarification. I have attached the two files for reference.

Thanks in advance

**mrice** · 10-31-2010, 01:41 PM

First draft attached.

I've assume that this workbook and the other two will be in the same folder.

You also need to be consistent with the naming of Mecerdes Benz.

Please test this - I haven't had time to do so fully - and get back to me.