On Fri, 8 Feb 2013 07:28:06 +0000, TimLeonard wrote:

Yes it would be the same as the "Orig Panel Data" sheet...
The purpose of the added rows on the "Summary" sheet is to represent the
max devices per loop that the panel can use. This way the engineer will
know what is available and what has been used in various drawings
against what has been already been programmed in the field. so removing
the empty fields would defeat the purpose...

OK, I've done some work, but it is incomplete, awaiting your answers to my previous post.

The way I would generate a worksheet that has all the possible "merged address" rows, would go something like:
Start with an "Original Panel Data" that somehow includes the corresponding "Merged Address" fields.
This is a problem as the Original Panel Data sheet I have does not have any Merged Addresses; for testing I generated something from your CompareData worksheet by removing all the rows that had a blank "Device Types" field.

Ensure there are no duplicates (there shouldn't be, if I understand things, but check to be sure -- use range.removeduplicates method for all columns)

Produce an array which includes all possible "merged addresses" given the number of loops

================
Function GenLoops(NumLoops) As Variant
'Part 1: L01-L10
'Part 2: D or M
'Part 3: 001-159
Dim MergAddr() As String
Dim i As Long, j As Long, k As Long, m As Long
ReDim MergAddr(1 To NumLoops * 2 * 159)

For i = 1 To NumLoops
For j = 1 To 2
For k = 1 To 159
m = m + 1
MergAddr(m) = "L" & Format(i, "00") & _
IIf(j = 1, "D", "M") & _
Format(k, "000")
Next k
Next j
Next i
GenLoops = MergAddr
End Function
======================

Read Original Panel Data into a variant array
e.g. vOrig = worksheet("Orig Panel Data").usedrange

Create a collection object consisting of the used "merged addresses"
Try to add to this collection from the inclusive list of all possible merged addresses.
If the attempt to add does not produce an error, then we have identified a missing address and this should go into a collection of missing addresses.
Read the collection into a 2D array so we can write it back to the worksheet expeditiously.

Write vOrig to the top of the new (Compare or Summary) worksheet
Write vNewAddresses onto the worksheet below this.

Sort by Merged Address

This method seems more complex to code than iterating through the worksheet, row by row, and testing each row, but by doing everything within VBA, using the collection object, and reading/writing to/from VBA/worksheet using the variant array methodology only at the beginning/end of the routine, it should execute considerably faster, especially with larger databases.

Once we get this part working OK, we can go on to the comparison issue.