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, that tells me that the extra rows have a different purpose than what I had assumed.

This plus looking at the worksheets and code provokes a few more questions.

Which column on "Orig Panel Data" tells how many "loops"?
I would have assumed it to be the highest number in LoopSelection, but maybe not.
Also, how are you constructing "Merged Address". I'm not seeing the code that does that, and that column does not exist on "Orig Panel Data"

My plan was to start with "Orig Panel Data"; create an array with all of the required "Merged Address" entries, and then check to see which are missing. Once we know which are missing, they can be added at the bottom, and then the entire sheet sorted by Merged Address. However, that column is not present on "Orig Panel Data", and I don't see how to create it.

By the way, I would assume, from what you've posted that the range of acceptable values for Merged Address is:

Part 1 Part 2 Part 3
L01 - L10 D or M 001 - 159

e.g. L01D001 to L10M159

The only consistency I see is that
if LoopSelection=1, then Part 2 = "D"
if LoopSelection=2, then Part 2 = "M"

Your Correct...BTW since the imported worksheet in actually named "PanelData" I change the name above...

See the attached workbook module 2, but I like the code you posted... If you wanted to add a column to the "Paneldata" worksheet after column "F" then it could look at Col. "D" for the Loop, Col. "F" for the D or M (1=Device and 2=Module 3=Zone) and col. "E" for the address so the three columns would makeup the L01D001 Then I suppose it could be used in the array

Actually each loop has 159 "D";s and 159 "M"'s
e.g. Loop 1 = L01D001 to L01D159 & L01M001 to L01M159
up to Loop 10 = L10D001 to L10D159 & L10M001 to L10M159

On Sun, 10 Feb 2013 00:20:45 +0000, TimLeonard wrote:

Response 1:

By the way, I would assume, from what you've posted that the range of
acceptable values for Merged Address is:

Part 1 Part 2 Part 3
L01 - L10 D or M 001 - 159

e.g. L01D001 to L10M159


Actually each loop has 159 "D";s and 159 "M"'s
e.g. Loop 1 = L01D001 to L01D159 & L01M001 to L01M159
up to Loop 10 = L10D001 to L10D159 & L10M001 to L10M159

I did mean to imply that the range would include "D" and "M" for each loop. Sorry if I was not clear

On Sun, 10 Feb 2013 00:20:45 +0000, TimLeonard wrote:

Response 2:

Also, how are you constructing "Merged Address". I'm not seeing the
code that does that, and that column does not exist on "PanelData"
See the attached workbook module 2, but I like the code you posted... If
you wanted to add a column to the "Paneldata" worksheet after column "F"
then it could look at Col. "D" for the Loop, Col. "F" for the D or M
(1=Device and 2=Module 3=Zone) and col. "E" for the address so the three
columns would makeup the L01D001 Then I suppose it could be used in the
array


Looking at the latest workbook; worksheet PanelData, I am not understanding your explanation of how to construct MergedAddress.

Previously you specified that the format of the merged address should be in the format of
LaaXnnn
where aa would be two digits in the range of 1-10 (eg 01-10)
X could be D or M
and nnn would be three digits in the range of 1-159 (eg 001-159)


Previously you wrote that the loops go from L01... to L10..., but column D (LoopSelection) has values of 0, 1, 2.
What to do if the value in column D is zero?

Previously you wrote that what I am calling "X" (or "Part 2" in an earlier post) could be a D or M, but if I am looking at column F, I see values of 1, 2, 3 and 5.
What to do if the value is not 1 or 2?

Previously you wrote that what I am calling nnn above (or "Part 3" in an earlier post) would be in the range of 1-159, but if I am looking at Col "E", I see some values of zero (0).
What to do if the value is zero?


Also in this latest workbook, on the CompareData (or Summary) worksheets, you show Merged addresses which have formats that do not comply with your original specifications!
Here are some:
L00000
L00001
L00002
L00003
L00004
L00005
L00006
L00007
L00008
L00009
L00Z000
L00Z001

