Results 1 to 5 of 5

Thread: Find multiple instances of a numeric value and Return row number where value is found

  1. #1

    Find multiple instances of a numeric value and Return row number where value is found



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All,

    I am looking for a formula based solution - using Excel 2007 for Windows.

    My data is numeric, in a tabular format, spanning many columns and rows – the number of rows will continue to increase. For that reason, I have created a dynamic named range called “Data” that refers to my table of data.

    The layout and structure of my table “Data”:
    Numeric labels in row 3, spanning the width of my table, “Data” starts in column “G” row 4 and ends in column “BK”. Column “A” contains a numeric reference that can be used as relative row numbers for my table (row 4 = table “Data” row 1). The cells within table “Data” are populated with a formula which returns either a numeric value or empty text (“”) showing a blank cell.

    Scenario:
    I would like to find many different duplicate criteria (values) in my table, starting with the oldest data in row 4, and working across each column and then down the rows. There may be multiple instances of the criteria in the same row. Once these criteria are found their table row number found in column “A” (not the worksheet row number) for each instance should be returned to separate cells, down a single column - using dynamic name “Data” in a formula based solution with an input cell for the criteria. In case a criterion is not found please provide an error-trap returning empty text (“”).

    Sample Layout of table "Data" contained in Book8.xls - attached


    Expected Results:
    Looking for all criteria of 0 (zero) in table “Data” –
    Found in column “I”, row 4; return table row number = 1
    Found in column “K”, row 4; return table row number = 1
    Found in column “T”, row 4; return table row number = 1
    Found in column “AB”, row 4; return table row number = 1
    Found in column “I”, row 9; return table row number = 6
    Found in column “V”, row 14; return table row number = 11

    Hope you can help.

    Attached Files Attached Files
    Thank you,
    Sam

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,436
    Articles
    0
    Excel Version
    Excel 2016
    You need to also create a Dynamic Named Range for column A so that you can grow that column too...

    After you've done that (it should be same rows as the DATA named range)... then try:

    =INDEX(colA,SMALL(IF(Data=0,ROW(Data)-MIN(ROW(Data))+1),ROWS($A$1:$A1)))

    where colA is dynamic named range name for column A corresponding to DATA range.

    confirmed with CTRL+SHIFT+ENTER and copied down.


  3. #3
    That worked brilliantly! Thank you for your time and assistance.
    Thank you,
    Sam

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,436
    Articles
    0
    Excel Version
    Excel 2016
    You're welcome.

    I did forget to mention that you could wrap an IFERROR() around the formula to "hide" errors as you copy down, so that your result range can be a bit dynamic too.


  5. #5
    Will do.

    Quote Originally Posted by NBVC View Post
    I did forget to mention that you could wrap an IFERROR() around the formula to "hide" errors as you copy down, so that your result range can be a bit dynamic too.
    Thank you,
    Sam

Posting Permissions

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