Sorting by looking for blanks

M

mlk

I am working in Excel 2000 and I have a file that has approximately 1000 rows
of data. I have 13 columns that reflect different months. Some columns have
a Y in them which means work was done in these months but there are other
columns that do not have a Y. I have done a sort so that I have all the ones
that have a Y under all 13 columns. Unluckily, after that there is no way to
see if there is someone that may be missing only one month unless you go
through all 1000 rows. Is there any way that this can be done to see if
someone is missing one month, 2 months, etc.
 
J

jjanyan

A way that will work, but might be a bit rough around the edges would
be this...

(Assuming you have an ID in Column A, then Months in B-N)

= if(B1 = "Y", 1,0) + if(C1 = "Y", 1,0) + if(D1 = "Y", 1,0).... etc.
this will give a "1" for each Y(es). it then totals them up, giving you
your total of months of Yes, if you want the opposite, just take
= 13 - if(B1 = "Y", 1,0) + .......

Please rate if this was helpful.
Hope it was.
J
 
D

Debra Dalgleish

You could add a column to your table, and enter a formula to count the
Ys in each row. For example, in cell N2, enter:

=COUNTIF(B2:M2,"Y")

Copy the formula down to the last row of data, then sort by the new column.
 
Top