I have incomplete contact list of about 2,000.
I want to sort by first, all the complete contacts (phone, address, and email),


then my people who have 2 of the 3 contact types,
then by people who only have 1 contact type.

example
rows 1-50 have data in columns A-E (5 columns total)
rows 51-80 only have data in 4 of those 5 columns
rows 81-150 only have data in 3 of those 5 columns
etc.

Is there any way to do that?

Thank you!!

See attached. I added a helper column that counts the number of those fields that have data. Then the table is sorted by first name, last name, and then fill count.

Attached Files

Contact.xlsx (10.8 KB, 11 views)

Great idea! How do I do that count? Can I do an "autosum" equation. I have about 2,000 contacts...

Thanks!

I'm not sure why you'd need to use Autosum.

I strongly suggest you do it as I have and utilize Excel's Format as Table functionality. It offers many advantages, but specifically for your list, it makes formulas more logical and entering them in a long list is a snap. Once you enter a new formula in one cell of a column, it automatically propagates through the rest of the cells unless you force it to do otherwise.

To use Format as Table, select your entire list. The easiest way to do this is to click on a single cell and then type CTRL-a. As long as all the data are contiguous (no completely blank rows or columns), Excel will select the entire list.

Then select Home: Format as Table (in the Styles submenu) on the ribbon. Select the style you like. You can easily change the style at any time.

Now in one of the cells to the right to the table, type and then drag your mouse over the three fields to be counted. Press enter. You should see Excel automatically add another column to the table and populate it with the formula:

Doesn't that look a lot more logical and intuitive than formulas that reference row and column addresses?

Trust me. If you learn this one simple thing, the Format as Table functionality, your Excel experience will change forever. Aside from the advantages already mentioned, adding/deleting data is a snap and filtering is built in.

If you run into trouble, let me know. If you want, I can do this for you in a snap if you post your sheet. Otherwise, if you prefer the classic look (please say no!), I can gin that up for you too.