Results 1 to 9 of 9

Thread: Find multiple instances of a numeric value and Return table row number & column label

  1. #1
    Acolyte Sam's Avatar
    Join Date
    Apr 2014
    Posts
    36
    Articles
    0
    Excel Version
    2007

    Find multiple instances of a numeric value and Return table row number & column label



    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 (not the worksheet row number) using dynamic name "Ref", together with their table column label (in row 3) for each instance should be returned to separate cells, down two separate columns (one for table row number and the other for column label) - 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 Book9.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; return table column label = 3
    Found in column “K”, row 4; return table row number = 1; return table column label = 5
    Found in column “T”, row 4; return table row number = 1; return table column label = 14
    Found in column “AB”, row 4; return table row number = 1; return table column label = 22
    Found in column “I”, row 9; return table row number = 6; return table column label = 3
    Found in column “V”, row 14; return table row number = 11; return table column label = 16

    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,489
    Articles
    0
    Excel Version
    Excel 2016
    Ok, I helped you on the table row number extractions yesterday here: http://www.excelguru.ca/forums/showt...value-is-found

    so same process there.

    Now you need to create a named range for the table column label.

    e.g. RefCol with formula: =OFFSET(Rawdata!$G$3,0,0,1,COUNTA(Rawdata!$4:$4)-1)

    Then, assuming you placed the table Row formula of yesterday is say in AE23, copied down

    Apply formula in AF23:

    =IFERROR(INDEX(RefCol,SMALL(IF(Data=0,IF(colA=AE23,COLUMN(Data)-MIN(COLUMN(Data))+1)),COUNTIF(AE$23:AE23,AE23))),"")

    where RefCol is named range for column headers, colA is named range for column A and Data is named range for the data in the table.

    confirmed with CTRL+SHIFT+ENTER copied down.


  3. #3
    Acolyte Sam's Avatar
    Join Date
    Apr 2014
    Posts
    36
    Articles
    0
    Excel Version
    2007
    Thank you very much for all your time and help. The formula on the whole does provide the required result - great! I removed the COUNTIF(AE$23:AE23,AE23) (because it returned only the first result all the way down the column) and replaced it with ROWS(AE$23:AE23) which then listed all the different, individual values down the column.

    Quote Originally Posted by NBVC View Post
    Apply formula in AF23:

    =IFERROR(INDEX(RefCol,SMALL(IF(Data=0,IF(colA=AE23,COLUMN(Data)-MIN(COLUMN(Data))+1)),COUNTIF(AE$23:AE23,AE23))),"")
    where RefCol is named range for column headers, colA is named range for column A and Data is named range for the data in the table.
    confirmed with CTRL+SHIFT+ENTER copied down.
    Thank you,
    Sam

  4. #4
    Acolyte Sam's Avatar
    Join Date
    Apr 2014
    Posts
    36
    Articles
    0
    Excel Version
    2007
    Unfortunately, the formula is only returning the column labels for the first table row of data that matches criteria 0; column labels 3,5,14,22 are returned. It is not returning any column labels for matched criteria 0 on table rows 6 or 11? It returns empty text for matched criteria 0 that are not on the first table row. Further assistance appreciated.

    Quote Originally Posted by Sam View Post
    Thank you very much for all your time and help. The formula on the whole does provide the required result - great! I removed the COUNTIF(AE$23:AE23,AE23) because it returned only the first result all the way down the column and replaced it with ROWS(AE$23:AE23) which then listed all the different, individual values down the column.
    Thank you,
    Sam

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    See attached.

    (Note: I used your Ref named Range for my original colA)
    Attached Files Attached Files


  6. #6
    Acolyte Sam's Avatar
    Join Date
    Apr 2014
    Posts
    36
    Articles
    0
    Excel Version
    2007
    I initially made a mistake with the column reference for the COUNTIF part of the formula using COUNTIF(AF$23:AF23,AF23) instead of
    COUNTIF(AE$23:AE23,AE23),and then went from bad to worst changing the COUNTIF to the ROWS function. Finally, I'm using the correct column reference for the countif part of the formula, and all the results are correctly returned. Thank you once again for your time and patience. Great Formula!
    Thank you,
    Sam

  7. #7
    Acolyte Sam's Avatar
    Join Date
    Apr 2014
    Posts
    36
    Articles
    0
    Excel Version
    2007
    Hi There,

    On 22 May 2014, Forum administrator NBVC provided me with a great formula solution that returned the table row number and column label where a specific criteria is found. I used criteria (zero) 0 in the sample file. Since then I've inserted more rows and added more values to the dynamic ranged table called "Data". Unfortunately, I cannot fathom what is causing the problem. The formulas that were created to provide the table row numbers and column labels where the criteria reside are no longer returning the correct co-ordinates. I would appreciate further assistance. I have attached the sample workbook.
    Attached Files Attached Files
    Thank you,
    Sam

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,489
    Articles
    0
    Excel Version
    Excel 2016
    Strange. When I go to Evaluate the formula, I notice that alot of the empty cells show as 0 instead of ""? Maybe it's how you put the data in the table (i.e partially entered, partially copy/pasted?).

    Anyway, try changing the formulas to these:

    =IFERROR(INDEX(Ref,SMALL(IF(Data<>"",IF(Data=0,ROW(Data)-MIN(ROW(Data))+1)),ROWS($A$1:$A1))),"")

    and

    =IFERROR(INDEX(RefCol,SMALL(IF(Data<>"",IF(Data=0,IF(Ref=AE35,COLUMN(Data)-MIN(COLUMN(Data))+1))),COUNTIF(AE$35:AE35,AE35))),"")

    both CSE confirmed, then copied down. That should fix it.

    P.S. by the way I am not forum administrator, I am a mere moderator :0


  9. #9
    Acolyte Sam's Avatar
    Join Date
    Apr 2014
    Posts
    36
    Articles
    0
    Excel Version
    2007
    That has definately sorted the problem. Thank you once again for your time and assistance. Brilliant!
    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
  •