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

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

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.

Ok, I helped you on the table row number extractions yesterday here: http://www.excelguru.ca/forums/show...ue-and-Return-row-number-where-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.

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.

NBVC said:

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.

Click to expand...

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.

Sam said:

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.

Click to expand...

See attached.

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

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!

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.

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

That has definately sorted the problem. Thank you once again for your time and assistance. Brilliant!