Either the specifications are incomplete, or there is a problem with the data in this latest workbook. I've got the basics of the code set up, but I need better specifications to proceed.

-- Ron

This is still the correct format...and the way it would be assigned to a device on the drawings...

In the loopselection column of the "paneldata" worksheet the zero is for zones and can go up to the value of 10 depending on how many loop are installed in the field panel. In the first work book I deleted the rows for zones due to uploading size limits. In the second workbook I removed everything from column I-CC on the "PanelData" worksheet. So if the two were put together then you would have the whole worksheet from the import...

On the "CompareData" worksheet the code from module 1 took the values and made them either Devices or Modules, using the following
.Range("E2").Formula = "=IF(D2=3,""Zone"",IF(D2=2,""Monitor"",IF(D2=1,""D etector"","""")))"
This was the only way I knew to get the "D" or "M" and while the "5" isn't used in this I left it in the comparison... Additionally the current values of 1, 2, 3 and 5 are what is used now but this area could expand or change completely in the future and I would need the ability to modify the code to work....

The range 1-159 represent the programmable device address range...and the zero or zone numbers represent the panels logic...So at this point zeros or zones has been kept in the mix for the comparison. However in my code Module 2 merges the columns it put "L00" on them, which was the best I could do... They should read either Zone 1-999 or Blank for 00-10 but this was too complicated for me to isolate so I left it alone...This resulted in the L00000-L00009 and the L00Z000-L00Z999 you see in the "Summary" and the CompareData" worksheets...

At this point I think the second workbook, as slow as it is and from piecing various codes together, works through all we have discussed except when adding missing addresses... But in some cases, it does not complete to address 001 but would stop at address 005. This causes the "Diff" worksheet to be populated with more cell differences than it should...For example...
L01D159
L01M005
L01M006

Also, if a new loop was added, it doesn't check the "Summary" sheet to see if it needs to be added there as well, nor does it write any data changes from to "CompareData" sheet to the "Summary" sheet such as if the programmed labels were different on the sheets and remove the column "A" project number info...

Perhaps one disconnect is that I made all comments using the "Summary" and "CompareData" sheets, which copies columns from the "PanelData sheets and sorts the data and adds the above mentioned code for adding the missing numbers, and I think now we are discussing using the "PanelData" sheet for that info instead...

I do want to Thank You for time and efforts, I truley appreciate

On Sun, 10 Feb 2013 19:43:27 +0000, TimLeonard wrote:

I feel I am not communicating.



Previously you specified that the format of the merged address should be
in the format of
LaaXnnn
where aa would be two digits in the range of 1-10 (eg 01-10)
X could be D or M
and nnn would be three digits in the range of 1-159 (eg 001-159)

This is still the correct format...and the way it would be assigned to a
device on the drawings...


Previously you wrote that the loops go from L01... to L10..., but column
D (LoopSelection) has values of 0, 1, 2.
What to do if the value in column D is zero?
In the loopselection column of the "paneldata" worksheet the zero is for
zones and can go up to the value of 10 depending on how many loop are
installed in the field panel.

But you wrote that I should "Look at Column D for the loop". If the loops MUST be in the range of one to ten (L01 - L10), what do you want to happen if the value in column D is zero??



Previously you wrote that what I am calling "X" (or "Part 2" in an
earlier post) could be a D or M, but if I am looking at column F, I see
values of 1, 2, 3 and 5.
What to do if the value is not 1 or 2?
On the "CompareData" worksheet the code from module 1 took the values
and made them either Devices or Modules, using the following
Range("E2").Formula =
"=IF(D2=3,""Zone"",IF(D2=2,""Monitor"",IF(D2=1,"" Detector"","""")))"
This was the only way I knew to get the "D" or "M" and while the "5"
isn't used in this I left it in the comparison... Additionally the
current values of 1, 2, 3 and 5 are what is used now but this area could
expand or change completely in the future and I would need the ability
to modify the code to work....

