Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
move blank data
HI all....
i want to move data from sheet1 to another sheet, maybe it will need macro...hope you can help me. Example...in sheet1 i have: Line Col A Col B Col C Col D AA 10 5 4 1 BB 5 - - - FF 13 3 2 2 KK 10 - - - now in sheet2 i want to create resume from sheet1 automatically.... but i want if col B is empty or no data...it can't move to sheet2... so and in sheet2 Line Col A Col B Col C Col D AA 10 5 4 1 FF 13 3 2 2 thanks in advance reza |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
move blank data
A formula solution would do. Is it ColC or ColB which is empty
If it is col C; try the below formula in Sheet2 cell A1 and drag/copy across upto D1. Once done you can select A1:D1 copy/drag the formula down as required =IF(COUNTA(Sheet1!$C$1:$C$1000)<ROW(A1),"",INDEX(S heet1!A$1:A$1000,SMALL(IF(Sheet1!$C$1:$C$1000<"", ROW($A$1:$A$1000)),ROW(A1)))) Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If it is Col B which is blank use the below instead.. =IF(COUNTA(Sheet1!$B$1:$B$1000)<ROW(A1),"",INDEX(S heet1!A$1:A$1000,SMALL(IF(Sheet1!$B$1:$B$1000<"", ROW($A$1:$A$1000)),ROW(A1)))) If this post helps click Yes --------------- Jacob Skaria "reza" wrote: HI all.... i want to move data from sheet1 to another sheet, maybe it will need macro...hope you can help me. Example...in sheet1 i have: Line Col A Col B Col C Col D AA 10 5 4 1 BB 5 - - - FF 13 3 2 2 KK 10 - - - now in sheet2 i want to create resume from sheet1 automatically.... but i want if col B is empty or no data...it can't move to sheet2... so and in sheet2 Line Col A Col B Col C Col D AA 10 5 4 1 FF 13 3 2 2 thanks in advance reza |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
move blank data
Hi Reza,
Jacob has a good solution, if you'd prefer here is a macro I used to do this task. It works well, if you need help give me a shout. Note, all you need to change are the TotalRows and TotalCols variables which I have set to 5 as per your example. Just copy this into a module and run. Sub MySort() Dim Orig As Worksheet, Destination As Worksheet Dim RowOrig As Long, RowDest As Long Dim TotalRows As String, TotalCols As Long Dim Cell As Range Set Orig = ActiveWorkbook.Worksheets("Sheet1") Set Destination = ActiveWorkbook.Worksheets("Sheet2") RowOrig = 1 RowDest = 1 TotalCols = 5 TotalRows = 5 Application.ScreenUpdating = False Destination.Activate Destination.Cells.Select Selection.ClearContents For Each Cell In Orig.Range("A1:A" & TotalRows) If Cell.Value < "" Then For Cols = 2 To TotalCols If Orig.Cells(RowOrig, Cols) = "-" Then Exit For ElseIf Cols = TotalCols Then Orig.Rows(RowOrig).Copy Destination.Rows(RowDest).Select ActiveSheet.Paste RowDest = RowDest + 1 End If Next Cols End If RowOrig = RowOrig + 1 Next Cell Destination.Range("A1").Select Application.ScreenUpdating = True End Sub "reza" wrote: HI all.... i want to move data from sheet1 to another sheet, maybe it will need macro...hope you can help me. Example...in sheet1 i have: Line Col A Col B Col C Col D AA 10 5 4 1 BB 5 - - - FF 13 3 2 2 KK 10 - - - now in sheet2 i want to create resume from sheet1 automatically.... but i want if col B is empty or no data...it can't move to sheet2... so and in sheet2 Line Col A Col B Col C Col D AA 10 5 4 1 FF 13 3 2 2 thanks in advance reza |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
move blank data
Rick
--the requirement is to have this to sheet2 automatically..I am not sure whether Reza meant "as and when you change the values in the active sheet" or just once.. --You can get the total rows; the below will do... Reza If you are looking for a one time copy of data from active sheet to sheet2 you can use the below straight away...If you are looking for the data in sheet2 to update automatically I would suggest to go with the formula solution itself; however you can try out the below macro to be called from Worksheet change event.... Sub MyMacro() Dim lngRow As Long, ws As Worksheet, lngNRow As Long Set ws = Sheets("Sheet2"): ws.UsedRange.Clear For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row If Range("C" & lngRow) < "" Then _ lngNRow = lngNRow + 1: Rows(lngRow).Copy ws.Rows(lngNRow) Next End Sub If this post helps click Yes --------------- Jacob Skaria "BSc Chem Eng Rick" wrote: Hi Reza, Jacob has a good solution, if you'd prefer here is a macro I used to do this task. It works well, if you need help give me a shout. Note, all you need to change are the TotalRows and TotalCols variables which I have set to 5 as per your example. Just copy this into a module and run. Sub MySort() Dim Orig As Worksheet, Destination As Worksheet Dim RowOrig As Long, RowDest As Long Dim TotalRows As String, TotalCols As Long Dim Cell As Range Set Orig = ActiveWorkbook.Worksheets("Sheet1") Set Destination = ActiveWorkbook.Worksheets("Sheet2") RowOrig = 1 RowDest = 1 TotalCols = 5 TotalRows = 5 Application.ScreenUpdating = False Destination.Activate Destination.Cells.Select Selection.ClearContents For Each Cell In Orig.Range("A1:A" & TotalRows) If Cell.Value < "" Then For Cols = 2 To TotalCols If Orig.Cells(RowOrig, Cols) = "-" Then Exit For ElseIf Cols = TotalCols Then Orig.Rows(RowOrig).Copy Destination.Rows(RowDest).Select ActiveSheet.Paste RowDest = RowDest + 1 End If Next Cols End If RowOrig = RowOrig + 1 Next Cell Destination.Range("A1").Select Application.ScreenUpdating = True End Sub "reza" wrote: HI all.... i want to move data from sheet1 to another sheet, maybe it will need macro...hope you can help me. Example...in sheet1 i have: Line Col A Col B Col C Col D AA 10 5 4 1 BB 5 - - - FF 13 3 2 2 KK 10 - - - now in sheet2 i want to create resume from sheet1 automatically.... but i want if col B is empty or no data...it can't move to sheet2... so and in sheet2 Line Col A Col B Col C Col D AA 10 5 4 1 FF 13 3 2 2 thanks in advance reza |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
move blank data
wow...another great answer...
thanks "Jacob Skaria" wrote: A formula solution would do. Is it ColC or ColB which is empty If it is col C; try the below formula in Sheet2 cell A1 and drag/copy across upto D1. Once done you can select A1:D1 copy/drag the formula down as required =IF(COUNTA(Sheet1!$C$1:$C$1000)<ROW(A1),"",INDEX(S heet1!A$1:A$1000,SMALL(IF(Sheet1!$C$1:$C$1000<"", ROW($A$1:$A$1000)),ROW(A1)))) Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" If it is Col B which is blank use the below instead.. =IF(COUNTA(Sheet1!$B$1:$B$1000)<ROW(A1),"",INDEX(S heet1!A$1:A$1000,SMALL(IF(Sheet1!$B$1:$B$1000<"", ROW($A$1:$A$1000)),ROW(A1)))) If this post helps click Yes --------------- Jacob Skaria "reza" wrote: HI all.... i want to move data from sheet1 to another sheet, maybe it will need macro...hope you can help me. Example...in sheet1 i have: Line Col A Col B Col C Col D AA 10 5 4 1 BB 5 - - - FF 13 3 2 2 KK 10 - - - now in sheet2 i want to create resume from sheet1 automatically.... but i want if col B is empty or no data...it can't move to sheet2... so and in sheet2 Line Col A Col B Col C Col D AA 10 5 4 1 FF 13 3 2 2 thanks in advance reza |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
move blank data
to all:
thanks for give me solution... but like jacob said...yes i want it update automatically...so i do not run every time again.... to Jacob: What should i do with your macro code...should i use with your formula above...can you tell me more detail... thanks "Jacob Skaria" wrote: Rick --the requirement is to have this to sheet2 automatically..I am not sure whether Reza meant "as and when you change the values in the active sheet" or just once.. --You can get the total rows; the below will do... Reza If you are looking for a one time copy of data from active sheet to sheet2 you can use the below straight away...If you are looking for the data in sheet2 to update automatically I would suggest to go with the formula solution itself; however you can try out the below macro to be called from Worksheet change event.... Sub MyMacro() Dim lngRow As Long, ws As Worksheet, lngNRow As Long Set ws = Sheets("Sheet2"): ws.UsedRange.Clear For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row If Range("C" & lngRow) < "" Then _ lngNRow = lngNRow + 1: Rows(lngRow).Copy ws.Rows(lngNRow) Next End Sub If this post helps click Yes --------------- Jacob Skaria "BSc Chem Eng Rick" wrote: Hi Reza, Jacob has a good solution, if you'd prefer here is a macro I used to do this task. It works well, if you need help give me a shout. Note, all you need to change are the TotalRows and TotalCols variables which I have set to 5 as per your example. Just copy this into a module and run. Sub MySort() Dim Orig As Worksheet, Destination As Worksheet Dim RowOrig As Long, RowDest As Long Dim TotalRows As String, TotalCols As Long Dim Cell As Range Set Orig = ActiveWorkbook.Worksheets("Sheet1") Set Destination = ActiveWorkbook.Worksheets("Sheet2") RowOrig = 1 RowDest = 1 TotalCols = 5 TotalRows = 5 Application.ScreenUpdating = False Destination.Activate Destination.Cells.Select Selection.ClearContents For Each Cell In Orig.Range("A1:A" & TotalRows) If Cell.Value < "" Then For Cols = 2 To TotalCols If Orig.Cells(RowOrig, Cols) = "-" Then Exit For ElseIf Cols = TotalCols Then Orig.Rows(RowOrig).Copy Destination.Rows(RowDest).Select ActiveSheet.Paste RowDest = RowDest + 1 End If Next Cols End If RowOrig = RowOrig + 1 Next Cell Destination.Range("A1").Select Application.ScreenUpdating = True End Sub "reza" wrote: HI all.... i want to move data from sheet1 to another sheet, maybe it will need macro...hope you can help me. Example...in sheet1 i have: Line Col A Col B Col C Col D AA 10 5 4 1 BB 5 - - - FF 13 3 2 2 KK 10 - - - now in sheet2 i want to create resume from sheet1 automatically.... but i want if col B is empty or no data...it can't move to sheet2... so and in sheet2 Line Col A Col B Col C Col D AA 10 5 4 1 FF 13 3 2 2 thanks in advance reza |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
move blank data
Since you need to to automatically populate; I would suggest to go with the
formula and ignore the macro solution... If this post helps click Yes --------------- Jacob Skaria "reza" wrote: to all: thanks for give me solution... but like jacob said...yes i want it update automatically...so i do not run every time again.... to Jacob: What should i do with your macro code...should i use with your formula above...can you tell me more detail... thanks "Jacob Skaria" wrote: Rick --the requirement is to have this to sheet2 automatically..I am not sure whether Reza meant "as and when you change the values in the active sheet" or just once.. --You can get the total rows; the below will do... Reza If you are looking for a one time copy of data from active sheet to sheet2 you can use the below straight away...If you are looking for the data in sheet2 to update automatically I would suggest to go with the formula solution itself; however you can try out the below macro to be called from Worksheet change event.... Sub MyMacro() Dim lngRow As Long, ws As Worksheet, lngNRow As Long Set ws = Sheets("Sheet2"): ws.UsedRange.Clear For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row If Range("C" & lngRow) < "" Then _ lngNRow = lngNRow + 1: Rows(lngRow).Copy ws.Rows(lngNRow) Next End Sub If this post helps click Yes --------------- Jacob Skaria "BSc Chem Eng Rick" wrote: Hi Reza, Jacob has a good solution, if you'd prefer here is a macro I used to do this task. It works well, if you need help give me a shout. Note, all you need to change are the TotalRows and TotalCols variables which I have set to 5 as per your example. Just copy this into a module and run. Sub MySort() Dim Orig As Worksheet, Destination As Worksheet Dim RowOrig As Long, RowDest As Long Dim TotalRows As String, TotalCols As Long Dim Cell As Range Set Orig = ActiveWorkbook.Worksheets("Sheet1") Set Destination = ActiveWorkbook.Worksheets("Sheet2") RowOrig = 1 RowDest = 1 TotalCols = 5 TotalRows = 5 Application.ScreenUpdating = False Destination.Activate Destination.Cells.Select Selection.ClearContents For Each Cell In Orig.Range("A1:A" & TotalRows) If Cell.Value < "" Then For Cols = 2 To TotalCols If Orig.Cells(RowOrig, Cols) = "-" Then Exit For ElseIf Cols = TotalCols Then Orig.Rows(RowOrig).Copy Destination.Rows(RowDest).Select ActiveSheet.Paste RowDest = RowDest + 1 End If Next Cols End If RowOrig = RowOrig + 1 Next Cell Destination.Range("A1").Select Application.ScreenUpdating = True End Sub "reza" wrote: HI all.... i want to move data from sheet1 to another sheet, maybe it will need macro...hope you can help me. Example...in sheet1 i have: Line Col A Col B Col C Col D AA 10 5 4 1 BB 5 - - - FF 13 3 2 2 KK 10 - - - now in sheet2 i want to create resume from sheet1 automatically.... but i want if col B is empty or no data...it can't move to sheet2... so and in sheet2 Line Col A Col B Col C Col D AA 10 5 4 1 FF 13 3 2 2 thanks in advance reza |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
move blank data
Jacob....
sorry...in that formula...can you add another formula in order to sort by alphabet... Analyst Design Design Program Program etc... thanks again... "Jacob Skaria" wrote: Since you need to to automatically populate; I would suggest to go with the formula and ignore the macro solution... If this post helps click Yes --------------- Jacob Skaria "reza" wrote: to all: thanks for give me solution... but like jacob said...yes i want it update automatically...so i do not run every time again.... to Jacob: What should i do with your macro code...should i use with your formula above...can you tell me more detail... thanks "Jacob Skaria" wrote: Rick --the requirement is to have this to sheet2 automatically..I am not sure whether Reza meant "as and when you change the values in the active sheet" or just once.. --You can get the total rows; the below will do... Reza If you are looking for a one time copy of data from active sheet to sheet2 you can use the below straight away...If you are looking for the data in sheet2 to update automatically I would suggest to go with the formula solution itself; however you can try out the below macro to be called from Worksheet change event.... Sub MyMacro() Dim lngRow As Long, ws As Worksheet, lngNRow As Long Set ws = Sheets("Sheet2"): ws.UsedRange.Clear For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row If Range("C" & lngRow) < "" Then _ lngNRow = lngNRow + 1: Rows(lngRow).Copy ws.Rows(lngNRow) Next End Sub If this post helps click Yes --------------- Jacob Skaria "BSc Chem Eng Rick" wrote: Hi Reza, Jacob has a good solution, if you'd prefer here is a macro I used to do this task. It works well, if you need help give me a shout. Note, all you need to change are the TotalRows and TotalCols variables which I have set to 5 as per your example. Just copy this into a module and run. Sub MySort() Dim Orig As Worksheet, Destination As Worksheet Dim RowOrig As Long, RowDest As Long Dim TotalRows As String, TotalCols As Long Dim Cell As Range Set Orig = ActiveWorkbook.Worksheets("Sheet1") Set Destination = ActiveWorkbook.Worksheets("Sheet2") RowOrig = 1 RowDest = 1 TotalCols = 5 TotalRows = 5 Application.ScreenUpdating = False Destination.Activate Destination.Cells.Select Selection.ClearContents For Each Cell In Orig.Range("A1:A" & TotalRows) If Cell.Value < "" Then For Cols = 2 To TotalCols If Orig.Cells(RowOrig, Cols) = "-" Then Exit For ElseIf Cols = TotalCols Then Orig.Rows(RowOrig).Copy Destination.Rows(RowDest).Select ActiveSheet.Paste RowDest = RowDest + 1 End If Next Cols End If RowOrig = RowOrig + 1 Next Cell Destination.Range("A1").Select Application.ScreenUpdating = True End Sub "reza" wrote: HI all.... i want to move data from sheet1 to another sheet, maybe it will need macro...hope you can help me. Example...in sheet1 i have: Line Col A Col B Col C Col D AA 10 5 4 1 BB 5 - - - FF 13 3 2 2 KK 10 - - - now in sheet2 i want to create resume from sheet1 automatically.... but i want if col B is empty or no data...it can't move to sheet2... so and in sheet2 Line Col A Col B Col C Col D AA 10 5 4 1 FF 13 3 2 2 thanks in advance reza |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
move blank data
Try the below UDF.Try this UDF (User Defined function). From workbook launch
VBE using Alt+F11. From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula..For your case the formula would be In Sheet1 cell A1 contains the PC number.. =VLOOKUP_CONCAT(Sheet2!A1:B1000,A1,2) In genral the Syntax: =VLOOKUP_CONCAT(rngRange,strLookupValue,intColumn, strDelimiter) rngRange is the Range strLookupValue is the lookup string or cell reference inColumn is the column to be concatenated strDelimiter Optional . Default is space Examples: '1. To vlookup 'jacob' and concatenate all entries of 2nd column =VLOOKUP_CONCAT(A1:B10,"jacob",2) '2. with lookup value in cell C1 =VLOOKUP_CONCAT(A1:B10,C1,2) '3. with delimiter as comma =VLOOKUP_CONCAT(A1:B10,C1,2,",") Function VLOOKUP_CONCAT(rngRange As Range, _ strLookupValue As String, intColumn As Integer, _ Optional strDelimiter As String = " ") Dim lngRow As Long For lngRow = 1 To rngRange.Rows.Count If CStr(rngRange(lngRow, 1)) = strLookupValue Then _ VLOOKUP_CONCAT = VLOOKUP_CONCAT & strDelimiter & _ rngRange(lngRow, intColumn) Next VLOOKUP_CONCAT = Mid(VLOOKUP_CONCAT, 2) End Function If this post helps click Yes --------------- Jacob Skaria "BSc Chem Eng Rick" wrote: Hi Reza, Jacob has a good solution, if you'd prefer here is a macro I used to do this task. It works well, if you need help give me a shout. Note, all you need to change are the TotalRows and TotalCols variables which I have set to 5 as per your example. Just copy this into a module and run. Sub MySort() Dim Orig As Worksheet, Destination As Worksheet Dim RowOrig As Long, RowDest As Long Dim TotalRows As String, TotalCols As Long Dim Cell As Range Set Orig = ActiveWorkbook.Worksheets("Sheet1") Set Destination = ActiveWorkbook.Worksheets("Sheet2") RowOrig = 1 RowDest = 1 TotalCols = 5 TotalRows = 5 Application.ScreenUpdating = False Destination.Activate Destination.Cells.Select Selection.ClearContents For Each Cell In Orig.Range("A1:A" & TotalRows) If Cell.Value < "" Then For Cols = 2 To TotalCols If Orig.Cells(RowOrig, Cols) = "-" Then Exit For ElseIf Cols = TotalCols Then Orig.Rows(RowOrig).Copy Destination.Rows(RowDest).Select ActiveSheet.Paste RowDest = RowDest + 1 End If Next Cols End If RowOrig = RowOrig + 1 Next Cell Destination.Range("A1").Select Application.ScreenUpdating = True End Sub "reza" wrote: HI all.... i want to move data from sheet1 to another sheet, maybe it will need macro...hope you can help me. Example...in sheet1 i have: Line Col A Col B Col C Col D AA 10 5 4 1 BB 5 - - - FF 13 3 2 2 KK 10 - - - now in sheet2 i want to create resume from sheet1 automatically.... but i want if col B is empty or no data...it can't move to sheet2... so and in sheet2 Line Col A Col B Col C Col D AA 10 5 4 1 FF 13 3 2 2 thanks in advance reza |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
move blank data
Sorry, please ignore the below post....
If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below UDF.Try this UDF (User Defined function). From workbook launch VBE using Alt+F11. From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula..For your case the formula would be In Sheet1 cell A1 contains the PC number.. =VLOOKUP_CONCAT(Sheet2!A1:B1000,A1,2) In genral the Syntax: =VLOOKUP_CONCAT(rngRange,strLookupValue,intColumn, strDelimiter) rngRange is the Range strLookupValue is the lookup string or cell reference inColumn is the column to be concatenated strDelimiter Optional . Default is space Examples: '1. To vlookup 'jacob' and concatenate all entries of 2nd column =VLOOKUP_CONCAT(A1:B10,"jacob",2) '2. with lookup value in cell C1 =VLOOKUP_CONCAT(A1:B10,C1,2) '3. with delimiter as comma =VLOOKUP_CONCAT(A1:B10,C1,2,",") Function VLOOKUP_CONCAT(rngRange As Range, _ strLookupValue As String, intColumn As Integer, _ Optional strDelimiter As String = " ") Dim lngRow As Long For lngRow = 1 To rngRange.Rows.Count If CStr(rngRange(lngRow, 1)) = strLookupValue Then _ VLOOKUP_CONCAT = VLOOKUP_CONCAT & strDelimiter & _ rngRange(lngRow, intColumn) Next VLOOKUP_CONCAT = Mid(VLOOKUP_CONCAT, 2) End Function If this post helps click Yes --------------- Jacob Skaria "BSc Chem Eng Rick" wrote: Hi Reza, Jacob has a good solution, if you'd prefer here is a macro I used to do this task. It works well, if you need help give me a shout. Note, all you need to change are the TotalRows and TotalCols variables which I have set to 5 as per your example. Just copy this into a module and run. Sub MySort() Dim Orig As Worksheet, Destination As Worksheet Dim RowOrig As Long, RowDest As Long Dim TotalRows As String, TotalCols As Long Dim Cell As Range Set Orig = ActiveWorkbook.Worksheets("Sheet1") Set Destination = ActiveWorkbook.Worksheets("Sheet2") RowOrig = 1 RowDest = 1 TotalCols = 5 TotalRows = 5 Application.ScreenUpdating = False Destination.Activate Destination.Cells.Select Selection.ClearContents For Each Cell In Orig.Range("A1:A" & TotalRows) If Cell.Value < "" Then For Cols = 2 To TotalCols If Orig.Cells(RowOrig, Cols) = "-" Then Exit For ElseIf Cols = TotalCols Then Orig.Rows(RowOrig).Copy Destination.Rows(RowDest).Select ActiveSheet.Paste RowDest = RowDest + 1 End If Next Cols End If RowOrig = RowOrig + 1 Next Cell Destination.Range("A1").Select Application.ScreenUpdating = True End Sub "reza" wrote: HI all.... i want to move data from sheet1 to another sheet, maybe it will need macro...hope you can help me. Example...in sheet1 i have: Line Col A Col B Col C Col D AA 10 5 4 1 BB 5 - - - FF 13 3 2 2 KK 10 - - - now in sheet2 i want to create resume from sheet1 automatically.... but i want if col B is empty or no data...it can't move to sheet2... so and in sheet2 Line Col A Col B Col C Col D AA 10 5 4 1 FF 13 3 2 2 thanks in advance reza |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
move blank data
Hi Jacob....
sorry i really don't get it... Step 1: First i copy and paste your function in Module : (Function VLOOKUP_CONCAT(rngRange As Range, _ strLookupValue As String, intColumn As Integer, _ Optional strDelimiter As String = " ") Dim lngRow As Long For lngRow = 1 To rngRange.Rows.Count If CStr(rngRange(lngRow, 1)) = strLookupValue Then _ VLOOKUP_CONCAT = VLOOKUP_CONCAT & strDelimiter & _ rngRange(lngRow, intColumn) Next VLOOKUP_CONCAT = Mid(VLOOKUP_CONCAT, 2) End Function) Step 2: Step 3: i dont understand...hope you can give me more detail about this... thanks "Jacob Skaria" wrote: Try the below UDF.Try this UDF (User Defined function). From workbook launch VBE using Alt+F11. From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula..For your case the formula would be In Sheet1 cell A1 contains the PC number.. =VLOOKUP_CONCAT(Sheet2!A1:B1000,A1,2) In genral the Syntax: =VLOOKUP_CONCAT(rngRange,strLookupValue,intColumn, strDelimiter) rngRange is the Range strLookupValue is the lookup string or cell reference inColumn is the column to be concatenated strDelimiter Optional . Default is space Examples: '1. To vlookup 'jacob' and concatenate all entries of 2nd column =VLOOKUP_CONCAT(A1:B10,"jacob",2) '2. with lookup value in cell C1 =VLOOKUP_CONCAT(A1:B10,C1,2) '3. with delimiter as comma =VLOOKUP_CONCAT(A1:B10,C1,2,",") Function VLOOKUP_CONCAT(rngRange As Range, _ strLookupValue As String, intColumn As Integer, _ Optional strDelimiter As String = " ") Dim lngRow As Long For lngRow = 1 To rngRange.Rows.Count If CStr(rngRange(lngRow, 1)) = strLookupValue Then _ VLOOKUP_CONCAT = VLOOKUP_CONCAT & strDelimiter & _ rngRange(lngRow, intColumn) Next VLOOKUP_CONCAT = Mid(VLOOKUP_CONCAT, 2) End Function If this post helps click Yes --------------- Jacob Skaria "BSc Chem Eng Rick" wrote: Hi Reza, Jacob has a good solution, if you'd prefer here is a macro I used to do this task. It works well, if you need help give me a shout. Note, all you need to change are the TotalRows and TotalCols variables which I have set to 5 as per your example. Just copy this into a module and run. Sub MySort() Dim Orig As Worksheet, Destination As Worksheet Dim RowOrig As Long, RowDest As Long Dim TotalRows As String, TotalCols As Long Dim Cell As Range Set Orig = ActiveWorkbook.Worksheets("Sheet1") Set Destination = ActiveWorkbook.Worksheets("Sheet2") RowOrig = 1 RowDest = 1 TotalCols = 5 TotalRows = 5 Application.ScreenUpdating = False Destination.Activate Destination.Cells.Select Selection.ClearContents For Each Cell In Orig.Range("A1:A" & TotalRows) If Cell.Value < "" Then For Cols = 2 To TotalCols If Orig.Cells(RowOrig, Cols) = "-" Then Exit For ElseIf Cols = TotalCols Then Orig.Rows(RowOrig).Copy Destination.Rows(RowDest).Select ActiveSheet.Paste RowDest = RowDest + 1 End If Next Cols End If RowOrig = RowOrig + 1 Next Cell Destination.Range("A1").Select Application.ScreenUpdating = True End Sub "reza" wrote: HI all.... i want to move data from sheet1 to another sheet, maybe it will need macro...hope you can help me. Example...in sheet1 i have: Line Col A Col B Col C Col D AA 10 5 4 1 BB 5 - - - FF 13 3 2 2 KK 10 - - - now in sheet2 i want to create resume from sheet1 automatically.... but i want if col B is empty or no data...it can't move to sheet2... so and in sheet2 Line Col A Col B Col C Col D AA 10 5 4 1 FF 13 3 2 2 thanks in advance reza |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to delete blank rows and move data/info | Excel Discussion (Misc queries) | |||
How do I delete blank rows (rows alternate data, blank, data, etc | Excel Discussion (Misc queries) | |||
Remove Civic numbers in Street Address, move to previous blank cell | Excel Worksheet Functions | |||
Imported Data creates blank cells that aren't really blank | Excel Worksheet Functions | |||
enter data in cell which will start macro to move data to sheet2 | Excel Discussion (Misc queries) |