Welcome Guest
Log In | Register )
You last visited January 20, 2017, 8:51 am
All times shown are
Eastern Time (GMT-5:00)

Xls formula hepl again

Topic closed. 9 replies. Last post 7 years ago by frenchie.

Page 1 of 1
PrintE-mailLink
frenchie's avatar - Lottery-041.jpg
Los Angeles
United States
Member #75410
June 2, 2009
489 Posts
Offline
Posted: April 11, 2010, 12:16 am - IP Logged

Hi everyone I need help also for this one wich is almost like my previous post but slitly different, I need to arrange the numbers a different way :         
           
 Here are the datas :         
           
 In Row 1 :   A1: 1  ;  B1: 2  ;  C1: 3  ;  D1: 4  ;  E1: 5  ;   F1: 6  ;  G1: 7  ;  H1: 8  ;  I1: 9  ;  J1: 10  ;   ETC…         
 In Row 2 :   A2: 0  ;  B2: 2  ;  C2: 4  ;  D2: 4  ;  E2: 6  ;   F2: 5  ;  G2: 6  ;  H2: 1  ;  I2: 3  ;  J2:  0  ;   ETC…         
           
 My question is can a formula make the duplicate of Row 2 become one, so it would look like this :         
 A2: 0  B2: 1  C2: 2  D2: 3  E2: 4  F2: 5  G2: 6         
           
 and the numbers in Row 1 would be arrange in columns according of the datas of Row 2.           
 It would look like this :         
     


ABCDEFGHIJ
112345678910
20244656130
30123456


41829365


510


4
7


6









7









