Ask Experts Questions for FREE Help !
Ask
    DBuslach's Avatar
    DBuslach Posts: 1, Reputation: 1
    New Member
     
    #1

    Oct 17, 2008, 11:06 PM
    Create column with descending numbers corresponding to data in another column.
    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's Avatar
    JBeaucaire Posts: 5,426, Reputation: 997
    Software Expert
     
    #2

    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.
    Attached Images
     

Not your question? Ask your question View similar questions

 

Question Tools Search this Question
Search this Question:

Advanced Search

Add your answer here.


Check out some similar questions!

10 column Worksheet [ 3 Answers ]

What is a 10 - column worksheet? How is it completed and what it is for?

How to create a10 Column Worksheet? [ 1 Answers ]

I would like to know how to create a 10 column worksheet?:confused:

How can I sort data on a Hidden column in Excel? [ 2 Answers ]

I have a spreadsheet that has a pivot table in it. Each record has a time group column and a time nbr column in the pivot table. (i.e. Timegroup = 90 days before and a timenbr of 1 is assigned to this group, Timegroup = 60 days before and a timenbr of 2 is assigned to this group,...

Excluding data from column [ 5 Answers ]

hi. Excel question. I have a number of column's, but I am mainly concerned with 2 columns, and the generation of new column. One column is a series of data where values equal, 0, 1000, -1000, 2000, -2000, and 1,2,3... 9. the other column has modulus values corresponding to this data (0, 1000,...


View more questions Search