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

Array of Numbers Excel 10

Topic closed. 11 replies. Last post 2 years ago by TasBob.

Page 1 of 1
51
PrintE-mailLink
TasBob's avatar - 4NJ9EUA
Bowling Green ,Florida
United States
Member #108735
March 30, 2011
4140 Posts
Offline
Posted: May 20, 2015, 8:31 am - IP Logged

Is there a formula  to take an Array of 18 to20 numbers                            504537675015894290

And have them come out like this and NOT sort them from low to high      5043761892     (All 10 numbers)

Procrastination Stops Progress ⇒Σ

    SergeM's avatar - slow icon.png
    Economy class
    Belgium
    Member #123700
    February 27, 2012
    4035 Posts
    Offline
    Posted: May 20, 2015, 8:50 am - IP Logged

    This might help: functions.

      CARBOB's avatar - FL LOTTERY_LOGO.png
      ORLANDO, FLORIDA
      United States
      Member #4924
      June 3, 2004
      5968 Posts
      Offline
      Posted: May 20, 2015, 8:51 am - IP Logged

      Is there a formula  to take an Array of 18 to20 numbers                            504537675015894290

      And have them come out like this and NOT sort them from low to high      5043761892     (All 10 numbers)

      May I suggest MrExcel.

        Avatar

        United States
        Member #41846
        June 23, 2006
        460 Posts
        Offline
        Posted: May 20, 2015, 8:10 pm - IP Logged

        tasbob  see code below  I named this function  strip_duplicates  you can rename it if you want  once loaded  click on fx or functions  look under user defined and select  it will ask for your input that is whatever cell you numbers are in.

         

        Function strip_duplicates(str)
        Dim n(10)
        a = Len(str)
        For q = 1 To a
        b = Val(Mid(str, q, 1))
        n(b) = n(b) + 1
        If n(b) > 1 Then GoTo skip
        c = c & b
        skip:
        Next q
        strip_duplicates = c
        End Function

          TasBob's avatar - 4NJ9EUA
          Bowling Green ,Florida
          United States
          Member #108735
          March 30, 2011
          4140 Posts
          Offline
          Posted: May 21, 2015, 6:37 am - IP Logged

          This might help: functions.

          Than You for info

          Procrastination Stops Progress ⇒Σ

            TasBob's avatar - 4NJ9EUA
            Bowling Green ,Florida
            United States
            Member #108735
            March 30, 2011
            4140 Posts
            Offline
            Posted: May 21, 2015, 6:38 am - IP Logged

            May I suggest MrExcel.

            Tks CarBob

            Procrastination Stops Progress ⇒Σ

              TasBob's avatar - 4NJ9EUA
              Bowling Green ,Florida
              United States
              Member #108735
              March 30, 2011
              4140 Posts
              Offline
              Posted: May 21, 2015, 6:40 am - IP Logged

              tasbob  see code below  I named this function  strip_duplicates  you can rename it if you want  once loaded  click on fx or functions  look under user defined and select  it will ask for your input that is whatever cell you numbers are in.

               

              Function strip_duplicates(str)
              Dim n(10)
              a = Len(str)
              For q = 1 To a
              b = Val(Mid(str, q, 1))
              n(b) = n(b) + 1
              If n(b) > 1 Then GoTo skip
              c = c & b
              skip:
              Next q
              strip_duplicates = c
              End Function

              Tks phileight

              I new at Excel could not get that working..You tube it no Help..

                                                        Thank You

              Procrastination Stops Progress ⇒Σ

                Avatar

                United States
                Member #41846
                June 23, 2006
                460 Posts
                Offline
                Posted: May 21, 2015, 11:58 am - IP Logged

                I'm not sure where you are having problems. the excel file in the attached link has this function loaded so you can see how it works.

                If you have more specific questions as to how to load or use it  let me know. I still remember when I first started using this stuff just how confusing it all was.  its just a lot of small steps.

                 

                https://www.dropbox.com/s/ojcre5zrbceyc4a/function.xlsm?dl=0

                  TasBob's avatar - 4NJ9EUA
                  Bowling Green ,Florida
                  United States
                  Member #108735
                  March 30, 2011
                  4140 Posts
                  Offline
                  Posted: May 21, 2015, 1:13 pm - IP Logged

                  I'm not sure where you are having problems. the excel file in the attached link has this function loaded so you can see how it works.

                  If you have more specific questions as to how to load or use it  let me know. I still remember when I first started using this stuff just how confusing it all was.  its just a lot of small steps.

                   

                  https://www.dropbox.com/s/ojcre5zrbceyc4a/function.xlsm?dl=0

                  Thank You So Much It's up and going.

                  Procrastination Stops Progress ⇒Σ

                    SergeM's avatar - slow icon.png
                    Economy class
                    Belgium
                    Member #123700
                    February 27, 2012
                    4035 Posts
                    Offline
                    Posted: May 22, 2015, 5:30 pm - IP Logged

                    I rewrote the function for myself.

                    za"/&1282zA981 _
                    za"/&128A9 _

                    I also wrote string to array.

                    12AB34     
                    12AB34
                      winsumloosesum's avatar - Lottery-060.jpg
                      Pennsylvania
                      United States
                      Member #2218
                      September 1, 2003
                      5396 Posts
                      Offline
                      Posted: May 22, 2015, 7:53 pm - IP Logged
                        TasBob's avatar - 4NJ9EUA
                        Bowling Green ,Florida
                        United States
                        Member #108735
                        March 30, 2011
                        4140 Posts
                        Offline
                        Posted: May 23, 2015, 7:07 pm - IP Logged

                        ok thank you, and all that help...Also tks for sharing your program's  with others...

                        Procrastination Stops Progress ⇒Σ