move blank data

[reza]

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

 

[Jacob Skaria]

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 A11 copy/drag the formula down as required

=IF(COUNTA(Sheet1!$C$1:$C$1000)<ROW(A1),"",INDEX(Sheet1!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(Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$B$1:$B$1000<>"",ROW($A$1:$A$1000)),ROW(A1))))


If this post helps click Yes

 

[BSc Chem Eng Rick]

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

 

[Jacob Skaria]

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

 

[reza]

wow...another great answer...

thanks

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 A11 copy/drag the formula down as required

=IF(COUNTA(Sheet1!$C$1:$C$1000)<ROW(A1),"",INDEX(Sheet1!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(Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$B$1:$B$1000<>"",ROW($A$1:$A$1000)),ROW(A1))))


If this post helps click Yes

 

[reza]

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]

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

 

[reza]

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]

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]

Sorry, please ignore the below post....

If this post helps click Yes

 

[reza]

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