The question I am asking has to do with constructing the MERGED ADDRESS, which is supposed to be a D or an M. Your formula above is being used for a different purpose, and does not result in a D or M.
You wrote: Col. "F" for the D or M (1=Device and 2=Module 3=Zone), but since we are restricted to only D or M, I need to know what to do if the contents of Col F is not a one or a two.

(So far as populating column E in the CompareData sheet, we'll deal with that after I understand how to construct the Merged Address -- it will be simple in code and simple to maintain or expand).


Previously you wrote that what I am calling nnn above (or "Part 3" in an
earlier post) would be in the range of 1-159, but if I am looking at Col
"E", I see some values of zero (0).
What to do if the value is zero?
The range 1-159 represent the programmable device address range...and
the zero or zone numbers represent the panels logic...So at this point
zeros or zones has been kept in the mix for the comparison. However in
my code Module 2 merges the columns it put "L00" on them, which was the
best I could do... They should read either Zone 1-999 or Blank for 00-10
but this was too complicated for me to isolate so I left it alone...This
resulted in the L00000-L00009 and the L00Z000-L00Z999 you see in the
"Summary" and the CompareData" worksheets...

I'm sure the code I have can deal with what you want to do, but I don't understand what you want to do with regard to constructing a merged address with the specifications of that value being in the range of L01D001 to L10M159. Please try to be specific.

It doesn't matter what the 1-159 represents in terms of the programming. No matter what they represent, they will be the terminal three digits of "Merged Address". What matters is what you want to happen if the value in that column is outside of the range 1-159. In other words, what should happen to the Merged Address if the value in that column is zero?

Any number of things are possible, including constructing a Merged Address that does not conform to the specifications above, or even excluding that line from CompareData completely.. But if you want to include the line, you will need to decide what should be in the "Merged Address" column.

Either the specifications are incomplete, or there is a problem with the
data in this latest workbook. I've got the basics of the code set up,
but I need better specifications to proceed.

At this point I think the second workbook, as slow as it is and from
piecing various codes together, works through all we have discussed
except when adding missing addresses... But in some cases, it does not
complete to address 001 but would stop at address 005. This causes the
"Diff" worksheet to be populated with more cell differences than it
should...For example...
L01D159
L01M005
L01M006

Also, if a new loop was added, it doesn't check the "Summary" sheet to
see if it needs to be added there as well, nor does it write any data
changes from to "CompareData" sheet to the "Summary" sheet such as if
the programmed labels were different on the sheets and remove the column
"A" project number info...

Perhaps one disconnect is that I made all comments using the "Summary"
and "CompareData" sheets, which copies columns from the "PanelData
sheets and sorts the data and adds the above mentioned code for adding
the missing numbers, and I think now we are discussing using the
"PanelData" sheet for that info instead...

I was under the impression that PanelData represented the original data, and was derived from some source probably not under your control.
It will be far simpler to start at that point; construct the MergedAddresses according to a well defined algorithm; and then construct the CompareData and Summary sheets appropriately. Once this is done, it will be relatively simple to determine the cell differences.

On Sun, 10 Feb 2013 19:43:27 +0000, TimLeonard wrote:

At this point I think the second workbook, as slow as it is and from
piecing various codes together, works through all we have discussed
except when adding missing addresses... But in some cases, it does not
complete to address 001 but would stop at address 005.

The following code starts with Worksheet "PanelData" as presented in your most recent workbook, and constructs a CompareData worksheet which, in order to not conflict, I have named "CompareData2".

I am trying to see if I am on the right track. This macro runs (on my machine), in less than one second.

It adds in all of the missing merged addresses.
It presents the columns in the same order that you have on your CompareData sheet.

It does NOT include lines that do not compute to a valid Merged Address code. (That can be changed if you like, but you'll need to define how to handle them).
It does NOT fill in the Node Address, LoopSelection, DeviceAddress fields at this time, but that can be easily changed if I am on the right track.

What do you think?

=============================================
Option Explicit
Sub CreateCompareDataSheet()
'Do this on a CompareData2 Sheet
'Keep only columns C:H
'Remove lines with no valid Device Address; (or not as required)
'Add Merged Address Column
'Append the "missing" Merged Addresses
'Rearrange columns by horizontal sorting according to custom list
'Sort results by Merged Address
Dim wsCompareData2 As Worksheet
Dim wsPD As Worksheet, vPD As Variant 'Panel Data
Dim r As Range

Dim LScol As Long 'Loop Selection column
Dim DTPcol As Long 'Device Type column
Dim sDTP As String
Dim DAcol As Long 'Device Address column
Dim MAcol As Long 'Merged Address column
Dim collUsedMA As Collection 'Used Merged Address Collection
Dim collMissMA As Collection 'Missing Merged Addresses
Dim DTPScol As Long 'Device Types column

Dim aTemp() As Variant
Dim v As Variant
Dim i As Long, j As Long

Set wsPD = Worksheets("PanelData")

'Clear CompareData2 sheet if present; create if not
On Error Resume Next
Set wsCompareData2 = Worksheets("CompareData2")
If Err.Number = 9 Then
Worksheets.Add
ActiveSheet.Name = "CompareData2"
Set wsCompareData2 = Worksheets("CompareData2")
End If
On Error GoTo 0
wsCompareData2.Cells.Clear

'Read Panel Data into array
'Assuming zero(0) blanks in Col A
'Assume we will retain only cols A:H
With wsPD
vPD = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)) _
.Offset(columnoffset:=2).Resize(columnsize:=6)
End With