8









     
     
         Thank you so much for your help everyone.

    Avatar

    United States
    Member #41846
    June 23, 2006
    460 Posts
    Offline
    Posted: April 11, 2010, 8:35 am - IP Logged

    Hi everyone I need help also for this one wich is almost like my previous post but slitly different, I need to arrange the numbers a different way :         
               
     Here are the datas :         
               
     In Row 1 :   A1: 1  ;  B1: 2  ;  C1: 3  ;  D1: 4  ;  E1: 5  ;   F1: 6  ;  G1: 7  ;  H1: 8  ;  I1: 9  ;  J1: 10  ;   ETC…         
     In Row 2 :   A2: 0  ;  B2: 2  ;  C2: 4  ;  D2: 4  ;  E2: 6  ;   F2: 5  ;  G2: 6  ;  H2: 1  ;  I2: 3  ;  J2:  0  ;   ETC…         
               
     My question is can a formula make the duplicate of Row 2 become one, so it would look like this :         
     A2: 0  B2: 1  C2: 2  D2: 3  E2: 4  F2: 5  G2: 6         
               
     and the numbers in Row 1 would be arrange in columns according of the datas of Row 2.           
     It would look like this :         
         


    ABCDEFGHIJ
    112345678910
    20244656130
    30123456


    41829365


    510


    4
    7


    6









    7









    8









         
         
             Thank you so much for your help everyone.

    Frenchie

    for this and you other question I don't understand what you are trying to do.  your reference don't match  the display. row 1 and 2 is fixed data. what is row 3, row 4 ,row 5. if you can explain what each of these represent then maybe we can help.

    from your previous post conditional formatting is limited. if you want to learn to work with (program) macros fewer limits exist

      frenchie's avatar - Lottery-041.jpg
      Los Angeles
      United States
      Member #75410
      June 2, 2009
      489 Posts
      Offline
      Posted: April 12, 2010, 12:41 am - IP Logged

      Hi phileight,

      Thank you to answer my post I really need help for this one.

      I will explain better, I hope !!! ( sorry I'm French and my English is not as great as yours !!!). 
       
           Here is a little bit modified chart to see better.


      ABCDEFGHIJ
      112345678910
      20244656130
      3









      4









      50123456


      6









      71829365


      810


      4
      7


            What I need actually is that the data in blue to be transfer and give me the result of the one in orange.   
       
      I need a formula that will take the data in (Row2) and eliminate all duplicates witch will result like in 
      (Row 5) : 0 1 2 3 4 5 6. 
       
      And that the numbers they were connected to per column in (Row1) follow them in (Row 7 and 8). 
       
      Explanation :    In (Row 2 ) the 0 was with 1 (A1) and 10 (J1).
                               In (Row 2 ) the 1 was with 8 (H1).
                               In (Row 2 ) the 2 was with 2 (B1).
                               In (Row 2 ) the 3 was with 9 (I1).
                               In (Row 2 ) the 4 was with 3 (C1) and 4 (D1).
                               In (Row 2 ) the 5 was with 6 (F1).
                               In (Row 2 ) the 6 was with 5 (E1) and 7 (G1).
       
      I Hope you understant better ?

       The previous post was a little bit different here it is :


      ABCDEFGHIJ
      112345678910
      20244656130
      3









      4









      50012344566
      6









      611082934657

      I just need the (Row 2) to be arrange in a numeric order and the numbers in (Row 1) following them.

      Witch would give me the result in orange.

       

         Thank you for your time phileight and everyone.

        Avatar

        United States
        Member #41846
        June 23, 2006
        460 Posts
        Offline
        Posted: April 13, 2010, 6:56 am - IP Logged

        Frenchie

        i can't write that formula in excel. using a macro (program) its fairly easy. I assume you are not used to using macros.

        open a new worksheet in excel.  if you don't like this just don't save the file and its gone.

        with worksheet open press alt f8 name the file duplicates and click on create then paste the following file on the line after    sub duplicates()    and before      end sub

        should look like this

         

        Sub duplicates()
        Dim dn(10)

        For x = 1 To 10
        a = Cells(2, x)
        dn(a) = 1 ' only matching #'s are marked and automatically sorted
        Next x

        t = 1
        For x = 1 To 10
        If dn(x) = 1 Then Cells(5, t) = x: t = t + 1
        Next x

        For x = 1 To t - 1 ' t lets me know how many to check
        Ln = 7
        a = Cells(5, x) ' a=# i'm looking for
        For y = 1 To 10 ' where i'm going to look
        b = Cells(2, y)
        If a = b Then Cells(Ln, x) = y: Ln = Ln + 1
        Next y
        Next x

          End Sub

         

         

        if your name and end sub appear twice then delete one set. click the x to close the window.enter your info on line 1 and 2

        press alt f8, highlite the file name if it's not alredy marked then click run.  change your data and repeat this line. there is a much easier way to start this file if you want to use it.

          frenchie's avatar - Lottery-041.jpg
          Los Angeles
          United States
          Member #75410
          June 2, 2009
          489 Posts
          Offline
          Posted: April 13, 2010, 11:51 pm - IP Logged

          Hi phileight ,

               Thank you  phileight for your answer, I'm gone try it and hope it's gone work with the rest of my datas, and yes I don't know nothing about macro that why I like to work with formulas for me it's easier,Thanks again.

            Avatar

            United States
            Member #41846
            June 23, 2006
            460 Posts
            Offline
            Posted: April 14, 2010, 6:32 pm - IP Logged

            Frenchie

            as written this will only deal with data on line 2.  It is easily(for someone familiar with it)adaptable to other data.

             if you learn some basic commands (program steps) its much easier than excels nested if/ and structure.

            good luck

              frenchie's avatar - Lottery-041.jpg
              Los Angeles
              United States
              Member #75410
              June 2, 2009
              489 Posts
              Offline
              Posted: April 15, 2010, 9:55 pm - IP Logged

              HI phileight,

              Thanks, It worked but the reason why I was asking for a formula is so I could change the formula around for different rows or columns to meet my needs.

              I have data on more rows which I would copy down the formula and get my results for multiple reguests.

              so if you can help on that or someone else, please do.

              Thank you.

                Avatar

                United States
                Member #41846
                June 23, 2006
                460 Posts
                Offline
                Posted: April 16, 2010, 6:38 am - IP Logged

                sounds like what you need then is a user defined function. once it is set up you use it like any other function in excel.  you still write the function using vba. may be able to get to that tonight

                  Avatar

                  United States
                  Member #41846
                  June 23, 2006
                  460 Posts
                  Offline
                  Posted: April 19, 2010, 7:47 pm - IP Logged

                  Sorry Frenchie. I seem to be at a dead end. the user defined function will only modify the cell it is in. you need to do more than that.

                  my only option would be to put the line and col of your data in cells on line1 col 1 and 2  then line and col of where you want the output displayed in cells 3,and 4. 

                  I don't believe you can write a formula for what you want.

                    frenchie's avatar - Lottery-041.jpg
                    Los Angeles
                    United States
                    Member #75410
                    June 2, 2009
                    489 Posts
                    Offline
                    Posted: April 20, 2010, 6:47 pm - IP Logged

                    Thanks phileight.