Hi
I have a matrix (A2:BJ200) on sheet1, and column A consist of name of
stations which name of any station repeated in 3 cells.( i say Reference
column).
On sheet2, i have another matrix (A2:BJ109), with missing stations.
I want this, when i copy Reference column to sheet2 (column A), blank rows
with name of station only create. in other words, dimension of second matrix
equal to first matrix with name of all of the stations.
Thank's for any help.

Respectfully yours

You need to create a listt of items that are missing from sheet 2. then add
these missing items to the end of the data in sheet 2 and then sort sheet 2
by column A including the new items added.

The best way is to work with a new sheet 3 so you done ruin the data on the
other sheets in case you make any mistakes. I would copy column A from Sheet
1 to column A on sheet 3. Then copy Copy Colu A of sheet 2 to Column C of
sheet 3.

Now on sheet 3 Add into cell B1 this formula

=IF(ISNA(VLOOKUP(A1,$C:$C,1,FALSE)),TRUE,FALSE)

then sort columns A & b using column B as KEY. The itmes that are true are
the ones missing in Sheet2. From sheet 3 the True items in column A to
sheet 2.

Now sort Sheet 2 by column a.

=vlookup(

"climate" wrote:

Hi
I have a matrix (A2:BJ200) on sheet1, and column A consist of name of
stations which name of any station repeated in 3 cells.( i say Reference
column).
On sheet2, i have another matrix (A2:BJ109), with missing stations.
I want this, when i copy Reference column to sheet2 (column A), blank rows
with name of station only create. in other words, dimension of second matrix
equal to first matrix with name of all of the stations.
Thank's for any help.

Respectfully yours

Hi Joel
Thank's, but i want to save my time, i need to a formula or macro, when i
copy Reference column on the column A of the second matrix,arrange it's
dimension automatically.
regards

"Joel" wrote:

You need to create a listt of items that are missing from sheet 2. then add
these missing items to the end of the data in sheet 2 and then sort sheet 2
by column A including the new items added.

The best way is to work with a new sheet 3 so you done ruin the data on the
other sheets in case you make any mistakes. I would copy column A from Sheet
1 to column A on sheet 3. Then copy Copy Colu A of sheet 2 to Column C of
sheet 3.

Now on sheet 3 Add into cell B1 this formula

=IF(ISNA(VLOOKUP(A1,$C:$C,1,FALSE)),TRUE,FALSE)

then sort columns A & b using column B as KEY. The itmes that are true are
the ones missing in Sheet2. From sheet 3 the True items in column A to
sheet 2.

Now sort Sheet 2 by column a.

=vlookup(

"climate" wrote:

Hi
I have a matrix (A2:BJ200) on sheet1, and column A consist of name of
stations which name of any station repeated in 3 cells.( i say Reference
column).
On sheet2, i have another matrix (A2:BJ109), with missing stations.
I want this, when i copy Reference column to sheet2 (column A), blank rows
with name of station only create. in other words, dimension of second matrix
equal to first matrix with name of all of the stations.
Thank's for any help.

Respectfully yours

I didn't know you wanted a macro. usually request for macros are on the
Excel Programming Group. Try this code

Sub GetMissingNames()

With Sheets("Sheet2")
'get Last row of sheet 2
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Newrow = LastRow + 1
End With

With Sheets("Sheet1")
RowCount = 2
Do While .Range("A" & RowCount) < ""
Station = .Range("A" & RowCount)
Set c = Sheets("Sheet2").Find(what:=Station, _
LookIn:=xlvlaues, lookat:=xlWhole)
If c Is Nothing Then
'add missing station to sheet 2
Sheets("Sheet2").Range("A" & Newrow) = Station
Newrow = Newrow + 1
End If
RowCount = RowCount + 1
Loop
End With
With Sheets("Sheet2")
Set Sortrows = .Rows("2:" & (Newrow - 1))
Sortrows.Sort _
key1:=.Range("A2"), _
order1:=xlAscending, _
Header:=xlNo

End With

End Sub


"climate" wrote:

Hi Joel
Thank's, but i want to save my time, i need to a formula or macro, when i
copy Reference column on the column A of the second matrix,arrange it's
dimension automatically.
regards

"Joel" wrote:

You need to create a listt of items that are missing from sheet 2. then add
these missing items to the end of the data in sheet 2 and then sort sheet 2
by column A including the new items added.

The best way is to work with a new sheet 3 so you done ruin the data on the
other sheets in case you make any mistakes. I would copy column A from Sheet
1 to column A on sheet 3. Then copy Copy Colu A of sheet 2 to Column C of
sheet 3.

Now on sheet 3 Add into cell B1 this formula

=IF(ISNA(VLOOKUP(A1,$C:$C,1,FALSE)),TRUE,FALSE)

then sort columns A & b using column B as KEY. The itmes that are true are
the ones missing in Sheet2. From sheet 3 the True items in column A to
sheet 2.

Now sort Sheet 2 by column a.

=vlookup(

"climate" wrote:

Hi
I have a matrix (A2:BJ200) on sheet1, and column A consist of name of
stations which name of any station repeated in 3 cells.( i say Reference
column).
On sheet2, i have another matrix (A2:BJ109), with missing stations.
I want this, when i copy Reference column to sheet2 (column A), blank rows
with name of station only create. in other words, dimension of second matrix
equal to first matrix with name of all of the stations.
Thank's for any help.

Respectfully yours

Hi Joel
Thank's, but when i copy your code to sheet1(right click and view code tab),
error message "run time error 438" is appear, and stop on this line :
Set c = Sheets("Sheet2").Find(what:=Station, _
LookIn:=xlvlaues, lookat:=xlWhole)
My data is located on sheet1 and sheet2.

regards

"Joel" wrote:

I didn't know you wanted a macro. usually request for macros are on the
Excel Programming Group. Try this code

Sub GetMissingNames()

With Sheets("Sheet2")
'get Last row of sheet 2
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Newrow = LastRow + 1
End With

With Sheets("Sheet1")
RowCount = 2
Do While .Range("A" & RowCount) < ""
Station = .Range("A" & RowCount)
Set c = Sheets("Sheet2").Find(what:=Station, _
LookIn:=xlvlaues, lookat:=xlWhole)
If c Is Nothing Then
'add missing station to sheet 2
Sheets("Sheet2").Range("A" & Newrow) = Station
Newrow = Newrow + 1
End If
RowCount = RowCount + 1
Loop
End With
With Sheets("Sheet2")
Set Sortrows = .Rows("2:" & (Newrow - 1))
Sortrows.Sort _
key1:=.Range("A2"), _
order1:=xlAscending, _
Header:=xlNo

End With

End Sub


"climate" wrote:

Hi Joel
Thank's, but i want to save my time, i need to a formula or macro, when i
copy Reference column on the column A of the second matrix,arrange it's
dimension automatically.
regards

"Joel" wrote:

You need to create a listt of items that are missing from sheet 2. then add
these missing items to the end of the data in sheet 2 and then sort sheet 2
by column A including the new items added.

The best way is to work with a new sheet 3 so you done ruin the data on the
other sheets in case you make any mistakes. I would copy column A from Sheet
1 to column A on sheet 3. Then copy Copy Colu A of sheet 2 to Column C of
sheet 3.

Now on sheet 3 Add into cell B1 this formula

=IF(ISNA(VLOOKUP(A1,$C:$C,1,FALSE)),TRUE,FALSE)

then sort columns A & b using column B as KEY. The itmes that are true are
the ones missing in Sheet2. From sheet 3 the True items in column A to
sheet 2.

Now sort Sheet 2 by column a.

=vlookup(

"climate" wrote:

Hi
I have a matrix (A2:BJ200) on sheet1, and column A consist of name of
stations which name of any station repeated in 3 cells.( i say Reference
column).
On sheet2, i have another matrix (A2:BJ109), with missing stations.
I want this, when i copy Reference column to sheet2 (column A), blank rows
with name of station only create. in other words, dimension of second matrix
equal to first matrix with name of all of the stations.
Thank's for any help.

Respectfully yours

I forgot to put a Range on the sheet2 lookup area.


from

Set c = Sheets("Sheet2").Find(what:=Station, _
LookIn:=xlvlaues, lookat:=xlWhole)

to

Set c = Sheets("Sheet2").Range("A2:A109").Find(what:=Stati on, _
LookIn:=xlvlaues, lookat:=xlWhole)


"climate" wrote:

Hi Joel
Thank's, but when i copy your code to sheet1(right click and view code tab),
error message "run time error 438" is appear, and stop on this line :
Set c = Sheets("Sheet2").Find(what:=Station, _
LookIn:=xlvlaues, lookat:=xlWhole)
My data is located on sheet1 and sheet2.

regards

"Joel" wrote:

I didn't know you wanted a macro. usually request for macros are on the
Excel Programming Group. Try this code

Sub GetMissingNames()

With Sheets("Sheet2")
'get Last row of sheet 2
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
Newrow = LastRow + 1
End With

With Sheets("Sheet1")
RowCount = 2
Do While .Range("A" & RowCount) < ""
Station = .Range("A" & RowCount)
Set c = Sheets("Sheet2").Find(what:=Station, _
LookIn:=xlvlaues, lookat:=xlWhole)
If c Is Nothing Then
'add missing station to sheet 2
Sheets("Sheet2").Range("A" & Newrow) = Station
Newrow = Newrow + 1
End If
RowCount = RowCount + 1
Loop
End With
With Sheets("Sheet2")
Set Sortrows = .Rows("2:" & (Newrow - 1))
Sortrows.Sort _
key1:=.Range("A2"), _
order1:=xlAscending, _
Header:=xlNo

End With

End Sub


"climate" wrote:

Hi Joel
Thank's, but i want to save my time, i need to a formula or macro, when i
copy Reference column on the column A of the second matrix,arrange it's
dimension automatically.
regards

"Joel" wrote:

You need to create a listt of items that are missing from sheet 2. then add
these missing items to the end of the data in sheet 2 and then sort sheet 2
by column A including the new items added.

The best way is to work with a new sheet 3 so you done ruin the data on the
other sheets in case you make any mistakes. I would copy column A from Sheet
1 to column A on sheet 3. Then copy Copy Colu A of sheet 2 to Column C of
sheet 3.

Now on sheet 3 Add into cell B1 this formula

=IF(ISNA(VLOOKUP(A1,$C:$C,1,FALSE)),TRUE,FALSE)

then sort columns A & b using column B as KEY. The itmes that are true are
the ones missing in Sheet2. From sheet 3 the True items in column A to
sheet 2.

Now sort Sheet 2 by column a.

=vlookup(

"climate" wrote:

Hi
I have a matrix (A2:BJ200) on sheet1, and column A consist of name of
stations which name of any station repeated in 3 cells.( i say Reference
column).
On sheet2, i have another matrix (A2:BJ109), with missing stations.
I want this, when i copy Reference column to sheet2 (column A), blank rows
with name of station only create. in other words, dimension of second matrix
equal to first matrix with name of all of the stations.
Thank's for any help.

Respectfully yours