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.
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.