'Add column for merged address
'For now, it will be the "last column", but could be moved if desired
ReDim Preserve vPD(1 To UBound(vPD, 1), 1 To UBound(vPD, 2) + 2)
MAcol = UBound(vPD, 2) - 1
DTPScol = UBound(vPD, 2)
vPD(1, MAcol) = "Merged Address"
vPD(1, DTPScol) = "Device Types"

'Get column numbers for data to create MergedAddress
ReDim aTemp(1 To UBound(vPD, 2))
For i = 1 To UBound(vPD, 2)
aTemp(i) = vPD(1, i)
Next i
With WorksheetFunction
LScol = .Match("LoopSelection", aTemp, 0)
DTPcol = .Match("DeviceType", aTemp, 0)
DAcol = .Match("DeviceAddress", aTemp, 0)
End With

'Create Merged Addresses
'Add Device Types Field
Set collUsedMA = New Collection
For i = 2 To UBound(vPD, 1)
Select Case vPD(i, DTPcol)
Case Is = 1
sDTP = "D"
vPD(i, DTPScol) = "Detector"
Case Is = 2
sDTP = "M"
vPD(i, DTPScol) = "Monitor"
Case Is = 3
sDTP = ""
vPD(i, DTPScol) = "Zone"
Case Else
sDTP = ""
End Select
If Not sDTP = "" Then
vPD(i, MAcol) = "L" & Format(vPD(i, LScol), "00") & _
sDTP & _
Format(vPD(i, DAcol), "000")
On Error Resume Next
collUsedMA.Add Item:=vPD(i, MAcol), Key:=vPD(i, MAcol)
If Err.Number < 0 Then
MsgBox ("Merged Address: " & vPD(i, MAcol) & _
"on Line " & i & " is a duplicate")
Exit Sub
End If
On Error GoTo 0
End If
Next i

'Develop collection of Missing Merged Addresses
Set collMissMA = New Collection
With WorksheetFunction
LScol = .Match("LoopSelection", wsPD.Rows(1), 0)
v = GenLoops(.Max(wsPD.Columns(LScol)))
End With

On Error Resume Next
For i = LBound(v) To UBound(v)
collUsedMA.Add Item:=v(i), Key:=v(i)
If Err.Number = 0 Then
collMissMA.Add Item:=v(i), Key:=v(i)
End If
Err.Clear
Next i

