Looking to speed up modified countif using VBA

Good Afternoon,

I am looking to speed up a portion of a macro where I am basically looking to create a unique ID for duplicate values. The following formula is currently being used on a dynamic range of ~21k rows and then converted to value. Any help would be greatly appreciated. Thank you!

Please Login or Register  to view this content.

How about

Please Login or Register  to view this content.

That is awesome! Works perfectly. I am new to coding so I really appreciate it. Thank you.

Glad to help & thanks for the feedback.

Also welcome to the board.

Hi Fluff13,

How are you? I hope all is well. I'm having an issue with a vba code you helped me with about 2-3 weeks ago. The code is re-pasted below.

The original file that was used with this code was updated (meaning we cleared out the previous months data and are now using current month data). I'm getting a type mismatch 13 error on the following line: Ary(i, 2) = Ary(i, 1) & "-" & .Item(Ary(i, 1)).

I'm not entirely familiar with scripting dictionaries and/or if there is something else that would be causing this error. I assumed I could just keep recycling/reusing the code each month as new data arrives. Any help would be greatly appreciated. Thank you.

Please Login or Register  to view this content.

When it fails what is the value of i?
Add 3 to that value & then look in col V for that row number, what is in the cell?
For instance if i is 5 what is in V8

Originally Posted by Fluff13

When it fails what is the value of i?
Add 3 to that value & then look in col V for that row number, what is in the cell?
For instance if i is 5 what is in V8

Sorry I just realized that there may be issues with the data. The first error occurs at the 7,162nd item which is #N/A. That should not be. Let me look into this further. Thank you.

It's the #N/A that's causing the problem.

Originally Posted by nickytraps

Hi Fluff13,

How are you? I hope all is well. I'm having an issue with a vba code you helped me with about 2-3 weeks ago. The code is re-pasted below.

The original file that was used with this code was updated (meaning we cleared out the previous months data and are now using current month data). I'm getting a type mismatch 13 error on the following line: Ary(i, 2) = Ary(i, 1) & "-" & .Item(Ary(i, 1)).

I'm not entirely familiar with scripting dictionaries and/or if there is something else that would be causing this error. I assumed I could just keep recycling/reusing the code each month as new data arrives. Any help would be greatly appreciated. Thank you.

Please Login or Register  to view this content.

Maybe version your excel until excel 2013
If using transpose and index Only 65.000 row
Try fluff code

Please Login or Register  to view this content.

This code works, however I am trying to reuse this code in another workbook. I thought it would be as simple as updating the range(s) but apparently not. Would anyone happen know what is causing this? Thank you!

Please Login or Register  to view this content.

How many rows of data do you have?

Went from originally about 21k to now 130k in the new workbook.

In that case try the code daboho supplied in post#9

I think that works. Testing now. Thanks again Fluff13, you too daboho.

You're welcome