Mis-aligned data on data refresh

Hi Robert

Any help is appreciated as I am drawing a blank with this problem.

the spreadsheet I have created is extracting data in columns A through F from an external data source in SQL server. The spreadsheet is being used as a planning tool so the data I am extracting is sales info. I am entering manual data (i.e dates/comments) into columns G & H. When I refresh the extracted data new rows can be added or removed (i.e. if an order had been deleted for example) but because the manual data is not linked in anyway to the extracted data the manual data remains where it was originally input and does not move with the refreshed data.

Eg

In Row 1 is sales order 1234 with a comment 'test' against it. When data is refreshed sales order 1234 moves to row 2 but comment 'test' remains in row 1.

Pleae help!!!!

Hey,

No need to sort, though it would make it easier to write the code for it.

I can write the code, I just need a bit of information:

The column the Unique ID is in.
Do you have headers?
The worksheet name that he Extracted data is on.
The worksheet name for the updated data.
What version of Excel do you use?

Regards,

Robert D. Specian Jr.

Hey,

I'm not sure if it will work well in 2000, because I am writing in in 2007, but we can troubleshoot it as we go.

With the workbook open hit Alt+F11 this should open up the VBA Editor window. There should be a place to add a new module. Add one in and paste the following code:

Sub test()
Dim cnt, rw As Integer
Dim val As Variant

cnt = Worksheets("Data").Range("A20000").End(xlUp).Row
On Error Resume Next

For rw = cnt To 2 Step -1
val = Empty
val = Application.WorksheetFunction.VLookup(Worksheets("Data").Range("A" & rw).Value, Worksheets("WORKS ORDER DOWNLOAD").Range("A:A"), 1, False)
If val = Empty Then
Worksheets("Data").Rows(rw).Delete
End If
nxt:
Next rw
End Sub

Click to expand...

When you hit play, it will delet the lines in Data whose Unique ID is not found in WORKS ORDER DOWNLOAD.

Will that work for you?

Regards,

Robert D. Specian Jr.