Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference column
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference column
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference column
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference column
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference column
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference column
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference column
Hi Joel
When replace lines which you said, run-time error"9" with suscript out of range is appear. regards "Joel" wrote: 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference column
I can't spell
from Set c = Sheets("Sheet2").Range("A2:A109").Find(what:=Stati on, _ LookIn:=xlvlaues, lookat:=xlWhole) to Set c = Sheets("Sheet2").Range("A2:A109").Find(what:=Stati on, _ LookIn:=xlvalues, lookat:=xlWhole) "climate" wrote: Hi Joel When replace lines which you said, run-time error"9" with suscript out of range is appear. regards "Joel" wrote: 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference column
Hi Joel
Please write complete macro with it's correction that you have suggested, again. Thank's regards "Joel" wrote: I can't spell from Set c = Sheets("Sheet2").Range("A2:A109").Find(what:=Stati on, _ LookIn:=xlvlaues, lookat:=xlWhole) to Set c = Sheets("Sheet2").Range("A2:A109").Find(what:=Stati on, _ LookIn:=xlvalues, lookat:=xlWhole) "climate" wrote: Hi Joel When replace lines which you said, run-time error"9" with suscript out of range is appear. regards "Joel" wrote: 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference column
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" wrote: Hi Joel Please write complete macro with it's correction that you have suggested, again. Thank's regards "Joel" wrote: I can't spell from Set c = Sheets("Sheet2").Range("A2:A109").Find(what:=Stati on, _ LookIn:=xlvlaues, lookat:=xlWhole) to Set c = Sheets("Sheet2").Range("A2:A109").Find(what:=Stati on, _ LookIn:=xlvalues, lookat:=xlWhole) "climate" wrote: Hi Joel When replace lines which you said, run-time error"9" with suscript out of range is appear. regards "Joel" wrote: 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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference column
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" wrote: 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" wrote: Hi Joel Please write complete macro with it's correction that you have suggested, again. Thank's regards "Joel" wrote: I can't spell from Set c = Sheets("Sheet2").Range("A2:A109").Find(what:=Stati on, _ LookIn:=xlvlaues, lookat:=xlWhole) to Set c = Sheets("Sheet2").Range("A2:A109").Find(what:=Stati on, _ LookIn:=xlvalues, lookat:=xlWhole) "climate" wrote: Hi Joel When replace lines which you said, run-time error"9" with suscript out of range is appear. regards "Joel" wrote: 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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference column
What 3 cells need to be repeated. It is not clear from any of you postings.
"climate" wrote: 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" wrote: 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" wrote: Hi Joel Please write complete macro with it's correction that you have suggested, again. Thank's regards "Joel" wrote: I can't spell from Set c = Sheets("Sheet2").Range("A2:A109").Find(what:=Stati on, _ LookIn:=xlvlaues, lookat:=xlWhole) to Set c = Sheets("Sheet2").Range("A2:A109").Find(what:=Stati on, _ LookIn:=xlvalues, lookat:=xlWhole) "climate" wrote: Hi Joel When replace lines which you said, run-time error"9" with suscript out of range is appear. regards "Joel" wrote: 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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference column
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" wrote: What 3 cells need to be repeated. It is not clear from any of you postings. "climate" wrote: 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" wrote: 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" wrote: Hi Joel Please write complete macro with it's correction that you have suggested, again. Thank's regards "Joel" wrote: I can't spell from Set c = Sheets("Sheet2").Range("A2:A109").Find(what:=Stati on, _ LookIn:=xlvlaues, lookat:=xlWhole) to Set c = Sheets("Sheet2").Range("A2:A109").Find(what:=Stati on, _ LookIn:=xlvalues, lookat:=xlWhole) "climate" wrote: Hi Joel When replace lines which you said, run-time error"9" with suscript out of range is appear. regards "Joel" wrote: 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 |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference column
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" wrote: 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" wrote: What 3 cells need to be repeated. It is not clear from any of you postings. "climate" wrote: 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" wrote: 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" wrote: Hi Joel Please write complete macro with it's correction that you have suggested, again. Thank's regards "Joel" wrote: I can't spell from Set c = Sheets("Sheet2").Range("A2:A109").Find(what:=Stati on, _ LookIn:=xlvlaues, lookat:=xlWhole) to Set c = Sheets("Sheet2").Range("A2:A109").Find(what:=Stati on, _ LookIn:=xlvalues, lookat:=xlWhole) "climate" wrote: Hi Joel When replace lines which you said, run-time error"9" with suscript out of range is appear. regards "Joel" wrote: 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 |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference column
Hi Joel
Your code works correctly. Many thank's for you. best regards "Joel" wrote: 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" wrote: 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" wrote: What 3 cells need to be repeated. It is not clear from any of you postings. "climate" wrote: 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" wrote: 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" wrote: Hi Joel Please write complete macro with it's correction that you have suggested, again. Thank's regards "Joel" wrote: I can't spell from Set c = Sheets("Sheet2").Range("A2:A109").Find(what:=Stati on, _ LookIn:=xlvlaues, lookat:=xlWhole) to Set c = Sheets("Sheet2").Range("A2:A109").Find(what:=Stati on, _ LookIn:=xlvalues, lookat:=xlWhole) "climate" wrote: Hi Joel When replace lines which you said, run-time error"9" with suscript out of range is appear. regards "Joel" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
column 2 to transpose using column 1 as reference | Excel Discussion (Misc queries) | |||
Column reference and column count | Excel Discussion (Misc queries) | |||
Row reference increment but preserve column reference | Excel Worksheet Functions | |||
Macro to Reference Column Next to Current Reference | Excel Discussion (Misc queries) | |||
I need to find the Average from Column A - but Reference Column B | Excel Worksheet Functions |