Countif to find 2nd occurence in range

**BHudPE** · 01-31-2011, 01:13 PM

I have a range that includes entries with 2 A's, 2 B's, 2 C's etc.
I want to create a duplicate range with the 1st A as A-1, the 2nd A as A-2 and 1st B as B-1, the 2nd B as B-2, etc. (the attached file shows these in cells)

I have tried
=IF(B1="","",B1&"-"&COUNTIF($B$1:$B1,B1))
but the results are inconsistent, see 3rd range values in red

The attached file has 3 ranges, from top to bottom:
1) starting data
2) how I want the results to look
3) my approach with ....countif mentioned above.

Can someone show me how to modify the eqn to give me correct results in all the cells?
thx

**NBVC** · 01-31-2011, 01:22 PM

Try:

=IF(B2="","",B2&"-"&COUNTIF($B$2:$G2,B2))

**daddylonglegs** · 01-31-2011, 01:29 PM

Try this formula in B29 copied across and down

=IF(B2="","",B2&"-"&COUNTIF($B1:G$1,B2)+COUNTIF($B2:B2,B2))

[which would accommodate 2 of a letter on a single row - is that possible?]

**MarvinP** · 01-31-2011, 01:43 PM

Hi BHudPE,

Put this formula in B29, Pull down and across

=IF(B2="","",B2&"-"&COUNTIF(OFFSET($B$2,0,0,ROW(B1),6),B2))

I think this is what you want.

Let us know which one of the 3 formulas worked.

**BHudPE** · 01-31-2011, 09:31 PM

All three worked, thx for the help. In answering your Q daddylonglegs, no the same letter will not be placed in the same row. But I will study your eqn until I get it, because future uses may fit.

On the attached file, I added ranges for all 3 eqns I rec'd, so I can inspect them to learn how they work.

I can't figure out something on the 2nd eqn (daddylonglegs). When seeing your eqn copied dn&over the "$" (from F4) doesn't seem to protected the correct Row &/or Col from changing. See the Blue cells for what I think it should be. Even tho the eqn copied dn&over funny, it works. I'm glad it works, but I don't understand why the cell refs aren't what I would have expected. Do you understand my question daddylonglegs?

**daddylonglegs** · 02-01-2011, 07:41 AM

Yep, I messed up the references - what I meant to post was

=IF(B2="","",B2&"-"&COUNTIF($B1:$G1,B2)+COUNTIF($B2:B2,B2))

The idea is that the first COUNTIF counts all the instances of B2 in the rows above and the second COUNTIF counts all the instances on that row but only in the cells to the left, I think you'd need that approach if letters were repeated in a row but if you don't then the other approaches suggested by Marvin and NBVC will be better.....

As it turns out my incorrect $ gives you an invalid range when copied down (you can't have B2:G1 for example) so Excel appears to correct it, and although you get cells referenced to the right, e.g. in columns H and beyond, which I didn't intend, it still works to get the right result because there didn't happen to be anything in those columns.....