Count cells in column only if Previous is not Duplicate

[equilter]

I'd like to find a formula to count the number of non-blank entries in an entire column, but ONLY if the Previous cell is not a duplicate, and ONLY if the cell has EIGHT characters.

In other words, for the data:

AAAAAAAA
BBBBBBBB
CCCCCCCC
CCCCCCCC
DDDDDDDD
DDDDDDDD
CCCCCCCC
DDDDDDDD
EEEEEEEE
FFFF

The formula should count 7.
Please let me know.

[newdoverman]

I don't know how you get 7 as the count because there are only 6 unique items in the list with only 5 having 8 characters.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[equilter]

Thank you for your reply, and sorry about the confusion. We're not looking for unique values. We want to count the number of cells in the column but ONLY if the Previous cell is not a duplicate, and ONLY if the cell has EIGHT characters.

Also, there's no before and after sheet, as we simply want to enter the formula in a cell to the right of the column to calculate. Does this explanation below help?

AAAAAAAA -> Count this cell
BBBBBBBB -> Count this cell
CCCCCCCC -> Count this cell
CCCCCCCC -> Do NOT count, as previous cell above is same
DDDDDDDD -> Count this cell
DDDDDDDD -> Do NOT count, as previous cell above is same
CCCCCCCC -> Count this cell because previous cell is NOT the same
DDDDDDDD -> Count this cell because previous cell is NOT the same
EEEEEEEE -> Count this cell
FFFF -> Do NOT count, as it does not have 8 characters

[newdoverman]

Maybe this is what you are looking for. The data starts in A2 and the formula is entered in B2 and copied down.

Please Login or Register  to view this content.

[equilter]

Thank you, that does perform the count we need. Is it possible to have a single cell formula to accomplish this? I suppose we could hide the calculating column and capture the data from the last calculated cell in another visible cell. Thoughts?

[Vikas_Gautam]

Try .. Arrayed Formula..

Please Login or Register  to view this content.

Check the attached file..

[newdoverman]

Probably the easiest solution is to use is this simple formula

Please Login or Register  to view this content.

[Pepe Le Mokko]

Thank you, that does perform the count we need. Is it possible to have a single cell formula to accomplish this? I suppose we could hide the calculating column and capture the data from the last calculated cell in another visible cell. Thoughts?

One of the greatest guys around here, named DonkeyOte, always advocated the use of a helper column when necessary. And, yes, hiding it is a good idea.

Beware of array formulas, they will slow things down and get you in real trouble with larger spreadsheets

[equilter]

Thank you Vikas Gautam, that's perfect, and the Arrayed Formula works great!

[Vikas_Gautam]

Thanks for the response Equilter..
Thanks for the suggestion PEPE..
Actually I just gave what OP wanted..
I am a great Array Admirer, you now..


Regards,

[Pepe Le Mokko]

Well, as for me, I'm a great admirer of the KISS method