Utter macro (and forum) noob seeks string fragment matching macro help

[Knownowt]

Hello,

I’m completely new to Excel macros and I’m hoping someone here might be kind enough to help me create a slightly fiddly one.

I have a worksheet with several thousands of rows and many of these are essentially duplicates I want to get rid of. Basically, one column contains a string (just text) and within this string may or may not be the word “Fixed”. I need to see if that string contains “Fixed” (and I don’t even know if looking inside a string is possible) and if it does, I need to compare it with the same cell in the row below. If that cell also contains “Fixed”, I want to delete that second entire row. I then want to repeat the test. In other words, I want to keep the first row that has “Fixed” in the target cell but delete its duplicates.

There’s a catch, natch. Once I’ve deleted all the duplicates of the first instance of "Fixed" (i.e. I come to a row that doesn’t match the row above when the strings are compared), I want to continue searching until I find another instance of the string. I then want to keep that instance but delete all its duplicates.

I think the logic goes like this:

1. Look in column C, row n (starting at row one)
2. If column C is empty, stop
3. Does cell contain the (sub)string “Fixed”?
4. If cell doesn’t contain “Fixed”, move to row n+1 and repeat steps 2 onwards
5. If cell does contain “Fixed”, repeat test on same cell in row below
If this cell also contains “Fixed”
Delete this entire row and move all remaining rows up (so no blank rows are created)
If it doesn’t, repeat step 4 and continue

This really isn't my forte and my investigations into macros have so far only hinted at parts of the solution. If your Kung Fu makes this task look trivially easy, I would be very grateful indeed for any assistance you can give me.

Thank you for reading.

A Knownowt

[bartelba]

Hi,

There is in the menu Data, Remove duplicates.

It just works without any programming.

Bas

[Knownowt]

Thank you for your swift reply. But wouldn't that result in my removing all duplicate values? I'm trying to remove duplicates in a range and then look for the beginning of the next range and delete all duplicates but the first, and so on.

So
F
F
F
F

Becomes

F

But

F
F
F
x
x
x
F
F
F

Becomes

F
x
x
x
F

And so on. Does the filtering mechanism you point to allow for this? (If it does, I'm afraid I've missed it but I'll be happy to roll up my sleeves if you're sure it does.)

Thanks anyway for your help.

[ARowbot]

What about this:

Please Login or Register  to view this content.

[Knownowt]

ARowbot, I'll try that right now and post back. Thank you very much indeed.

[walruseggman]

Another approach:

Please Login or Register  to view this content.

[Knownowt]

Walruseggman, coo-ca-choo! Your routine did the business. ARowbot, Excel crashed when I ran your routine (but that's more likely something to do with my laptop than your code). I'm very grateful to you all for your helpfulness and wizardry. Thank you.

[walruseggman]

Glad to help.

Please mark this thread as solved, and any reputation points (the * in the lower left of the posts) is appreciated.

[Knownowt]

Done and done. Thanks again.