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