+ Reply to Thread
Results 1 to 7 of 7

In which row is a number?

  1. #1
    Registered User
    Join Date
    09-08-2014
    Location
    New York
    MS-Off Ver
    2010
    Posts
    12

    In which row is a number?

    Hello,

    I have tried to solve this by myself but I need some help.
    I have a table with numbers from 1 to 9, exactly the same like in this document: In which row is a number.xlsx

    I need to know in which row every number appears. For example, it is easy to find a ROW of a number that appears only once, but there are
    some numbers that can appear twice or more and for that numbers I need only lower row to be shown.
    To clarify, number 6 appears in first and fourth row, but I need only first row as a result.
    There is also number 7 that doesn't appears at all, as a result I would like to show 0 (zero).


    P.S. The dashes in the first table should not be corrected or deleted, they must stay there as they are.

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: In which row is a number?

    Using your posted workbook, this ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER) begins the row list for each digit from 1 throufh 9
    Please Login or Register  to view this content.
    Copy that formula across through Q2

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: In which row is a number?

    With helpcolumns and -cells.

    the result is in the green cells.

    see the attached file.
    Attached Files Attached Files
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  4. #4
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: In which row is a number?

    This is my approach. I inserted a row so that the numbers being searched were in a row by themselves (row 2 from I to Q):

    Enter in I3 and copy across. This is an Array Formula so is entered with Ctrl + Shift + Enter

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  5. #5
    Registered User
    Join Date
    09-08-2014
    Location
    New York
    MS-Off Ver
    2010
    Posts
    12

    Re: In which row is a number?

    Quote Originally Posted by Ron Coderre View Post
    Using your posted workbook, this ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (instead of just pressing ENTER) begins the row list for each digit from 1 throufh 9
    Please Login or Register  to view this content.
    Copy that formula across through Q2

    Is that something you can work with?

    Yes, you helped me a lot!

  6. #6
    Registered User
    Join Date
    09-08-2014
    Location
    New York
    MS-Off Ver
    2010
    Posts
    12

    Re: In which row is a number?

    Quote Originally Posted by oeldere View Post
    With helpcolumns and -cells.

    the result is in the green cells.

    see the attached file.

    Quote Originally Posted by newdoverman View Post
    This is my approach. I inserted a row so that the numbers being searched were in a row by themselves (row 2 from I to Q):

    Enter in I3 and copy across. This is an Array Formula so is entered with Ctrl + Shift + Enter

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Thank you guys!

  7. #7
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: In which row is a number?

    Thank you for the feedback. You now have a few solutions from which to choose....nice position to be in

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 10
    Last Post: 10-30-2013, 07:29 PM
  2. Validation Number number number + letter depending on numbers
    By Adamitus in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-31-2012, 04:28 AM
  3. number in cell is number to add number of rows wit copy some contex
    By nicollab in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-31-2012, 08:09 AM
  4. Lookup largest number in a column treating negative number as postive number
    By xWiZardx in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-17-2010, 08:09 AM
  5. Replies: 7
    Last Post: 12-18-2008, 07:34 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1