Adding the count of repeating values

[dshafique]

Hi everyone, I would like your help on figuring out an issue I am facing. I have built a macro, where I need to format the source data and map it and transfer it to a template. One of the fields I have to map requires that I put an underscore and a number after the record, the number being the duplicate number. for instance, if the data set has multiple same records, I need to map it like "record_1", "record_2" etc etc. I can understand the logic of how to do this if I'm coding in python or something, but cannot execute my thoughts in vba, any help would be appreciated. see below an example of sample data.

record	Data
Apple	data
Orange	data
Orange	data
Kiwi	data
Apple	data
Apple	data
Apple	data
Kiwi	data
Banana	data

 

[zero269]

I found this example here. If I understand you correctly...

You'll notice that as the formula counts down, it automatically increased the range it's counting to get numbers greater than 1.

VBA Testing.xlsm
	I	J	K
1	Record	Data	Count
2	Apple	data	1
3	Orange	data	1
4	Orange	data	2
5	Kiwi	data	1
6	Apple	data	2
7	Apple	data	3
8	Apple	data	4
9	Kiwi	data	2
10	Banana	data	1
Indirect-Dates

Cell Formulas
Range	Formula
K2:K10	K2	=COUNTIF($I$2:$I2,$I2)

 

[dshafique]

Thank you, this is exactly in the direction of what I'm looking for. Is there a way for me to do this in the VBA code without the need of the extra helper columnn with the formula?

currently I have a code which will take the value in the record column and add_1 to it. currently I just hardcoded _1, but would like to have it be _X where x will be the number it is

Thanks

 

[zero269]

Is there a way for me to do this in the VBA code without the need of the extra helper columnn with the formula?

I'm confident there is, but my VBA skills are lacking to say the least. I try to avoid adding new columns as well, especially with large datasets in the thousands, as formulas constantly updating with new changes slows down Excel.

I suspect that the cell value in the 'Record' column would need to be renamed to reflect the number curently in the 'Count' column. You may not want your macro renaming all of the values in the column, and just the new one being added.

This might be achieveable using a Worksheet_Change sub routine. Hopefully this helps you look for a solution if the formula/helper column isn't optimal.

However, until a VBA expert in this forum comes along, you could use this to combine the results and then have your macro use that combined value for the timebeing.

I'm providing two examples: TEXTJOIN and CONCAT, depending on which version you are using.
If you use either of the two examples, you don't need my column 'C'. I'm combining the value in your 'Record' column with that formula from 'C' so you only need one helper column.

If a MACRO is your desired approach, then don't mark this thread as SOLVED until a VBA expert provides one. Or, you can take this thread and create a new one converting this Formula-based solution to a Macro-based solution.

Either way, best of luck to you.

VBA Testing.xlsm
	A	B		D	E
1				Requires Office 365	Any Office Version
2				Combined using TEXTJOIN	Combined using CONCAT
3	Record	Data		Record_Count	Record_Count
4	Apple	data		Apple_1	Apple_1
5	Orange	data		Orange_1	Orange_1
6	Orange	data		Orange_2	Orange_2
7	Kiwi	data		Kiwi_1	Kiwi_1
8	Apple	data		Apple_2	Apple_2
9	Apple	data		Apple_3	Apple_3
10	Apple	data		Apple_4	Apple_4
11	Kiwi	data		Kiwi_2	Kiwi_2
12	Banana	data		Banana_1	Banana_1
Count-Dupes

Cell Formulas
Range	Formula
D4:D12	D4	=TEXTJOIN("_",1,$A4,COUNTIF($A$4:$A4,$A4))
E4:E12	E4	=CONCAT($A4,"_",COUNTIF($A$4:$A4,$A4))

 

[zero269]

Is there a way for me to do this in the VBA code without the need of the extra helper columnn with the formula?

I just realized something while looking for a vba solution...

Renaming the values in the Record column will change the cell value and thus change the Count number of duplicates.

For example, if a macro renames the first Apple to Apple_1, then the next Apple in the list will not be identified as a duplicate, such as Apple_2.

It looks like the helper column is necessary, UNLESS the macro strips the "_#" to check for duplicates... and that sounds insanely difficult... in my opinion of course.

 

[dshafique]

I Appreciate it, I guess helper column is needed, thank you

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I guess helper column is needed

No, it shouldn't be needed.
Try this with a copy of your workbook (you may need to edit the column as I have assumed the "record" data in column A.

Sub Add_Suffix()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  With Range("A2", Range("A" & Rows.Count).End(xlUp))
    a = .Value
    For i = 1 To UBound(a)
      d(a(i, 1)) = d(a(i, 1)) + 1
      a(i, 1) = a(i, 1) & "_" & d(a(i, 1))
    Next i
    .Value = a
  End With
End Sub

Before:

dshafique.xlsm
	A	B
1	record	Data
2	Apple	data
3	Orange	data
4	Orange	data
5	Kiwi	data
6	Apple	data
7	Apple	data
8	Apple	data
9	Kiwi	data
10	Banana	data
Sheet1

After:

dshafique.xlsm
	A	B
1	record	Data
2	Apple_1	data
3	Orange_1	data
4	Orange_2	data
5	Kiwi_1	data
6	Apple_2	data
7	Apple_3	data
8	Apple_4	data
9	Kiwi_2	data
10	Banana_1	data
Sheet1