Count Column blank entries, reset after entry in cell

**Sedge** · 01-26-2011, 06:34 PM

Hi Didn't know how to word the question for a search for this one so..
I have 6 columns, E,F,G,H,I,J. columns are populated (updated) by a 'countif' result on a continuing basis.. Only one result total for each row per time.

E F G H I J
0 0 0 0 0 0
0 0 0 0 0 1
0 0 0 1 0 0
0 0 0 1 0 0
0 1 0 0 0 0
0 1 0 0 0 0
0 1 0 0 0 0
0 0 1 0 0 0
0 0 0 1 0 0

I want to know which two columns have the longest set of blank cells after each update with a reset when a '1' shows.

For example as above the columns that have the most blanks in a row are "E" and "I".
If Column "E" got the next cell populated, then columns "I" and "J" would be the answer required.

Just don't know how to code it.

Appreciate your help.
Excel 2007

Thanks Sedge

**DonkeyOte** · 01-26-2011, 06:45 PM

Sedge, not entirely sure I follow but the simplest thing may be to store the max streak of each column separately with a unique decimal value.

Using your sample data - let's assume stored in E1:J9

Please Login or Register  to view this content.

Then let's say you want the top two column numbers returned in L1:L2

Please Login or Register  to view this content.

which would return 5 (E) & 9 (I) respectively - this assumes of course that columns containing nothing but 0 are valid.

edit: per your edit - if you want E & I rather than 5 and 9

Please Login or Register  to view this content.

**Sedge** · 01-26-2011, 07:16 PM

Thanks DonkeyOte, it sounds like what i want, I'm having trouble inputting the code. A further sample is attached.

I want to show which columns qualify, as E,I then I,J then J F etc.

I don't understand where to place the formula you sent me as if I place it at E11: it replaces my countif formula.

Sorry to be so blond :-)

Cheers

**DonkeyOte** · 01-27-2011, 05:22 AM

Not sure I totally understand the requirements (ie pairs of letters) nor why E would precede I given I has a longer streak then E (by my reckoning)

Based on the sample it's simply a case of modifying the ranges in the formulae per your own data - I had assumed for sake of example E1:J9 whereas reality is E10:J21

Please Login or Register  to view this content.

**Sedge** · 01-27-2011, 02:27 PM

Thanks DonkeyOte,

Not quite what I'm after but I see the principal, so should be able to apply it. It is difficult to get what is in my head down onto paper. :-). One last try... With the sample, the ongoing data is entered into column B and will continue for maybe 500 rows. A row by row event. Columns M and N represent the result for all preceeding rows for column E$10 to J21, next entry E$10 to J23 and so on.

Re your comment: at row 18 E is the same as I = 9 blanks each to date. At row 19 I preceeds J. I=10 blanks and J= 8 blanks. At row 20 J= 9 blanks, F = 4 blanks and so on as fresh data is entered.
The results of each uptade are shown on columns M and N. Copy the formula E21:J21 to say E22:J25. Place any of the range E1 to J6 into column B 22- to B25 to see what I want.

Thanks a bunch for the formulae, I will experiment as to the placement