'write array to CompareData2 sheet
'sort by Merged Addresses and delete lines with no MA's
'then sort horizontally by first row and custom sort
'set up custom order based on fields in row 1 of panel data
'verify labels are correct

'Column Headers for Compare and Summary Sheets
'Need to be in the desired order -- will be used as a Custom Sort Order List
'Need to match exactly the headers (but not the order)
' on the PanelData worksheet
Dim aCL(1 To 8) 'custom list array
aCL(1) = "NodeAddress"
aCL(2) = "LoopSelection"
aCL(3) = "DeviceAddress"
aCL(4) = "Merged Address"
aCL(5) = "DeviceType"
aCL(6) = "Device Types"
aCL(7) = "DeviceLabel"
aCL(8) = "ExtendedLabel"
ReDim aTemp(1 To UBound(vPD, 2))
For i = 1 To UBound(vPD, 2)
aTemp(i) = vPD(1, i)
Next i

On Error Resume Next
For i = 1 To UBound(aCL)
j = WorksheetFunction.Match(aCL(i), aTemp, 0)
If Err.Number < 0 Then
MsgBox (aCL(i) & " Not exact match in Panel Data Label row")
Exit Sub
End If
Next i

'Write data to CompareData2 sheet
Application.ScreenUpdating = False
With wsCompareData2
Set r = .Range("B1", .Cells(UBound(vPD, 1), UBound(vPD, 2) + 1))
r = vPD

'Add the Missing Merged Addresses to the correct column
ReDim aTemp(1 To collMissMA.Count, 1 To 1)
For i = 1 To collMissMA.Count
aTemp(i, 1) = collMissMA(i)
Next i
MAcol = WorksheetFunction.Match("Merged Address", .Rows(1), 0)
Set r = .Range(Cells(r.Row + r.Rows.Count, MAcol), _
Cells(r.Rows.Count + UBound(aTemp, 1), MAcol))
r = aTemp
'Sort by Merged Address and delete those with blank MA's
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range(.Cells(.UsedRange.Row + 1, MAcol), _
.Cells(.UsedRange.Row + .UsedRange.Rows.Count, MAcol)), _
SortOn:=xlsortonxlvalues, Order:=xlAscending
With .Sort
.SetRange wsCompareData2.UsedRange
.Header = xlYes
.Orientation = xlTopToBottom
.Apply
End With
Set r = .Cells(.Cells.Rows.Count, MAcol).End(xlUp).Offset(1)
Set r = Range(r, r.End(xlDown))
r.EntireRow.Delete

'Now sort horizontally to reorder the columns
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("B1", .Cells(1, .Columns.Count).End(xlToLeft)), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
CustomOrder:=Join(aCL, ",")
With .Sort
.SetRange wsCompareData2.UsedRange
.Header = xlYes
.Orientation = xlLeftToRight
.Apply
End With
..UsedRange.EntireColumn.AutoFit
End With

Application.ScreenUpdating = True
End Sub

'-------------------------------------------------------
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
===========================================

On Sun, 10 Feb 2013 19:43:27 +0000, TimLeonard wrote:

At this point I think the second workbook, as slow as it is and from
piecing various codes together, works through all we have discussed
except when adding missing addresses..

Here is a modified version that includes:
Filling in the Loop Selection, Device Address and Device Type entries for the "missing" Merged Address Entries
Formats the first row to have the gray interior and frozen header row as you do in some of your other examples.
Includes some "clean-up" with regard to references.

I could not fill in the NodeAddress column as I do not know how these are derived from the information I have.

It runs in less than 1/2 second on my machine.
It will not run as written on versions of Excel prior to 2007.
If you might be running this on a Macintosh, you will need to change the interior color format to something that does not involve RGB.

================================
Option Explicit
Sub CreateCompareDataSheet()
'Do this on a CompareData2 Sheet
'Keep only columns C:H
'Remove lines with no valid Device Address; (or not as required)
'Add Merged Address Column
'Append the "missing" Merged Addresses
'Rearrange columns by horizontal sorting according to custom list
'Sort results by Merged Address
Dim wsCompareData2 As Worksheet
Dim wsPD As Worksheet, vPD As Variant 'Panel Data
Dim r As Range, rw As Range, rMissed As Range

