Sorting rows based on populated columns

[rufuxinix]

Hello fundis
I have a table which I need to sort, based on column criteria i enter.
As an example I want to extract rows that are populated in columns x, y and z. There could be other columns in the row that are also populated, or not.
So the result could be
x, y, z
but it could also be, say,
a, c, x, y, z, d

Any formula or easy way to do this?

Thanks

 

[rufuxinix]

	Queried Table
	A	B	C	D	E	F	G
	John			George	Paul	Mary
		Jack	Peter			Lisa	Steve
	Mike	Ian	Ritchie	Sonia		Ed
	Alex	Sam	Stanley		Grace	Carlos
	Resulting Table
	A	B	C	D	E	F	G
	John			George	Paul	Mary
	Mike	Ian	Ritchie	Sonia		Ed

 

[rufuxinix]

You can Do it With formula? E.g.at sheet2 at A1 write =Sheet1!A1 And at B1 Write =Sheet1!C1 And ... Finally drag them down.
I cann't do it with Macro until I have a criteria for setting?

Please Upload Example file & Desired results with XL2BB ADDIN(Preferable) or upload at free hosting Site e.g. www.dropbox.com , GoogleDrive or OneDrive & insert Link here.

Sorry, I included the wrong table in my first response, please check 2nd response with 2 tables, ie, Queried Table and Resulting Table

Thanks

 

[rufuxinix]

	Queried Table
	A	B	C	D	E	F	G
	John			George	Paul	Mary
		Jack	Peter			Lisa	Steve
	Mike	Ian	Ritchie	Sonia		Ed
	Alex	Sam	Stanley		Grace	Carlos
	Resulting Table
	A	B	C	D	E	F	G
	John			George	Paul	Mary
	Mike	Ian	Ritchie	Sonia		Ed

 

[maabadi]

This Macro Extract Data from Sheet1 and Paste at Sheet2

Sub ExtractCriteriaRow()
Dim i As Long, Lr1 As Long, Lr2 As Long, Sh1 As Worksheet, Sh2 As Worksheet
Set Sh1 = Sheets("Sheet1")
Set Sh2 = Sheets("Sheet2")
Lr1 = Sh1.Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sh2.Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To Lr1
If Sh1.Range("A" & i) <> "" And Sh1.Range("D" & i) <> "" And Sh1.Range("F" & i) <> "" Then
Sh1.Range("A" & i & ":G" & i).Copy Sh2.Range("A" & Lr2 + 1)
Lr2 = Sh2.Range("A" & Rows.Count).End(xlUp).Row
End If
Next i
End Sub

 

[rufuxinix]

This Macro Extract Data from Sheet1 and Paste at Sheet2

Sub ExtractCriteriaRow()
Dim i As Long, Lr1 As Long, Lr2 As Long, Sh1 As Worksheet, Sh2 As Worksheet
Set Sh1 = Sheets("Sheet1")
Set Sh2 = Sheets("Sheet2")
Lr1 = Sh1.Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sh2.Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To Lr1
If Sh1.Range("A" & i) <> "" And Sh1.Range("D" & i) <> "" And Sh1.Range("F" & i) <> "" Then
Sh1.Range("A" & i & ":G" & i).Copy Sh2.Range("A" & Lr2 + 1)
Lr2 = Sh2.Range("A" & Rows.Count).End(xlUp).Row
End If
Next i
End Sub

Thank you very much!! Regards

 

[rufuxinix]

Thank you very much!! Regards

This Macro Extract Data from Sheet1 and Paste at Sheet2

Sub ExtractCriteriaRow()
Dim i As Long, Lr1 As Long, Lr2 As Long, Sh1 As Worksheet, Sh2 As Worksheet
Set Sh1 = Sheets("Sheet1")
Set Sh2 = Sheets("Sheet2")
Lr1 = Sh1.Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Sh2.Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To Lr1
If Sh1.Range("A" & i) <> "" And Sh1.Range("D" & i) <> "" And Sh1.Range("F" & i) <> "" Then
Sh1.Range("A" & i & ":G" & i).Copy Sh2.Range("A" & Lr2 + 1)
Lr2 = Sh2.Range("A" & Rows.Count).End(xlUp).Row
End If
Next i
End Sub

I have tried it and it works, thanks very much.
However, being blind to programming, how do I change the criteria (the columns I want) in an easy way, on the sheet I am working?
Another thing is, I would like, at times, to exclude certain columns from the result as it can become very crowded with columns I do not want.
Possible?

 

[maabadi]

1.Change this to your columns

If Sh1.Range("A" & i) <> "" And Sh1.Range("D" & i) <> "" And Sh1.Range("F" & i) <> "" Then

2. for 2nd question, we should copy separately columns that you want.

 

[rufuxinix]

1.Change this to your columns

If Sh1.Range("A" & i) <> "" And Sh1.Range("D" & i) <> "" And Sh1.Range("F" & i) <> "" Then

2. for 2nd question, we should copy separately columns that you want.

Sorry, I don't understand. This code is already included in the script

 

[rufuxinix]

Sorry, I don't understand. This code is already included in the script

I read through your response again, I now get what you meant. It's a matter of defining the columns on the code line.
Sorry, initially I read through it quickly and missed your point.
Thanks for your all your great help, it is highly appreciated!
Regards

 

[maabadi]

You're Welcome & Thanks for feedback.