Hi, Jack-P-Winner!
Give a look at this file:
https://dl.dropboxusercontent.com/u/60558749/Look for U's and D's in a pattern within several rows - UD Look up (for Jack-P-Winner at chandoo.org).xlsm
Trying to repair my unforgivable error of haven't understood your workbook at once and more indeed having doubted from your assertions, and considering your concerns about VBA code and its placement, I built a workbook that contains basically 1 worksheet and 1 module:
- worksheet Parameters
- module Módulo1
It's supposed (as far as I tested) to let you fill the proper values in worksheet Parameters, then press the cyan button and an output worksheet with the matching rows would (should!) be created in the indicated place.
Parameters are:
- Input WB: workbook where the data to be analyzed is located (if left empty, this workbook is assumed)
- Input WS: worksheet of Input WB with the data (if left empty, 1st worksheet (for other workbooks) and 2nd worksheet (for this workbook) is used
- Title row: now no. of titles, if any (if left empty, no title is assumed)
- Column no.: "columns" (in your jargon) or "groups of six columns" (in Excel)
- Start row: if left empty, 1st of data range is assumed
- Start column: if left empty, 1st of data range is assumed
- Pattern valid: 2 characters indicating the accepted values (in the sample, DU)
- Pattern search: string to be matched (of length less or equal than column no.)
- Output WB: workbook where the output worksheet is going to be created/updated (if left empty, this workbook is assumed)
- Output WS: worksheet of output WB with the output data (if left empty, input WS plus "_Out" is assumed
It has all the validations that I thought could be included to guarantee the proper functioning, but maybe I missed something. Check this too and advise.
I tested it with many patterns from your worksheet Sheet1 (which is included in the workbook) and created the output in the same workbook. For me it works fine, but of course it's you who have the last word.
The idea is that the workbook (without) data worksheet may be used to take a worksheet from any workbook and create a result worksheet in any workbook, including the actual one: this workbook. I only checked the behavior with this workbook, so I don't know how it works with other workbooks, it should even create a new workbook if the chosen doesn't exist. While I test this tomorrow, you might want to play with the known proved "in-workbook" form, using your actual worksheet Sheet1 or any other one.
Give it a try and let me know how it goes.
Regards!