Dim LScol As Long 'Loop Selection column
Dim DTPcol As Long 'Device Type column
Dim sDTP As String
Dim DAcol As Long 'Device Address column
Dim MAcol As Long 'Merged Address column
Dim collUsedMA As Collection 'Used Merged Address Collection
Dim collMissMA As Collection 'Missing Merged Addresses
Dim DTPScol As Long 'Device Types column

Dim aTemp() As Variant
Dim v As Variant
Dim i As Long, j As Long

Set wsPD = Worksheets("PanelData")

'Clear CompareData2 sheet if present; create if not
On Error Resume Next
Set wsCompareData2 = Worksheets("CompareData2")
If Err.Number = 9 Then
Worksheets.Add
ActiveSheet.Name = "CompareData2"
Set wsCompareData2 = Worksheets("CompareData2")
End If
On Error GoTo 0
wsCompareData2.Cells.Clear

'Read Panel Data into array
'Assuming zero(0) blanks in Col A
'Assume we will retain only cols A:H
With wsPD
vPD = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp)) _
.Offset(columnoffset:=2).Resize(columnsize:=6)
End With

'Add column for merged address
'For now, it will be the "last column", but could be moved if desired
ReDim Preserve vPD(1 To UBound(vPD, 1), 1 To UBound(vPD, 2) + 2)
MAcol = UBound(vPD, 2) - 1
DTPScol = UBound(vPD, 2)
vPD(1, MAcol) = "Merged Address"
vPD(1, DTPScol) = "Device Types"

'Get column numbers for data to create MergedAddress
ReDim aTemp(1 To UBound(vPD, 2))
For i = 1 To UBound(vPD, 2)
aTemp(i) = vPD(1, i)
Next i
With WorksheetFunction
LScol = .Match("LoopSelection", aTemp, 0)
DTPcol = .Match("DeviceType", aTemp, 0)
DAcol = .Match("DeviceAddress", aTemp, 0)
End With

'Create Merged Addresses
'Add Device Types Field
Set collUsedMA = New Collection
For i = 2 To UBound(vPD, 1)
Select Case vPD(i, DTPcol)
Case Is = 1
sDTP = "D"
vPD(i, DTPScol) = "Detector"
Case Is = 2
sDTP = "M"
vPD(i, DTPScol) = "Monitor"
Case Is = 3
sDTP = ""
vPD(i, DTPScol) = "Zone"
Case Else
sDTP = ""
End Select
If Not sDTP = "" Then
vPD(i, MAcol) = "L" & Format(vPD(i, LScol), "00") & _
sDTP & _
Format(vPD(i, DAcol), "000")
On Error Resume Next
collUsedMA.Add Item:=vPD(i, MAcol), Key:=vPD(i, MAcol)
If Err.Number < 0 Then
MsgBox ("Merged Address: " & vPD(i, MAcol) & _
"on Line " & i & " is a duplicate")
Exit Sub
End If
On Error GoTo 0
End If
Next i

'Develop collection of Missing Merged Addresses
Set collMissMA = New Collection
With WorksheetFunction
LScol = .Match("LoopSelection", wsPD.Rows(1), 0)
v = GenLoops(.Max(wsPD.Columns(LScol)))
End With

On Error Resume Next
For i = LBound(v) To UBound(v)
collUsedMA.Add Item:=v(i), Key:=v(i)
If Err.Number = 0 Then
collMissMA.Add Item:=v(i), Key:=v(i)
End If
Err.Clear
Next i

'write array to CompareData2 sheet
'sort by Merged Addresses and delete lines with no MA's
'then sort horizontally by first row and custom sort
'set up custom order based on fields in row 1 of panel data
'verify labels are correct

