You are here:

Excel/Find multiple instances of a numeric value and Return various data when the value is found

Advertisement


Question
Hi Bob,

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:

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

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

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

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

Thank you,
Sam

Answer
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 bobumlas@hotmail.com
use subject of "AllExpertsQ"
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Bob Umlas

Expertise

I`m a Microsoft Excel MVP (Most Valuable Professional) and have been since the inception of the program in 1995. I can answer every kind of Excel question except: API, Importing/exporting to other programs (powerpoint, word,...) Also check out my in-person training link at http://www.thumbtack.com/ny/new-york/excel-training/

Experience

Worked with MS Excel since version 0.99 (on the Mac!). Was contributing editor to Excellence Magazine, having written >300 articles. John Walkenbach said of me "I finally met someone who knows as much about Excel as I do."

Publications
Excellence, The Expert, Microsoft

Education/Credentials
BA in math, Hofstra University, 1965

Awards and Honors
MVP
Led sessions for the Convergence 2004-2006 seminar on Excel tips & tricks

©2016 About.com. All rights reserved.