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

need help with excel

Topic closed. 7 replies. Last post 8 years ago by phileight.

Page 1 of 1
PrintE-mailLink
Avatar
Mount Morris
United States
Member #46750
September 27, 2006
139 Posts
Offline
Posted: April 27, 2009, 6:55 pm - IP Logged

My problem is this I got a xl page it has  400 numbers in it all in diffront cells A1-A150 -- E1--E150

now I want to find only the numbers that are in all  the cells STRAIGHT only that show  more than 1 Times

like 225 cell a5    225 e100   cell b90 225 hope this can be done in some easy way.


    United States
    Member #48180
    December 14, 2006
    4 Posts
    Offline
    Posted: April 27, 2009, 7:36 pm - IP Logged

    Place your cursor any where in the cells the hold down CTRL key and press F (CTRL+F) to launch the Find window. Place the number you are searching for in the Find What window. Set options to value ,then press find all. If you like you can highlight all the found values by selecting the first cell listed, hold down the SHIFT key and select the last listed (all will be selected). Select a fill color to easily see all instances of your straigt hit.

     

    Hope this helps

      diamondpalace's avatar - Untitled 2.jpg
      Dallas, TX
      United States
      Member #60284
      April 12, 2008
      3856 Posts
      Offline
      Posted: April 27, 2009, 7:46 pm - IP Logged

      Try the Straight Eliminator sheet I have created. It also count how many str are within the count range. Would need to change the black color into white to see the counts, do this after you have paste in the numbers on white column under the "Clear All" button. Pink box next to the str combos indicating 0 hit.

        Avatar
        Mount Morris
        United States
        Member #46750
        September 27, 2006
        139 Posts
        Offline
        Posted: April 27, 2009, 8:32 pm - IP Logged

        See thats what I don't want to do is tye in ever number to  look for. Just say you had 500 numbers in cells I want to find all the numbers it got in it that are exact match or dupicates of them that are in cell.

        Thank you

          Avatar
          Mount Morris
          United States
          Member #46750
          September 27, 2006
          139 Posts
          Offline
          Posted: April 27, 2009, 8:43 pm - IP Logged

          I have a problem my numbers are like this 254   not  2-4-5 and it will only show you 1 st number

          I need something that can find duplicates in all  my cells 150 of them.

            i  want only the numbers that have exact match in them  235 235  235  235   235 

          what i have some thing that produces 150 numbers say 20 of them  duplicates and thats the ones I want to look at.

          Thanks for your time

            Avatar

            United States
            Member #41846
            June 23, 2006
            460 Posts
            Offline
            Posted: April 27, 2009, 8:51 pm - IP Logged

            sounds like you need a vba program.

             copy your file to a new spreadsheet.  copy the  code below.  open your new spreadsheet.

            press alt f11 then paste the code. close window.

            put your lowest # in row 2 col G  your highest in row 2 col H.

            press alt f8 run macro "check". 

             if you like the results you can repeat the steps above in your original spreadsheet. if not delete the new spreadsheet

            p8

             

            Sub check()

            Dim m(160, 8)' slightly larger than your data file
            Row = 2
            Cells(1, 7) = "First": Cells(1, 8) = "Last" ' headings
            Cells(1, 10) = "Test Num"
            Cells(1, 11) = "col": Cells(1, 12) = "row"
            Cells(1, 13) = "col": Cells(1, 14) = "row"
            Cells(1, 15) = "etc"

            first = Cells(2, 7): last = Cells(2, 8)

            For x = first To last ' first to last number to test
            GoSub clear
            For Z = 1 To 150 ' column to test
            For y = 1 To 5 ' col a thru e

            a = Cells(Z, y) ' number to be tested
            If a = x Then m(Z, y) = n: n = n + 1 ' if it matches store count in this matrix position
            Next y
            Next Z
            If n > 2 Then GoSub display
            Next x

             

            Exit Sub

             


            clear: ' clear results last number under test
            For j = 1 To 150
            For k = 1 To 5
            m(j, k) = 0
            Next k
            Next j
            n = 1
            Return

            display:

            nx = 10 '  col i as start display point
            Cells(Row, nx) = x: nx = nx + 1 '  number under test
            For Z = 1 To 150
            For y = 1 To 5
            If m(Z, y) > 0 Then Cells(Row, nx) = y: nx = nx + 1: Cells(Row, nx) = Z: nx = nx + 1
            Next y
            Next Z
            Row = Row + 1
            Return


            End Sub

              Avatar
              Mount Morris
              United States
              Member #46750
              September 27, 2006
              139 Posts
              Offline
              Posted: April 28, 2009, 9:22 am - IP Logged

              I done the first part copy my file in new sheet done that ok

              then I pressed alt f11 and it comes up with gray window I dont see a place to paste in your formula. Would it be possible for you to do it for me and send sheet email?

              If so numbers in my sheet would be 80 sets say A1- A80   B1- B80  C1 -C80  D1- D80  E1-E80 - F1-F80

              NOW THESE CELLS HAVE MY 3 DIGIT NUMBERS TOTAL OF 480

               tHANKS HILLBILLY

                Avatar

                United States
                Member #41846
                June 23, 2006
                460 Posts
                Offline
                Posted: April 28, 2009, 12:12 pm - IP Logged

                I done the first part copy my file in new sheet done that ok

                then I pressed alt f11 and it comes up with gray window I dont see a place to paste in your formula. Would it be possible for you to do it for me and send sheet email?

                If so numbers in my sheet would be 80 sets say A1- A80   B1- B80  C1 -C80  D1- D80  E1-E80 - F1-F80

                NOW THESE CELLS HAVE MY 3 DIGIT NUMBERS TOTAL OF 480

                 tHANKS HILLBILLY

                Hillbilly

                 

                The problem with memory it tends to forget minor details.

                open your spreadsheet. alt F11. then select tools. then macro.  create a macro. I used check as macro name but you can use any name .

                you should now see your macro name followed by (),  a blank line, then end sub  paste the file starting at the blank line.

                delete one of the macro names and one of the end sub statements. if you used check as the macro name then the file should look like my earlier post.

                 

                VBA is fairly easy once you get familiar with it. it is intimidating to get started, but worth the effort.

                let me know if you are still having problems with this.

                 

                p8