All -- I have searched all 37 pages of SQL/EXCEL questions and cannot find the distinct issue I am trying to solve for. It is to UPDATE/INSERT/ARCHIVE(MOVE) rows in Excel workbook based on SQL results. I currently have a functioning MSSQL 2008r2 ODBC workbook connection within an Excel 2010 worksheet which pulls back roughly 38 columns. I need the following to occur:
Based on the SQL results returned lookup for a match to a DistinctID (B:B) in worksheet OPEN
- IF DistinctID NOT EXIST, THEN insert all new values across 38 columns in worksheet OPEN
- IF DistinctID EXIST and STATUS column = 'CLOSED' (from a previous value not 'CLOSED'), THEN UPDATE column all changed values and MOVE row to separate worksheet named CLOSED
- IF DistinctID EXIST and STATUS column <> 'CLOSED' and ANY column values have changed, THEN UPDATE column values in same worksheet leaving row in worksheet OPEN
- IF DistinctID EXIST and all values are unchanged, THEN no update to row in worksheet OPEN
I have a macro which runs and copies original data and inserts new, but it is proving to be of little value.