PDA

View Full Version : Create column with descending numbers corresponding to data in another column.


DBuslach
Oct 17, 2008, 11:06 PM
I created an alphabetized column with names. In another column I put an x next to those names that were heads of households leaving the rest blank. I would like to create a new column that would recognize the x then insert a number in the cell next to the x in descending order (i.e. 1-50).

JBeaucaire
Oct 17, 2008, 11:47 PM
Let's assume the following:

Column A = X or no X
Column B = Names
Column C = the desired numbers

Here's the formula you put in cell C1:
=IF(ISBLANK(A1),"",COUNTIF($A$1:$A1,"X"))

Now, if you've copied/pasted that formula accurately, you can copy that formula down the rest of the C column and the numbers will increment properly. See the pic below.

Explanation - simply, we look at column A for an X, if we find one, we count all the X's up to that point and show the total.

When you go to adapt this formula for your own sheet, keep in mind all the references in the COUNTIF function have a $ sign to make them absolute references, except the last part of the second A1. This means as you copy the formula down, the only digit that is allowed to change is that last digit. In the second row, it would appear as $A$1:$A2. This why is works to count all the way up, you've locked the beginning of the range while leaving the end of the range relative.