'Column Headers for Compare and Summary Sheets
'Need to be in the desired order -- will be used as a Custom Sort Order List
'Need to match exactly the headers (but not the order)
' on the PanelData worksheet
Dim aCL(1 To 8) 'custom list array
aCL(1) = "NodeAddress"
aCL(2) = "LoopSelection"
aCL(3) = "DeviceAddress"
aCL(4) = "Merged Address"
aCL(5) = "DeviceType"
aCL(6) = "Device Types"
aCL(7) = "DeviceLabel"
aCL(8) = "ExtendedLabel"
ReDim aTemp(1 To UBound(vPD, 2))
For i = 1 To UBound(vPD, 2)
aTemp(i) = vPD(1, i)
Next i

On Error Resume Next
For i = 1 To UBound(aCL)
j = WorksheetFunction.Match(aCL(i), aTemp, 0)
If Err.Number < 0 Then
MsgBox (aCL(i) & " Not exact match in Panel Data Label row")
Exit Sub
End If
Next i

'Write data to CompareData2 sheet
Application.ScreenUpdating = False
With wsCompareData2
Set r = .Range("B1").Resize(rowsize:=UBound(vPD, 1), columnsize:=UBound(vPD, 2))
r = vPD

'Add the Missing Merged Addresses to the correct column
'Also deconstruct to fill in the LS, DA and DT columns
Set rw = r.Rows(1)
With WorksheetFunction
MAcol = .Match("Merged Address", rw, 0)
LScol = .Match("LoopSelection", rw, 0)
DAcol = .Match("DeviceAddress", rw, 0)
DTPcol = .Match("DeviceType", rw, 0)
End With

ReDim aTemp(1 To collMissMA.Count, 1 To r.Columns.Count)
For i = 1 To collMissMA.Count
aTemp(i, MAcol) = collMissMA(i)
aTemp(i, LScol) = Val(Mid(collMissMA(i), 2, 2))
aTemp(i, DAcol) = Val(Right(collMissMA(i), 3))
aTemp(i, DTPcol) = IIf(Mid(collMissMA(i), 4, 1) = "D", 1, 2)
Next i

Set rMissed = .Cells(r.Row + r.Rows.Count, r.Column).Resize(rowsize:=UBound(aTemp, 1), columnsize:=UBound(aTemp, 2))
rMissed = aTemp
Set r = Union(r, rMissed)
'Sort by Merged Address and delete those with blank MA's
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=r.Resize(rowsize:=r.Rows.Count - 1).Offset(rowoffset:=1).Columns(MAcol), _
SortOn:=xlSortOnValues, Order:=xlAscending
With .Sort
.SetRange r
.Header = xlYes
.Orientation = xlTopToBottom
.Apply
End With
Set r = Range(r(1, MAcol).End(xlDown).Offset(rowoffset:=1), r(.Cells.Rows.Count, MAcol))
r.EntireRow.Delete

'Now sort horizontally to reorder the columns
Set r = .UsedRange
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=r.Rows(1), _
SortOn:=xlSortOnValues, Order:=xlAscending, _
CustomOrder:=Join(aCL, ",")
With .Sort
.SetRange r
.Header = xlYes
.Orientation = xlLeftToRight
.Apply
End With
r.EntireColumn.AutoFit
'NOTE: Cannot use RGB on Macintosh. If that is a problem, use something
'like colorindex 15
r.Rows(1).Interior.Color = RGB(191, 191, 191) 'Same gray as on your Summary Sheet

'I don't like to activate or select, but I don't know how else to
' freeze panes
.Activate
With ActiveWindow
.SplitRow = 1
.FreezePanes = True
End With


End With

Application.ScreenUpdating = True
End Sub

'-------------------------------------------------------
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
==================================

Incredible!!! You are on the right track…
I will also reply here instead of the previous posts to keep it current...


My initial thought was always around the device/module addresses, but after giving this more thought, If it’s not too much to include, I think it should also populate the zones, these have the device type value of (3) and the loopselection of (0) they should be in the format of Z000-Z999…The device type values of (5) can be excluded

