Elegant way to find row in dataset with the largest number of non-empty columns?

[TheDougmeister]

I have a large dataset and am looking for a good "example" row.

Every row will have a "blank" (empty) value in at least one of the columns.

Is there an easy, elegant way to find out which row has the most columns with actual data?

I thought about exporting to a CSV and quickly scrolling down the file to "eyeball" which row is the "longest", but there must be a better way.

Thoughts?

 

[Joe4]

You can use the COUNTA function to count how many columns in a row have entries in them.
So do that on each row, and see which one returns the highest value.

See: How to use the Excel COUNTA function | Exceljet

 

[TheDougmeister]

Thanks again, @Joe4 .

As I examine my data, I also see a lot of columns that have all zeros (either '0' or even '00/00/0000' in a "General" formatted cell, not "Date"). Should I start a new thread to ask how to also look for non-zero values or just ask here?

 

[Joe4]

You have a few options.

You could try to use a COUNTIFS formula, where you listed multiple criteria in telling it values NOT to select (see here: Excel formula: Count cells not equal to many things | Exceljet.)

You could also do a Find/Replace, replacing all the instance of values like 00/00/0000 with nothing.
If you do decide to go that route, I would HIGHLY recommend expanding the Options on the Replace tab, and make sure that you have the "Match entire cell contents" option checked.
That way, if you wanted to replace the 0's with nothing, it would only replace the values EXACLTY equal to 0, and not remove the 0 from numbers like "104", leaving "14".