Clearing Contents in 2 cells, based on the value of 3 cells

[hriggs]

Hi,

Ok so I am finding this a bit complicated to say the least.

I've got this sheet (example attached) where the are a few sections of data.

The first, highlighted in blue is of a variable number of rows.

The rows highlighted in yellow have values in column A, which other macros work off so these cannot be changed.

I've written a macro that goes through the sections marked 'Add Additional Accurals here' and 'Add Additional Open Trades here', and it clears the contents of a cell in the Fund_Group_id and Asset_Id colums if the value column L (End_Market_Value) is equal to zero. Thats why there are some blank rows in the data.

What I need is a macro that will look at all the remaining data in the range in the macro on the attached sheet (where I got stuck), and will count the number of times the values in Fund_Group_id, Asset_Id and End_Market_Value (column L) are the same. If there is more than one line for each, then the values in Fund_Group_id and Asset_Id for all but the first occurance need to be cleared.

I hope that makes sense! If you check what I've written so far in the attached sheet, hopefully that will give you a better idea of what I'm trying to explain.

Thanks very much in advance.

example sheet 1.xls

[Leith Ross]

Hello hriggs,

After looking at your data, it appears the data doesn't contain any triple duplicates (Columns B, E, L) that you mention. I see instances of where columns B and E repeat, but none where L is also repeated. Have you already removed them?

Sincerely,
Leith Ross

[hriggs]

Hi Leith,

Thanks for the reply.

I've highlighted a few of the duplicates in the attached, in this case there are not many instances, however sometimes there are quite a few.

I forgot to add in my previous post, if there is an entry under the additional accruals section, then any duplicates under the additional recruals and the open trades section should be removed, apart from the first instance.

If the duplicates are all in one section, then all apart from the first instances should be removed.

So in the case of the attached in red, all entries from the open trades would be removed. For the green, the second and third instance would be removed.

Thanks

Huwexample sheet 1 highlighted.xls

[hriggs]

Just to add, only the values in columns B and E need to be cleared, as the values in all the other columns pull through using formula based on B and E.

I was thinking of using some sort of loop that would run until the value of the cell in column A = End, as the number of rows changes each time. I've used this for some of the other macros on the sheet.

[hriggs]

After confusing myself a fair bit over the last couple of days, I've managed to bodge this together.

Ok, it may not be pretty, but I am still learning and it does the job

I've changed the macro slightly so that it clears the contents of two cells based on the values of those two, instead of three as per the title of the topic.

Please Login or Register  to view this content.