Reference column

[climate]

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

 

[Joel]

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]

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]

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]

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]

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:=Station, _
LookIn:=xlvlaues, lookat:=xlWhole)

 

[climate]

Hi Joel
When replace lines which you said, run-time error"9" with suscript out of
range is appear.

regards

 

[Joel]

I can't spell

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

to
Set c = Sheets("Sheet2").Range("A2:A109").Find(what:=Station, _
LookIn:=xlvalues, lookat:=xlWhole)

 

[climate]

Hi Joel
Please write complete macro with it's correction that you have suggested,
again.
Thank's
regards

 

[Joel]

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").Columns("A").Find(what:=Station, _
LookIn:=xlValues, 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]

Hi Joel
Thank's, your macro works correctly,but when create missing station on
sheet2, one time create name of any missing station (only in one cell),
therefore, number of cells on sheet1 and sheet2 not equal after run macro,as
i mentioned, i want to repeat name of any missing station in 3 cells,would
you please correct it.

regards

 

[Joel]

What 3 cells need to be repeated. It is not clear from any of you postings.

 

[climate]

Hi Joel
Please refer to first my post. my Reference column similar to following,
when run macro, it is necessary to create 3 times name of any missing
station.
A
London
London
London
Tokyo
Tokyo
Tokyo
.......
.......
.......
Paris
Paris
Paris
Regards

 

[Joel]

Try this

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").Columns("A").Find(what:=Station, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
'add missing station to sheet 2
Sheets("Sheet2").Range("A" & Newrow) = Station
Sheets("Sheet2").Range("A" & (Newrow + 1)) = Station
Sheets("Sheet2").Range("A" & (Newrow + 2)) = Station
Newrow = Newrow + 3
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]

Hi Joel
Your code works correctly.
Many thank's for you.

best regards