Question…
Is there a way to have it look at the “summary” sheet to see if the Node Address, LoopSelection, DeviceAddress and Merged Address fields need to be updated…I know I told you this is manually populated but my thought is that if an additional loop was added in the field panel not exceeding ten loops, rather than having to manually insert the additional rows, have the code do it?

Another concern I have is what if the technician re-labels a device/module or zone, how can we update the summary sheet with the revised labels. Originally I planned to have code look at both sheets labels and if they were both greater than blank then overwrite the data on the summary sheet in the same row/cells.

There is one piece that was too complicated for me, that may prove easy for you…On the “PanelData”column “K” there is values that correlate to another worksheet that is imported named “DeviceType”. On that Sheet “DeviceType” in Column “A” it has those same values and in Column ”E” has the Device Type Labels that if possible, I would like to included on Both the “Summary” and “CompareData2” Worksheets Column “J” (once they both match column wise)


Since the panel will max out with the (10) loop for the NodeAddress, it will be "1" for the first (10) loops then it will be "2" for the next (10) loops and so up to 104 NodeAddresses. However most "PanelData" sheets will only contain 1 NodeAddress

This will be use on Excel 2007 and not on a Macintosh

BTW I was going to tell you that there was a small issue, if on another tab when the macro is ran the results are not what is expected… But you seemed to have fixed it on the revised code…

Questions in line below


Incredible!!! You are on the right track…
I will also reply here instead of the previous posts to keep it
current...


My initial thought was always around the device/module addresses, but
after giving this more thought, If it’s not too much to include, I think
it should also populate the zones, these have the device type value of
(3) and the loopselection of (0) they should be in the format of
Z000-Z999…The device type values of (5) can be excluded

Just to be clear
If Device Type = 3
Merged Address = Znnn where nnn is the Device Address with three digits
and
you also want to have blank entries for the missing values in the range of 000-999

(So it would not be L00Z000 - L00Z999 but just Z000-Z999)



Question…
Is there a way to have it look at the “summary” sheet to see if the Node
Address, LoopSelection, DeviceAddress and Merged Address fields need to
be updated…I know I told you this is manually populated but my thought
is that if an additional loop was added in the field panel not exceeding
ten loops, rather than having to manually insert the additional rows,
have the code do it?

As designed, the code I have provided generates a CompareData sheet (named CompareData2) using the sheet PanelData. I have not got to the Summary sheet yet.

Is the entire summary sheet manually generated?
If so, how do you mitigate for data entry errors?
If not, how is it generated.

Another concern I have is what if the technician re-labels a
device/module or zone, how can we update the summary sheet with the
revised labels.

What process does the technician go thru in order to do this.

Originally I planned to have code look at both sheets
labels

what do you mean by "both sheets labels"?

and if they were both greater than blank then overwrite the data
on the summary sheet in the same row/cells.

This specification needs clarification.

---------------------------------------


There is one piece that was too complicated for me, that may prove easy
for you…On the “PanelData”column “K” there is values that correlate to
another worksheet that is imported named “DeviceType”. On that Sheet
“DeviceType” in Column “A” it has those same values and in Column ”E”
has the Device Type Labels that if possible, I would like to included on
Both the “Summary” and “CompareData2” Worksheets Column “J” (once they
both match column wise)

I don't know what you are trying to specify here. Column K on PanelData is marked, on my copy, TypeID with a notation it is to be removed to reduce file size. There is no data there.
But any column from PanelData can be included on CompareData. You just enlarge the vPD array to accomodate it. If you are adding data from other worksheets, you are going to need to be sure it matches up with the devices/zones that are already there.




I could not fill in the NodeAddress column as I do not know how these
are derived from the information I have.


Since the panel will max out with the (10) loop the node address will be
1 throughout the column..

I will modify the code to include the NodeAddress


This will be use on Excel 2007 and not on a Macintosh

BTW I was going to tell you that there was a small issue, if on another
tab when the macro is ran the results are not what is expected… But you
seemed to have fixed it on the revised code…


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+