Incement a cell then keep it

C

Cpt_kludge

New Member
Joined
Dec 10, 2018
Messages
16
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
 
Sort by date Sort by votes
You are welcome ...

Once you have tested the macro ... feel free to share your comments ...:)
 
Upvote 0

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
OK, I have pasted this into the VBA editor. I have not messed with Macro's in a LOOOONG time.

Saved it as a Macro enabled spreadsheet and tried to run it..
Cannot see what it it is trying to do and what I need to do to make it run.
AFAICT i am not doing it right:(

I have attached a insert of the spreadsheet with names duplicated. I would like the Column C numbers to increment then when I delete the duplicates, it keeps the number in column c and adds to it when I add other duplicates. I cannot see how to paste the VBA screen shot :(

Sorry, I think I know excel some but am obviously NOT in your league

Thanks for your help I Tried it in the Test and previewed the post. The names that are ??? are Chinese, or some such text characters.

Excel 2013 32 bit
ABCD
1COUNTIF(D:D, Dx)IF(A2>1, C2+1, C2)# of occurancesNames
2101A Linda Zimmerman Karnstedt
3101Aage van Ruiten
4201Aaron Bibl
5201Aasusanne Meyer Borden
6201Abbee Warner
7101Abby McAbby
8101Abbys Gramma McEachern
9101ACosmin Pao
10101Ad van der Made
11101Adalberto Gentiloni
12101Adam Myers
13101Adam Price
14101??
15201???
16201???
17101???
18101???
19101???
20101???
21101??
22101??
23Aaron Bibl
24Aasusanne Meyer Borden
25Abbee Warner
26???
27???

<tbody>
</tbody>
Sheet1​

Worksheet Formulas
CellFormula
A2=COUNTIF([COLOR=rgb(255]D:D, D2[/COLOR])
B2=IF([COLOR=rgb(255]A2>1, B2+1, B2[/COLOR])
A3=COUNTIF([COLOR=rgb(255]D:D, D3[/COLOR])
B3=IF([COLOR=rgb(255]A3>1, B3+1, B3[/COLOR])
A4=COUNTIF([COLOR=rgb(255]D:D, D4[/COLOR])
B4=IF([COLOR=rgb(255]A4>1, B4+1, B4[/COLOR])
A5=COUNTIF([COLOR=rgb(255]D:D, D5[/COLOR])
B5=IF([COLOR=rgb(255]A5>1, B5+1, B5[/COLOR])
A6=COUNTIF([COLOR=rgb(255]D:D, D6[/COLOR])
B6=IF([COLOR=rgb(255]A6>1, B6+1, B6[/COLOR])
A7=COUNTIF([COLOR=rgb(255]D:D, D7[/COLOR])
B7=IF([COLOR=rgb(255]A7>1, B7+1, B7[/COLOR])
A8=COUNTIF([COLOR=rgb(255]D:D, D8[/COLOR])
B8=IF([COLOR=rgb(255]A8>1, B8+1, B8[/COLOR])
A9=COUNTIF([COLOR=rgb(255]D:D, D9[/COLOR])
B9=IF([COLOR=rgb(255]A9>1, B9+1, B9[/COLOR])
A10=COUNTIF([COLOR=rgb(255]D:D, D10[/COLOR])
B10=IF([COLOR=rgb(255]A10>1, B10+1, B10[/COLOR])
A11=COUNTIF([COLOR=rgb(255]D:D, D11[/COLOR])
B11=IF([COLOR=rgb(255]A11>1, B11+1, B11[/COLOR])
A12=COUNTIF([COLOR=rgb(255]D:D, D12[/COLOR])
B12=IF([COLOR=rgb(255]A12>1, B12+1, B12[/COLOR])
A13=COUNTIF([COLOR=rgb(255]D:D, D13[/COLOR])
B13=IF([COLOR=rgb(255]A13>1, B13+1, B13[/COLOR])
A14=COUNTIF([COLOR=rgb(255]D:D, D14[/COLOR])
B14=IF([COLOR=rgb(255]A14>1, B14+1, B14[/COLOR])
A15=COUNTIF([COLOR=rgb(255]D:D, D15[/COLOR])
B15=IF([COLOR=rgb(255]A15>1, B15+1, B15[/COLOR])
A16=COUNTIF([COLOR=rgb(255]D:D, D16[/COLOR])
B16=IF([COLOR=rgb(255]A16>1, B16+1, B16[/COLOR])
A17=COUNTIF([COLOR=rgb(255]D:D, D17[/COLOR])
B17=IF([COLOR=rgb(255]A17>1, B17+1, B17[/COLOR])
A18=COUNTIF([COLOR=rgb(255]D:D, D18[/COLOR])
B18=IF([COLOR=rgb(255]A18>1, B18+1, B18[/COLOR])
A19=COUNTIF([COLOR=rgb(255]D:D, D19[/COLOR])
B19=IF([COLOR=rgb(255]A19>1, B19+1, B19[/COLOR])
A20=COUNTIF([COLOR=rgb(255]D:D, D20[/COLOR])
B20=IF([COLOR=rgb(255]A20>1, B20+1, B20[/COLOR])
A21=COUNTIF([COLOR=rgb(255]D:D, D21[/COLOR])
B21=IF([COLOR=rgb(255]A21>1, B21+1, B21[/COLOR])
A22=COUNTIF([COLOR=rgb(255]D:D, D22[/COLOR])
B22=IF([COLOR=rgb(255]A22>1, B22+1, B22[/COLOR])

<tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
Hello,

The macro posted in message # 9 is an Event Macro ...

An Event macro has two main characteristics :

1. You need to store it in the Sheet1 module

2. It will be triggered automatically as soon as you change your worksheet

Hope this clarifies
 
Upvote 0
OK that helps:) I will see if I can put it into sheet 1 as an event Macro:)
Had never heard of this :) A learning experience:) Thanks
 
Upvote 0
Sadly pictures are totally inert objects ... and do not speak ...

Once your Event Macro is correctly stored ... ONLY thing which matters is TESTING ... :)
 
Upvote 0
I have added names to column D with it only changes to the numbers in column A and B. A
Column A goes from 1 to 2 On the original row when the a duplicate is added to column D
Column B goes from 1 to 3 On the original row when the a duplicate is added to column D
Both go back to 1 when the duplicate is removed.

Currently Column A is =COUNTIF(D:D, Dx) (and filled down)
Currently Column B is =IF(A2>1, A2+1, 1) (and filled down)
Currently Column C is 0 (and filled down)

Is there a way to send you a copy of the test file I'm using?
Joe
 
Upvote 0
Hello,

Could you test following :

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

The Column C is not part of this macro ...
What is its purpose ...?

Hope his will help
 
Upvote 0
Tried and again, when I add duplicates the numbers in columns A & B increment. When the duplicates are removed, the numbers in column A & B return to 1.
I would like it to, each time a duplicate in column D is added, have a number increment (In some column that can be related to the information in each cell of column D) and stay incremented when the duplicate is removed.
I have saved the file to https://www.dropbox.com/s/urfi93zbctj5ua8/20180702 gift list.xlsm?dl=0 if anyone wants to play with it
Thanks
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Delete duplicate rows while keeping the largest value and smallest value corresponding to the same name
Replies
4
Views
122
sofas
S
A
  • Solved
Remove duplicates keep formatting
Replies
1
Views
199
Aqusmacro
A
T
  • Solved
Adding Another Condition to 'Countifs' Formula to not count duplicates
Replies
3
Views
238
Fluff
Fluff
G
  • Solved
If duplicate in a cell then keep 1st row and move remaining row to new sheet/ excel
Replies
4
Views
284
goodstudent
G
P
  • Question
I am trying to do a pivot table but I have repeated info in two columns that are related so I cannot use the remove duplicates. What can I do?
Replies
1
Views
118
pinkybabs
P

Forum statistics

Threads
1,214,415
Messages
6,119,382
Members
448,889
Latest member
TS_711

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top