Need to sort file so that cells that have a certain column blank come first

N

nfschuck

New Member
Joined
Jul 16, 2013
Messages
5
I have an Excel file with about 30,000 cells in it. Here's an example of what the file looks like here. And I need it to be sorted like this.

To explain further, If someone's name(B) or plan number (A) has blank cells AND additional names under the "Beneficiary Name" column (F), I want these cells to be in the bottom of the file. BUT, if someone's name (B) or plan number (A) ONLY has blank cells under the "Beneficiary Name" column (F), THAT is what I want in the beginning of the file.

Please let me know if you can provide any helpful tips.

Thank you.

--Nick
 
Sort by date Sort by votes
Here's the data from the first screen shot if you want to try it out:
[TABLE="width: 561"]
<TBODY>[TR]
[TD]Plan Number</SPAN>[/TD]
[TD]Name</SPAN>[/TD]
[TD]Plan Type</SPAN>[/TD]
[TD]Open Date</SPAN>[/TD]
[TD]Bene Ind</SPAN>[/TD]
[TD]Beneficiary Name</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]123456789</SPAN>[/TD]
[TD]JON SNOW</SPAN>[/TD]
[TD="align: right"]001</SPAN>[/TD]
[TD="align: right"]9/24/2007</SPAN>[/TD]
[TD]N</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]123456789</SPAN>[/TD]
[TD]JON SNOW</SPAN>[/TD]
[TD="align: right"]001</SPAN>[/TD]
[TD="align: right"]9/24/2007</SPAN>[/TD]
[TD]N</SPAN>[/TD]
[TD]ROBB STARK</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]987654321</SPAN>[/TD]
[TD]NED STARK</SPAN>[/TD]
[TD="align: right"]002</SPAN>[/TD]
[TD="align: right"]6/12/2007</SPAN>[/TD]
[TD]N</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]987654321</SPAN>[/TD]
[TD]NED STARK</SPAN>[/TD]
[TD="align: right"]002</SPAN>[/TD]
[TD="align: right"]6/12/2007</SPAN>[/TD]
[TD]N</SPAN>[/TD]
[TD]BRANDON STARK</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]987654321</SPAN>[/TD]
[TD]NED STARK</SPAN>[/TD]
[TD="align: right"]002</SPAN>[/TD]
[TD="align: right"]6/12/2007</SPAN>[/TD]
[TD]N</SPAN>[/TD]
[TD]BRANDON STARK</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]147258369</SPAN>[/TD]
[TD]CAT TULLY</SPAN>[/TD]
[TD="align: right"]001</SPAN>[/TD]
[TD="align: right"]12/8/2006</SPAN>[/TD]
[TD]N</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]147258369</SPAN>[/TD]
[TD]CAT TULLY</SPAN>[/TD]
[TD="align: right"]001</SPAN>[/TD]
[TD="align: right"]12/8/2006</SPAN>[/TD]
[TD]N</SPAN>[/TD]
[TD]BRYNDEN TULLY</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]147258369</SPAN>[/TD]
[TD]CAT TULLY</SPAN>[/TD]
[TD="align: right"]001</SPAN>[/TD]
[TD="align: right"]12/8/2006</SPAN>[/TD]
[TD]N</SPAN>[/TD]
[TD]BRYNDEN TULLY</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]963852741</SPAN>[/TD]
[TD]ADRIANNE MARTELL</SPAN>[/TD]
[TD="align: right"]001</SPAN>[/TD]
[TD="align: right"]10/9/2008</SPAN>[/TD]
[TD]N</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]159753456</SPAN>[/TD]
[TD]RAMSAY BOLTON</SPAN>[/TD]
[TD="align: right"]002</SPAN>[/TD]
[TD="align: right"]2/9/2010</SPAN>[/TD]
[TD]N</SPAN>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]159753456</SPAN>[/TD]
[TD]RAMSAY BOLTON</SPAN>[/TD]
[TD="align: right"]002</SPAN>[/TD]
[TD="align: right"]2/9/2010</SPAN>[/TD]
[TD]N</SPAN>[/TD]
[TD]JAIME LANNISTER</SPAN>[/TD]
[/TR]
[TR]
[TD="align: right"]159753456</SPAN>[/TD]
[TD]RAMSAY BOLTON</SPAN>[/TD]
[TD="align: right"]002</SPAN>[/TD]
[TD="align: right"]2/9/2010</SPAN>[/TD]
[TD]N</SPAN>[/TD]
[TD]JAIME LANNISTER</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]
 
Upvote 0
If I understand correctly what you want, maybe this can help you:

In column H create a Help Column with this array formula (use Ctrl+Shift+Enter and not only Enter):

