A complex problem-replacing repetitive numbers with blank

Dear All, once again thanks for reading and trying to help. Its one of the most wonderful forums I have been to!
I'm attaching a mini replica of the excel file I have. As you will see, there are company names in the first row and company codes below each name. Its a time series daily data for 30 years. Now, here is the problem. For some companies (those which say 'dead' after the company name-for example column F company, ABN Granites), after a certain date (in the case of ABN granites, its 17-02-1997) the values are repeated till the end (again in the case of ABN granites, that value is 5.56 which starts at around the same date-17 feb 1997). There are few other companies in this file which are dead and have the same issues after a certain date.
In the original sheet-and I have many such sheets-, there are approx 200+ companies which either have the word, dead or the word, susp, which has the same issue. Is there a way I can tell excel to replace all such numbers after a particular date with a blank? Manually doing it one by one will take hours if not days!
Please let me know if you want more information on this problem.
Thanks very much!

Wonderful! Thanks so much! Yes, it worked on the active sheet. Yes, the dates are always the part of the column title and in the same format-which is bit different from the format in the time series dates. So, will this macro work on much larger data sets? Also, if I want to include the word, 'susp' where should i put it in the macro? Some companies have susp in the column title instead of dead and they have the same issue.

Hi p24leclerc, unfortunately this macro doesnt seem to work on a larger data set with 250+ columns and same number of rows as I had in my example sheet. I mean it does the job for first few companies but after that, it completely misses replacing the repetitive numbers with blanks. Not sure what's the problem. I couldnt attach my sheet here because its much more than 1 mb limit, so perhaps if i can send you an email you can see the sheet.
Thanks!

Originally Posted by mike7952

Your missing on column G though!

Ignore this, if this one is talking about my code.

maybe "$1/$2/$3" ("dd/mm/yy")

You are totally worng.
What I edited was just changed 0 to 1 for the 3rd arg for Match function, just in case for the date is missing.
Nothing to do with the original algorithm.

ColG says "05/02/01" that means 2001/Feb/05.
See row 7333 which is the date of 2001/2/5 after the code.
The code attached to the attached file is the code before I have edited, so it is 0, instead of 1 for 3rd arg of Match function and it is still functioning.

perhaps it depends on the regional settings
it

Please Login or Register  to view this content.

gives me
17.02.1997
01.02.2005 (instead of 05.02.2001)
15.01.1999
and "$1/$2/$3" gives the correct result

Originally Posted by nilem

perhaps it depends on the regional settings

Maybe so, then OP should adjust it under his/hers regional setting.
Anyway, the code should be like that simple when using RegExp.

Please Login or Register  to view this content.

My apologies guys, I was traveling, so didnt check the thread. Thanks to P24Lecrec for the PM. There seems to be many versions, let me go through each of the replies properly before I comment further.

Hello P24leclerc and others, I checked the macros and here is the problem:
1. There are many companies in the actual sheet which are dead but there is no date listed. Even though there are repetitive numbers after a certain date, that date in not mentioned, so I'm not sure if there could be a solution to that.

2. As noticed earlier, in the column A there are certain dates missing. So, for example if the top cell mentions date 02/03/1999 as the dead date, there is a possibility that the repetitive numbers start at 04/03/1999 as 02/03/1999 is missing. But I guess P24leclerc has already found a solution for that.

3. As for the formats of the dates which you guys have already discussed a lot, I think it works fine, but we have to first find the solution for point 1 before we get to these issues.

I think there are two ways to get around point 1.

a) Either we first create a macro so that it could identify such dead companies with dates missing and highlight them or separate them. Then I manually give them dates so that macros above could run successfully. Ofcourse it will take some time as there seems to be quite a few such companies in each sheet.

b) Or we could write a new macro where rather than having this complex macro with matching dates from dead companies etc, we simply write a program so that in the sheet all such companies when a number starts repeating and goes on till the end, it removes all such numbers. For example, we could write a program so that it takes into account that only when the number repeats more than 10-20 times and towards the end, it should replace such numbers with blank. The only problem with this approach is that at times, a company gets dead bit later than the date when the numbers start repeating. So, there will be some numbers deleted which ideally shouldnt be.

My guess will be that b) should be easier to do, but I have to check with my boss if that's not a big compromise we are doing.

Any thoughts on this? Thanks again for your help everyone.

This will clear the cells from the bottom as long as the value is the same.

Be careful, this doesn't care about the heading.

Please Login or Register  to view this content.

If the header always contains "Dead" or "sups", the code should look like

Please Login or Register  to view this content.

Wonderful guys! Million thanks to both Jindon and P24leclerc! I checked the last macro provided my Jindon on a large dataset and it worked! I mean I randomly checked 8-10 companies and it has done the job. There is just one little thing-for companies where the dead date is not available corresponding the top cell date, the repetitive numbers are cut from a day earlier. I was wondering if the blanks could start from a day later? I mean if the dead date is 27th feb and there is no 27th feb on the A column, it starts deleting the numbers starting from 26th feb rather than lest say 1st march.
Anyways this is a small thing. Thanks again!

Try change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.