Find and collate rows with repeated partial text in one column

[mpeterson]

I'm working on analysing one data worksheet which has over 6200 rows (no blanks) within 7 columns (A:G).


ColD has the main data fields which are the focus of this question.


I need to find EVERY repeated THREE WORDS in all ColD cells, highlight them in red, and collate the entire data rows of those cells in groups separated with one blank row in another worksheet.


I have manually created the attached example, hoping it would illustrate my question properly.


I do appreciate any precious assistance with this question.


Many thanks in advance.

sample_data_example.xlsx

[大灰狼1976]

Hi mpeterson!
Please refer to the attachment.
The result is output at Sheet3.

[mpeterson]

Hi SuZhou,
I really don't know how to thank you for such masterpiece code you produced. It ran smoothly with no problem and yielded the desired results.
I published this question a while ago and over 100 expert read it, but no one was able to even suggest a solution, rather to produce it as you did. Thus, I do appreciate your time, effort and knowledge you used to code the solution.
Once again thank you very much, and I'm sure you do have a great value to add to this world SuZhou.
All the best.

M.

[大灰狼1976]

Because there are continuous spaces in many cells, so there will be deviations in starting position and length when marking color. It would be better if we could standardize the D column data.
In addition, the processing speed is slower when data are many. I will make a better sample today.

[大灰狼1976]

There is little difference in speed. because of it takes a lot of time to mark colors.

[mpeterson]

Thank you very much SuZhou for your modification. In fact, I noticed differences in text red highlight it in the first code, but said to myself I will do it manually. For you to perfect it in such way, is considered remarkable.

Well, I ran the new code on my sample text, and it ran PERFECT 100% as expected.

But, when I ran it on the real data, it halted on this line:

rng.Cells(i, 4).Characters(Start:=arr1((i - 1) * 3 + 1), Length:=arr1((i - 1) * 3 + 2)).Font.Color = vbRed

with runtime error '9' Subscript out of range. In my real ColD data there are cells with over 250 chars.

I also tried to normalize ColD data with the other new code, some cell were deleted with #VALUE! string instead of the original string. I realised they are those cells which are longer than 250 chars.

Can you please apply some modification to accommodate for longer text?

Please accept my deep gratitude.

M.

[大灰狼1976]

Can you send me a wrong form? Just the wrong part is enough. I will find out the problem.

[大灰狼1976]

Confirmed results:
1. Application.Trim() will make errors when it is over 255 characters long. But it can be implemented in other ways.
2. Characters(Start:=?, Length:=?).Font.Color when more than 255 characters long is no problem.
so I need 2.'s wrong texts.

[大灰狼1976]

Solution of Standardize D column data:

Private Sub CommandButton3_Click()
Dim i&
For i = 1 To 10    'It is assumed that there are no more than 10 consecutive spaces.
  Columns(4).Replace "  ", " "
Next i
For i = 1 To [d65536].End(3).Row
  If Left(Cells(i, 4), 1) = " " Then Cells(i, 4) = Mid(Cells(i, 4), 2)
  If Right(Cells(i, 4), 1) = " " Then Cells(i, 4) = Left(Cells(i, 4), Len(Cells(i, 4)) - 1)
Next i
End Sub

[mpeterson]

Dear SuZhou, IT WORKED, IT WORKED ..

In fact, it was my mistake. I copied 150 cells from real data in ColD and pasted it in your updated second file sample in ColD. I did not fill out other columns A-C and E-F with fake "data". This is why the code halted. As soon as I realised it, I filled those empty cells and ran your genius code again, and IT WORKED AS NEVER BEFORE.

I really don't know what to say, you left me speechless. But I still can say "Thank you very much my dear SuZhou, wishing for you all the best at present and future".

M.

[mpeterson]

May I ask for one more thing please ..
Is it possible to copy only the red text in ColD to ColG?
That's it .. no more questions .. : )

M.

[大灰狼1976]

Hi mpeterson! Don't mention it.
I'll settle it tomorrow.

[大灰狼1976]

Hi mpeterson!
I want to confirm output to which worksheet's G column.

[mpeterson]

Hi SuZhou,

Sure .. I think I typed G instead of H as ColG has data already in it. I'm attaching the same sample file with the desired results in ColH, which will be used in a more detailed analysis.
Thank you very much indeed SuZhou.

M.

[大灰狼1976]

Hi mpeterson!
Please refer to the attachment.
have a good weekend

[mpeterson]

Great .. really great SuZhou .. you really stunned me. I really wish for you all success that deserves your knowledge, experience and above all .. sincerety ..
Thank you very much my dear ..

M.

[mpeterson]

https://www.excelforum.com/excel-pro...ne-column.html