Results 1 to 1 of 1

Thread: Find multiple instances of a value and Return various data when the value is found

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi All,

    I am looking for a VBA 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 values in my table, starting with the oldest data in row 4, and working across each column and then down the rows. Once these values are found per the VBA process they should be returned to Sheet “Data”.


    Sample Layout of table "Data" contained in Book7.xlsm - attached

    Sheet Rawdata – contains data in tabular format: “Data”

    Sheet DataResults - contains expected results using VBA process

    Sheet Data – template for the actual VBA processed results.



    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 “Data” 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” (zero).


    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.

    The row numbers where the criteria is found should be returned (per Step4), but using my table’s relative row reference in column “A”. Thus, the relative row numbers to be returned for criteria 0 are:

    Actual Cell Ref. Return table Relative Row Ref (in Col. “A”)

    I4 1

    K4 1

    T4 1

    AB4 1

    I9 6

    V14 11



    Summary of where criteria 0 data to be pasted:
    For criteria 0 – Step2 return the column number to sheet “Data” row 3 – starting in column “C”
    For criteria 0 – Step3 return criteria to sheet “Data” row 6 – starting in column “D”

    For criteria 0 – Step4 return criteria relative row ref. to 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.

    Attached Files Attached Files
    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
  •