Sorting rows based on populated columns

rufuxinix

New Member
Joined
Jan 23, 2019
Messages
34
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,341
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Select All Range go to Sort, Custom Sort, Add Levels Until wants, then Select Columns Criteria, respectively, Also tick my table Has headers (if your Data has).
 

rufuxinix

New Member
Joined
Jan 23, 2019
Messages
34
Select All Range go to Sort, Custom Sort, Add Levels Until wants, then Select Columns Criteria, respectively, Also tick my table Has headers (if your Data has).
Thanks but doesn't work as intended.
It will sort but will also include columns which I do not want to see
so, on the x, y, z example it will give me rows where x is present even if y and z are not present in that row
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,341
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
It will sort but will also include columns which I do not want to see
You want sort only one column within Table?
If Yes. Select one cell within Table, Go to Design tab, Convert to Range,
Only Select Column that you want sort, Sort it
Then Again Press CTRL+T to Convert Range to Table
 

rufuxinix

New Member
Joined
Jan 23, 2019
Messages
34

ADVERTISEMENT

You want sort only one column within Table?
If Yes. Select one cell within Table, Go to Design tab, Convert to Range,
Only Select Column that you want sort, Sort it
Then Again Press CTRL+T to Convert Range to Table
i have a table with several columns.
Ex:
a b c d e f
x x x
x x x x
x x x x x

I want to be able to extract rows that have column a, c, and e populated

The result should show me Row 1 and Row 3.

Row 2 will not be extracted because it does not meet my criteria, as Column a is not populated


.
 

rufuxinix

New Member
Joined
Jan 23, 2019
Messages
34
i have a table with several columns.
Ex:
a b c d e f
x x x
x x x x
x x x x x

I want to be able to extract rows that have column a, c, and e populated

The result should show me Row 1 and Row 3.

Row 2 will not be extracted because it does not meet my criteria, as Column a is not populated


.
Sorry, the text might not show properly.
Please see below. Rows 2 and 5 should be extracted

ABCDEFG
XXXX
XXXX
XXXXX
XXXXX
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,341
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

You can do it with Macro/VBA.
What is your Criteria? I don't understand?
 

rufuxinix

New Member
Joined
Jan 23, 2019
Messages
34
You can do it with Macro/VBA.
What is your Criteria? I don't understand?
So i would like to extract the rows I want, into a new table.
Criteria would be
I need a table with all the rows that have columns a, c, e populated
or with all the rows that have columns b, c, d populated

Do you get it?
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,341
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.
 

rufuxinix

New Member
Joined
Jan 23, 2019
Messages
34
Ok, thank you.

So, from the ex table below I want to extract rows, which will appear in another sheet, based on criteria I enter.
Example
Based on the table below I want a new table with all the rows that have names in columns A, D, and F
The result would be a table with the following rows

ABCDEFGHIJ
JohnGeorgeRobertLisaJoey
JackJustinVaughanLouis
RodneyJohnLisaJoeyJack
GeorgeJustinLouisDerekMary
 

Forum statistics

Threads
1,141,129
Messages
5,704,448
Members
421,350
Latest member
jake9951

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top