Incement a cell then keep it

[Cpt_kludge]

I have a column of about 5000 names. I add to this column regularly and have it the conditions set to show me duplicates. I then delete them.
I want to have a count of how many times I have tried to add a name that is already on the list, then KEEP that value showing me the names that have come up the most. Over time this will show me the names with the most activity. The date currently is in D column and starts on Row 2

In A2 I have used =COUNTIF(D:D, D2) to tell me the number of times D2 appears in the column. It is a value of 1 and will change to the number of duplicates found in the column.
In B2 I have =IF(A2>1, A2+1, B2) this should make B2 increment +1 each time that A2 is >1. It returns the number of duplicates +1 but does not keep this number once the duplicates are removed. Anyidea how I can keep this number and incrament it by the number of dupicates? I tried =abs, but could not get that to work.
Thanks from a Newby
Joe

 

[James006]

Hello,

There are many different potential solutions ...

The fastest is probably to use revision tracking ...

see https://excelribbon.tips.net/T008150_Using_Revision_Tracking.html

Hope this will help

 

[Cpt_kludge]

Thanks. This is not really what I'm looking for. If there is a way to make a cell increment up then lock so the formula incrementing it cannot drop it back to it's lower value. I want to keep track of the number of instances that a duplicate to my original list is added then lock that number and remove the duplicate so the list only has one occurrence of each name. Have not figured that out

 

[James006]

Hello again,

A couple of quick remarks :

1. In cell B2 ... you cannot have =IF(A2>1, A2+1, B2) ... Circular Reference ...

2. Probably you need in B2 =IF(A2>1, A2+1, 1)

3. Do you manually add Names in your Column D ..?

4. Apparently you are deleting Duplicates ... but are you ALWAYS deleting the latest ones ...?

5. Could a Worksheet_Change event be of any help ?

 

[Cpt_kludge]

Hello again,

A couple of quick remarks :

1. In cell B2 ... you cannot have =IF(A2>1, A2+1, B2) ... Circular Reference ...
Yes, I saw this and tried it.

2. Probably you need in B2 =IF(A2>1, A2+1, 1)
as A2 is always 1 (number of times that D2 occurs in Column D) then If true, B2 becomes whatever new number A2 becomes +1. That would be fine if B2 could NOT change DOWN when A2 goes back to 1 (after I eliminate the duplicates).

3. Do you manually add Names in your Column D ..?
Yes, the column will fill all duplicates a different color so I can delete them!

4. Apparently you are deleting Duplicates ... but are you ALWAYS deleting the latest ones ...?
Yes and adding new ones.

5. Could a Worksheet_Change event be of any help ?

I don't see how

Thanks

 

[Cpt_kludge]

I am trying to keep track of how often a person posts on a dertain site. I can tell when they do, but am having a hard time weeing who posts the most

 

[James006]

Sorry ...

But if you do not want to answer the questions ...

Cannot see how we could help you out ...

 

[Cpt_kludge]

Here are the answers again, with the questions.
A couple of quick remarks :

1. In cell B2 ... you cannot have =IF(A2>1, A2+1, B2) ... Circular Reference ...
Yes, I saw this and tried it.

2. Probably you need in B2 =IF(A2>1, A2+1, 1)
as A2 is always 1 (number of times that D2 occurs in Column D) then If true, B2 becomes whatever new number A2 becomes +1. That would be fine if B2 could NOT change DOWN when A2 goes back to 1 (after I eliminate the duplicates).

3. Do you manually add Names in your Column D ..?
Yes, the column will fill all duplicates a different color so I can delete them!

4. Apparently you are deleting Duplicates ... but are you ALWAYS deleting the latest ones ...?
Yes and adding new ones.

5. Could a Worksheet_Change event be of any help ?
I cannot see how

Joe

 

[James006]

Thanks for ALL your answers ...

Below is your Event macro to be tested ...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 4 Then Exit Sub
Dim last As Long
Application.EnableEvents = False
  last = ActiveSheet.Cells(Application.Rows.Count, "A").End(xlUp).Row
  Target.Offset(0, -3).FormulaR1C1 = "=COUNTIF(C[3], RC[3])"
  Range("B2:B" & last).FormulaR1C1 = "=IF(RC[-1]>1, RC[-1]+1, 1)"
  Range("B2:B" & last).Value = Range("B2:B" & last).Value
Application.EnableEvents = True
End Sub

Hope this will help

 

[Cpt_kludge]

Thanks James