VBA code for If same ID then a few functions

[boojangus]

So I am trying to make a macro with data that has IDs starting in column "A2:" and trades starting in column "D2". The IDs are sometimes repeated as you can see below. I want the macro to only populate rows if negative values or both negative and positive values exist in the trade column given the corresponding ID, and to NOT show the row if only positive for the ID. I have bolded the 2 rows that should be deleted in this example to make it easier to see. Also in the expected if possible, I am looking to label which rows were a combination of positive and negative values, or just negatives. So in the expected below in column "O" you will see "RR" on the rows which had positive and negative, and "R" on the IDs contained only negative.

Please Login or Register  to view this content.

Below is the code I have so far but it does not work properly and I have not attempted to include the part in Column "O"

Please Login or Register  to view this content.

I get an error on
obj.Add Cells(j, 2).value, Cells(j, 1).value
It seems as though the error occurs when theres repeated value the error I receive is: "Item has already been added. Key in dictionary: '-1000' Key being added '-1000'

I am open to any other approach on this but this is currently the progress I have made. Please let me know if you have any other questions regarding the data, any help would be greatly appreciated. This would also serve as a great learning tool for me and I am sure others as well. Thank you

[Leith Ross]

Hello boojangus,

Welcome to the forum!

I simplified your macro by using a single dictionary object and an array.

Please Login or Register  to view this content.

[Marc L]

As column O can be filled just using an easy formula combining REPT & COUNTIFS so after you can apply a filter for rows to delete,
all needs around 10 codelines … A workbook attachment may be appreciated to bring a more targeted help.