Excel/Find multiple instances of a numeric value and Return various data when the value is found
I am looking for a VBA solution - using Excel 2007 for Windows. My email address is Sam-reply at mail dot com.
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 “Results” that refers to my table of data.
The layout and structure of my table “Results”:
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 “Results” row 1). The cells within table “Results” are populated with a formula which returns either a numeric value or empty text (“”) showing a blank cell.
I would like to find many different duplicate values in my table, starting with the oldest data in row 4, and working across each column and then down the rows.
What the VBA process should do:
Cycle through all criteria range input - for example, 0-50
Allow me to input a criterion range, for example, from zero to fifty (0-50). I would also like the criterion range to be flexible, so that I can input a different sequential criterion range (0-60, 0-10, 1-10). Run the process from the beginning, from the first row in my named range “Results” through each and every column, row by row, looking for each criterion within the range.
NB: There will be occasions where the criteria will not be found in a row (error trap may be needed).
For the sake of brevity, I will focus on just one criterion – 0.
Find and return criteria 0’s column number
Starting with criteria 0 (zero); if any criteria 0’s are found return the relative column number (relative to my table starting in column “G”) to sheet “Data” row 3 – starting in column “C”, then (every 3rd cell) column “F”, column “I”, and so on; until all the criteria 0 column numbers are returned.
Find and return the criteria 0
Copy and paste any criteria 0’s found to sheet “Data” row 6 – starting in column “D”, then (every 3rd cell) column “G”, column “J”, and so on; until all criteria 0’s copied and pasted.
Find and return criteria 0’s row number
Return the relative row number where criteria 0 is found - copy the reference numbers listed in column “A” on the row where criteria 0 is found and paste to sheet “Data” row 7, starting in column “C”, then (every 3rd cell) column “F”, column “I”, and so on; until all the relative row numbers where criteria 0 are found are returned/pasted.
Summary of where criteria 0 data to be pasted:
For criteria 0 – Step3 return the column number to sheet “Data” row 3 – starting in column “C”
For criteria 0 – Step4 sheet “Data” row 6 – starting in column “D”
For criteria 0 – Step5 sheet “Data” row 7 – starting in column “C”
You may see a more practical and efficient way to run the VBA process.
Hope you can help.
I can help a lot more easily if I had an actual workbook which illustrates what you want do to.
And it'd also help if you created an "answer" page -- what you'd like the results of the macro to show.
Email me the sample at email@example.com
use subject of "AllExpertsQ"