Looping to compare values in different worksheets?

Val,

Since you use terms such as "unique ID" and "recordset", I'm a little curious as to why you aren't using Access. I've made this comment before, but it applies again, the task that you want a macro for is trivial when performed in a database application such as Access. Of course, if you don't have Access, then it's understandable why you're not using it. But if you do have Access it would really be in your best interest to learn it. This little task is easy in Access. [end preaching ;-))]

However, I'll work up a little macro to do this operation in Excel. Give me a little time (like about 15 - 30 minutes) and I'll post it.

Maybe someone will beat me to it. LOL

in a bit

The macro

Val,

Here it is (at least I hope this is what you wanted :-)) ):

Sub InsertRecord()
Dim Sht1LastRow As Long
Dim i As Long
Dim j As Long

Sht1LastRow = Sheets("Sheet1").Cells(65536, 1).End(xlUp).Row
j = 2

For i = 2 To Sht1LastRow
If Sheets("Sheet2").Cells(j, 1).Value <> Sheets("Sheet1").Cells(i, 1).Value Then
Sheets("Sheet2").Rows(j).Insert Shift:=xlDown
' Sheets("Sheet2").Cells(j, 1).Value = Sheets("Sheet1").Cells(i, 1).Value
End If
j = j + 1
Next i
End Sub

You said that you wanted a blank row inserted. This does that. If, however, you want the unique ID added after the row is inserted, then "uncomment" (i.e., remove the single quote (') from the line which reads " Sheets("Sheet2").Cells(j, 1).Value = Sheets("Sheet1").Cells(i, 1).Value ".

Adjust the sheet names if/as necessary.

enjoy

,

: I'm hoping this can be done with a macro... : I have a workbook with two worksheets, several hundred rows each. The columns in each worksheet are identical, with A:A being a unique ID field. The data is sorted prior to output so that the records for each ID appear in the same row in each worksheet. My problem is that Sheet2 contains an incomplete recordset that needs to be compared to Sheet1 which is complete. : I want to compare the data in Sheet2,A:A to the data in Sheet1,A:A and perform a row insert based on the result. : For example, if Sheet2,A2 = Sheet1,A2 then nothing happens, but if the values are different, I want to insert a new, blank row automatically in Sheet2, above A2... then loop through the rest of the records and repeat as necessary. : Please tell me this is possible with a macro. What once was a small monthly task is now a daily request that can no longer be accomplished manually... : Val

Re: The macro

You're right, Access is the culprit behind this. A little background...

The recordsets that I am comparing are the results of two separate SELECT queries against an Access database, one for clients that have enrolled (complete recordset, Sheet1) and one for the enrolled clients who have completed their training (incomplete recordset, Sheet2).

All IDs appearing on Sheet2 will also be on Sheet1, but there will be many missing IDs on Sheet2, as these clients have not yet completed and have not been entered into Access table that generates Sheet2.

Using a macro, I was able to output each query to separate worksheets in a workbook, but then I am having to manually insert rows on Sheet2 to account for the missing IDs and make the rows line up between the two worksheets.

I'll let you know how it goes....Your help is greatly appreciated. I'm almost brain-dead at this point :-)

Thanks ! ~Val , Here it is (at least I hope this is what you wanted :-)) ): Sub InsertRecord() Dim Sht1LastRow As Long Dim i As Long Dim j As Long Sht1LastRow = Sheets("Sheet1").Cells(65536, 1).End(xlUp).Row j = 2 For i = 2 To Sht1LastRow If Sheets("Sheet2").Cells(j, 1).Value <> Sheets("Sheet1").Cells(i, 1).Value Then Sheets("Sheet2").Rows(j).Insert Shift:=xlDown ' Sheets("Sheet2").Cells(j, 1).Value = Sheets("Sheet1").Cells(i, 1).Value End If j = j + 1 Next i End Sub You said that you wanted a blank row inserted. This does that. If, however, you want the unique ID added after the row is inserted, then "uncomment" (i.e., remove the single quote (') from the line which reads " Sheets("Sheet2").Cells(j, 1).Value = Sheets("Sheet1").Cells(i, 1).Value ". Adjust the sheet names if/as necessary. enjoy

:

It worked! Thanks a bunch, Bariloche!

you've literally trimmed off hours of work for me. I'm off to go enjoy what's left of my weekend.

Cheers!

Val

Re: The macro

Val,

What are you going to do with "Sheet2" after you run the macro. Do you want to update one of your Access tables or create a report or what? Looks like you're looking for folks who are enrolled but haven't completed the training?

Give me a little more info and maybe I can post some SQL so that you can get Access to do what you need.

thanks

You're right, Access is the culprit behind this. A little background... The recordsets that I am comparing are the results of two separate SELECT queries against an Access database, one for clients that have enrolled (complete recordset, Sheet1) and one for the enrolled clients who have completed their training (incomplete recordset, Sheet2). All IDs appearing on Sheet2 will also be on Sheet1, but there will be many missing IDs on Sheet2, as these clients have not yet completed and have not been entered into Access table that generates Sheet2. Using a macro, I was able to output each query to separate worksheets in a workbook, but then I am having to manually insert rows on Sheet2 to account for the missing IDs and make the rows line up between the two worksheets. I'll let you know how it goes....Your help is greatly appreciated. I'm almost brain-dead at this point :-) Thanks ! ~Val

Re: It worked! Thanks a bunch, Bariloche!

Val,

You're welcome. Post later this weekend, if you'd like, and I'll see if I can't help you with the Access side of things. I'm pretty sure that we can make this chore a thing of the past.

take care

you've literally trimmed off hours of work for me. I'm off to go enjoy what's left of my weekend. Cheers!

Re: The macro

Sheet2 is being used create a summary report on who has not yet completed training. This report will be used to communicate the incomplete status to other staff.

The reason for inserting a blank row is so the operators can see at a quick glance (the blank rows are later highlighted using conditional formatting) which clients are incomplete, and when contacting the clients by phone, be able to write notes that will later be entered into the database.

The macro works great! Thanks again for your help!
~Val , What are you going to do with "Sheet2" after you run the macro. Do you want to update one of your Access tables or create a report or what? Looks like you're looking for folks who are enrolled but haven't completed the training? Give me a little more info and maybe I can post some SQL so that you can get Access to do what you need. thanks

: You're right, Access is the culprit behind this. A little background... : The recordsets that I am comparing are the results of two separate SELECT queries against an Access database, one for clients that have enrolled (complete recordset, Sheet1) and one for the enrolled clients who have completed their training (incomplete recordset, Sheet2). : All IDs appearing on Sheet2 will also be on Sheet1, but there will be many missing IDs on Sheet2, as these clients have not yet completed and have not been entered into Access table that generates Sheet2. : Using a macro, I was able to output each query to separate worksheets in a workbook, but then I am having to manually insert rows on Sheet2 to account for the missing IDs and make the rows line up between the two worksheets. : I'll let you know how it goes....Your help is greatly appreciated. I'm almost brain-dead at this point :-) : Thanks ! ~Val