Finding Repeat Range

[joewagner501]

I have wracked my brain and Google searches for several days trying to find an answer to my dilemma. Hopefully someone here can help with it.

I have a range of numbers within a large set of numbers that will repeat. For example, in the range below, the first nine numbers are a range that repeats afterward. This is a simple example because sometimes the repeat range may be 50 or more in length.

Is there a formula or macro that will scan a set of numbers to determine where the repeat range begins? In the example below, the ninth number is the last of the range that repeats and is therefore considered the beginning of the repeat range.

14.00
13.00
14.00
13.00
13.00
14.00
13.00
14.00
13.00
14.00
13.00
14.00
13.00
13.00
14.00
13.00
14.00
13.00
14.00
13.00
14.00
13.00
13.00
14.00
13.00
14.00
13.00

<colgroup><col width="64" style="width:48pt"></colgroup><tbody>
</tbody>

Any help would be greatly appreciated.

 

[UWMmakow]

Will there always be at least one repeated range? Are there any constraints on the size of the range, as in, it will always be at least 3 rows or never more than 100? And to clarify, all you want to return is the first row of the first repeated set? What's the range of the top cell (A1?)

 

[joewagner501]

Thank you for your reply.

There will always be at least one repeated range.

There is no constraint on the size of the range. It could have 2 rows or over 1,000.

The row needed would be the last row of the first repeated range.

Here is a more comprehensive list of the data. The min/max is the spread of numbers within the range. The range is the number of numbers between the min/max. The first row is ignored because it will never coincide with the rest of the data.

Min	Max	Range
0.01	0.06	5.00
0.07	0.2	14.00
0.21	0.33	13.00
0.34	0.47	14.00
0.48	0.6	13.00
0.61	0.73	13.00
0.74	0.87	14.00
0.88	1	13.00
1.01	1.14	14.00
1.15	1.27	13.00
1.28	1.41	14.00
1.42	1.54	13.00
1.55	1.68	14.00
1.69	1.81	13.00
1.82	1.94	13.00
1.95	2.08	14.00
2.09	2.21	13.00
2.22	2.35	14.00
2.36	2.48	13.00

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[UWMmakow]

If that's a real data set, it looks like the repeating range doesn't start until row 2? I don't know if I'll be able to help you with this. Frankly, the only thing I could even think of would be far too complex to even bother spending the time putting together and debugging. I think you need someone with more experience or a more keen thought process than I.

 

[joewagner501]

I appreciate you trying.

 

[Marcelo Branco]

The repeated ranges always begin in row 3?

M.

 

[joewagner501]

It will always begin in row 2. The file linked below is an example showing the Min, Max and Range columns. The Range column is the one that is used to determine the repeat range. I have highlighted the repeat range in alternating colors to make it easier to see in the example. The first row of the repeat range is row 3 with the last being row 11. Row 11 is considered the where the repeat range begins because every set of nine rows afterward is exactly like those nine.

I hope this helps explains better. Currently this is done manually and since some of these are thousands of rows long, it takes a long time to find the repeat ranges.


filehosting.org | Download | MrExcel Example.xlsx

 

[Marcelo Branco]

It will always begin in row 2. ....
The first row of the repeat range is row 3 with the last being row 11.

I'm confused...
I was asking if the first row of the repeated range is always row 3. Or the repeated range can begin in row 4, 5,...etc?
Please, clarify.

M.
ps: i have not downloaded your file because an email address was required (???)

 

[mikerickson]

I'm confused on another issue.
In the OP, 14,13,14,13,13,...I see the first two rows and then their repeat.
If I'm looking for the shortest length that is repeated, then the answer is 2.

(or 1 if the 13,13 counts as a repeating sequence that starts on row 4)

 

[Marcelo Branco]

In other words, if the beginning (first cell) of the repeated range is fixed (always row 3), i believe that the solution will not be so complicated. But if the first cell of the repeated range is variable (any row) things get much more difficult ...

BTW i'm assuming the code must find the longest length that is repeated. Am i right?

M.