Welcome Guest
Log In | Register )
You last visited December 9, 2016, 4:45 am
All times shown are
Eastern Time (GMT-5:00)

Can anyone help me with this Excel question?

Topic closed. 3 replies. Last post 6 years ago by time*treat.

Page 1 of 1
Member #73987
April 30, 2009
33 Posts
Posted: January 26, 2011, 10:33 am - IP Logged

Hi Lottery Post'ers

I wonder if anyone can help me with this excel question.

In cells A1 to J1, I have 10 numbers:

A1 B1 C1 D1 E1 F1 G1 H1 I1 J1

 1  7 11 14 18 19 22 27 39 40

In cells K1 - T1, I would like to appear the numbers from 1-10 (so in this example: 1 in cell K1, and 7 in cell L1, cells M1 to T1 remain blank).

In cells U1 - AD1, I would like to appear the numbers form 11-20 (so in this example: 11 in cell U1, 14 in cell V1, 18 in cell W1 and 19 in cell X1, cells Y1 to AD1 remain blank).

Can I do this with a formula? Thank you very much in advance.

    United States
    Member #79057
    August 26, 2009
    70 Posts
    Posted: January 26, 2011, 11:04 am - IP Logged

    There's probably an easier way,but I don't know it.  One way I do know of doing it is to add columns at the end - for example to do 1 in cell K1:


    In cell AE1 -            =Countif(A1:J1,1)

    This looks at the A1 - J1 array and counts the number of times 1 shows up. 


    In cells K1 -             =If(AE1>0,1,0)

    If one does appear in the array then it puts the value of 1 in K1 - otherwise it puts in zero.


    For a second example I'll do 7

    In cell AK1 -                    =countif(a1:j1,7)

    In cell Q1 -                     =If(AK1>0,7,0)


    I'm sure there's an easier way, but I hope this helps.

      Member #73987
      April 30, 2009
      33 Posts
      Posted: January 27, 2011, 2:26 am - IP Logged

      Thank you for the effort ACPutz, I appreciate it, but it's not what I'm looking for.


      The lowest number from the group 1 to 10, whatever it is, should appear in cell K1. Not easy :-S


      Anyone else?

        time*treat's avatar - radar

        United States
        Member #13130
        March 30, 2005
        2171 Posts
        Posted: January 27, 2011, 4:31 am - IP Logged

        (formula in cell K1) =IF(A1>=0,IF(A1<=10,A1,""),"")
        (formula in cell L1) =IF(B1>=0,IF(B1<=10,B1,""),"")
        (formula in cell M1) =IF(C1>=0,IF(C1<=10,C1,""),"")

        (formula in cell U1) =IF(A1>10,IF(A1<=20,A1,""),"")
        (formula in cell V1) =IF(B1>10,IF(B1<=20,B1,""),"")
        (formula in cell W1) =IF(C1>10,IF(C1<=20,C1,""),"")

        Put in the K1 and U1 cell formulas, you may be able to drag-and-drop.

        In neo-conned Amerika, bank robs you.
        Alcohol, Tobacco, and Firearms should be the name of a convenience store, not a govnoment agency.