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

[nfschuck]

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

 

[nfschuck]

Here's the data from the first screen shot if you want to try it out:

Plan Number</SPAN>	Name</SPAN>	Plan Type</SPAN>	Open Date</SPAN>	Bene Ind</SPAN>	Beneficiary Name</SPAN>
123456789</SPAN>	JON SNOW</SPAN>	001</SPAN>	9/24/2007</SPAN>	N</SPAN>
123456789</SPAN>	JON SNOW</SPAN>	001</SPAN>	9/24/2007</SPAN>	N</SPAN>	ROBB STARK</SPAN>
987654321</SPAN>	NED STARK</SPAN>	002</SPAN>	6/12/2007</SPAN>	N</SPAN>
987654321</SPAN>	NED STARK</SPAN>	002</SPAN>	6/12/2007</SPAN>	N</SPAN>	BRANDON STARK</SPAN>
987654321</SPAN>	NED STARK</SPAN>	002</SPAN>	6/12/2007</SPAN>	N</SPAN>	BRANDON STARK</SPAN>
147258369</SPAN>	CAT TULLY</SPAN>	001</SPAN>	12/8/2006</SPAN>	N</SPAN>
147258369</SPAN>	CAT TULLY</SPAN>	001</SPAN>	12/8/2006</SPAN>	N</SPAN>	BRYNDEN TULLY</SPAN>
147258369</SPAN>	CAT TULLY</SPAN>	001</SPAN>	12/8/2006</SPAN>	N</SPAN>	BRYNDEN TULLY</SPAN>
963852741</SPAN>	ADRIANNE MARTELL</SPAN>	001</SPAN>	10/9/2008</SPAN>	N</SPAN>
159753456</SPAN>	RAMSAY BOLTON</SPAN>	002</SPAN>	2/9/2010</SPAN>	N</SPAN>
159753456</SPAN>	RAMSAY BOLTON</SPAN>	002</SPAN>	2/9/2010</SPAN>	N</SPAN>	JAIME LANNISTER</SPAN>
159753456</SPAN>	RAMSAY BOLTON</SPAN>	002</SPAN>	2/9/2010</SPAN>	N</SPAN>	JAIME LANNISTER</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP>

 

[markmzz]

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):

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

Markmzz

 

[markmzz]

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):

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

Markmzz

Sorry, in column G and not H.

Markmzz

 

[nfschuck]

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>

 

[markmzz]

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

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

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

 

[nfschuck]

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?

 

[markmzz]

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?

Maybe this formula:

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

Markmzz

 

[nfschuck]

YES! Thank you so much, Markmzz, that's exactly what I needed!

I appreciate all your help!

--Nick

 

[markmzz]

YES! Thank you so much, Markmzz, that's exactly what I needed!

I appreciate all your help!

--Nick

Nick,

I'm glad that the formula work.

And thanks for the feedback.

Markmzz