Code: 
=OR(A2:B2="")*(F2<>"")-OR(A2:B2<>"")*(F2="")

Markmzz
 
Upvote 0
markmzz said:
If I understand correctly what you want, maybe this can help you:

In column H create a Help Column with this array formula (use Ctrl+Shift+Enter and not only Enter):

Code: 
=OR(A2:B2="")*(F2<>"")-OR(A2:B2<>"")*(F2="")

Markmzz
Click to expand...

Sorry, in column G and not H.

Markmzz
 
Upvote 0
Thanks for your response, Markmzz

But this forumla
=OR(A2:B2="")*(F2<>"")-OR(A2:B2<>"")*(F2="") just gave me 0's when I did it. But I edited it so that wherever there are "", I put " " instead but that just gave me 0 if column F was blank, and -1 if column F contained characters. It's close to what I need but not quite. I need accounts that only have blank cells to be sorted at the top, this means that if the name appears again underneath the cell with a name under column F, I want those to be sorted at the bottom.It's hard to explain but I hope that makes sense.Thanks for your help Markmzz, let me know if you can help me with the next step!
--Nick
</PRE>
 
Upvote 0
nfschuck said:
Thanks for your response, Markmzz

But this forumla
=OR(A2:B2="")*(F2<>"")-OR(A2:B2<>"")*(F2="") just gave me 0's when I did it. But I edited it so that wherever there are "", I put " " instead but that just gave me 0 if column F was blank, and -1 if column F contained characters. It's close to what I need but not quite. I need accounts that only have blank cells to be sorted at the top, this means that if the name appears again underneath the cell with a name under column F, I want those to be sorted at the bottom.It's hard to explain but I hope that makes sense.Thanks for your help Markmzz, let me know if you can help me with the next step!
--Nick
Click to expand...

Here is what I have with your data and my array formula:

Code: 
Plan Number	Name	Plan Type	Open Date	Bene Ind Beneficiary Name Help Col
963852741	ADRIANNE MARTELL001	10/09/08	N		-1
147258369	CAT TULLY	001	12/08/06	N		-1
123456789	JON SNOW	001	24/09/07	N		-1
987654321	NED STARK	002	06/12/07	N		-1
159753456	RAMSAY BOLTON	002	02/09/10	N		-1
147258369	CAT TULLY	001	12/08/06	N	BRYNDEN TULLY	0
147258369	CAT TULLY	001	12/08/06	N	BRYNDEN TULLY	0
123456789	JON SNOW	001	24/09/07	N	ROBB STARK	0
987654321	NED STARK	002	06/12/07	N	BRANDON STARK	0
987654321	NED STARK	002	06/12/07	N	BRANDON STARK	0
159753456	RAMSAY BOLTON	002	02/09/10	N	JAIME LANNISTER	0
159753456	RAMSAY BOLTON	002	02/09/10	N	JAIME LANNISTER	0
159753457	RAMSAY BOLTON	003	03/09/10	N	JAIME LANNISTER	0

This is what you want?

Markmzz
 
Upvote 0
Not quite. For example, the account of NED STARK, although having a blank cell under column F, this account also has names under column F. But ADRIANNE MARTELL, only has a blank cell under column F, with NO additional names. So in this case, ADRIANNE MARTELL would be sorted at the top since it has no names under column F and the rest would be sorted at the bottom (since they have additional names in column F under their account).

Does that make sense?
 
Upvote 0
nfschuck said:
Not quite. For example, the account of NED STARK, although having a blank cell under column F, this account also has names under column F. But ADRIANNE MARTELL, only has a blank cell under column F, with NO additional names. So in this case, ADRIANNE MARTELL would be sorted at the top since it has no names under column F and the rest would be sorted at the bottom (since they have additional names in column F under their account).

Does that make sense?
Click to expand...

Maybe this formula:

Code: 
=COUNTIFS($A$2:$A$14,A2,$F$2:$F$14,"*")

Markmzz
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Locked
  • Question Question
Severe Amateur needs code to pull data from multiple sheets into one master
Replies
1
Views
160
Peter_SSs
Peter_SSs
R
  • Solved
Macro to loop through workbooks and find certain text in multiple rows
Replies
6
Views
155
rachel06
R
R
  • Question Question
Spill array that only shows rows that have value in certain cells
Replies
1
Views
228
Alex Blakenburg
A
H
  • Question Question
How Can I Automatically Delete Only Completely Blank Columns in Excel 2010?
Replies
3
Views
538
Logit
Logit
I
  • Question Question
Converting employee records to a table (I think I need VBA code)
Replies
9
Views
158
Hans Troost
H

Share this page

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
Back
Top