Matching and copying repeated values based on root value - code works but very slow

I have working code to solve my data issue, my problem is that my data set is enormous and my code is way too slow and inefficient to run in an appropriate time frame (it's taking many many hours to complete).

I have two sets of data and I need to match them based on a "root" value to create a mapping. I'll explain what I mean by "root" value here with a basic data scenario...

Data Set 1:
PRA7854.0
PRA7854.1
PRA7854.2
EVA1125.0
EVA1125.1
EVA1125.2

Data Set 2:
PRA7854.SS1.0
EVA1125.SS2.2

In the above data sets the root values would be PRA7854 and EVA1125. So the final data map, in this basic scenario, would look like this:

Data map:
PRA7854.0 PRA7854.SS1.0
PRA7854.1 PRA7854.SS1.0
PRA7854.2 PRA7854.SS1.0
EVA1125.0 EVA1125.SS2.2
EVA1125.1 EVA1125.SS2.2
EVA1125.2 EVA1125.SS2.2

But there's one more wrench: the data maps one to many. Here's a more accuracte data scenario...

Data set 1:
PRA7854.0
PRA7854.1
PRA7854.2
EVA1125.0
EVA1125.1
EVA1125.2

Data set 2:
PRA7854.SS1.0
PRA7854.SS1.1
PRA7854.SS1.2
EVA1125.SS1.0
EVA1125.SS2.0
EVA1125.SS2.1

So in the above data sets the root values are still PRA7854 and EVA1125 but the final data map would need to look like this:

Data map:
PRA7854.0 PRA7854.SS1.0
PRA7854.0 PRA7854.SS1.1
PRA7854.0 PRA7854.SS1.2
PRA7854.1 PRA7854.SS1.0
PRA7854.1 PRA7854.SS1.1
PRA7854.1 PRA7854.SS1.2
PRA7854.2 PRA7854.SS1.0
PRA7854.2 PRA7854.SS1.1
PRA7854.2 PRA7854.SS1.2
EVA1125.0 EVA1125.SS1.0
EVA1125.0 EVA1125.SS2.0
EVA1125.0 EVA1125.SS2.1
EVA1125.1 EVA1125.SS1.0
EVA1125.1 EVA1125.SS2.0
EVA1125.1 EVA1125.SS2.1
EVA1125.2 EVA1125.SS1.0
EVA1125.2 EVA1125.SS2.0
EVA1125.2 EVA1125.SS2.1

As you can see, the values of Data set 1 need to be repeated for as many matching root values there are in Data set 2. I've already accomplished this as well as adding the root value (with manually doing Text-to-columns and a countif formula), so my prepared data sets look like this (I have removed some records from data set 1 just to reduce the length of my post):

Data Set 1:
AA284.0 AA284
AA284.0 AA284
AA284.0 AA284
AA284.0 AA284
AA284.0 AA284
AA284.0 AA284
AA284.0 AA284
AA284.0 AA284
AA284.1 AA284
AA284.1 AA284
AA284.1 AA284
AA284.1 AA284
AA284.1 AA284
AA284.1 AA284
AA284.1 AA284
AA284.1 AA284
AA30.0 AA30
AA30.0 AA30
AA30.0 AA30
AA30.0 AA30
AA336.0 AA336
AA337.0 AA337
AA337.0 AA337
AA337.0 AA337
AA337.0 AA337
AA337.0 AA337
AA337.0 AA337
AA337.1 AA337
AA337.1 AA337
AA337.1 AA337
AA337.1 AA337
AA337.1 AA337
AA337.1 AA337
AA8.0 AA8
EVA1176.0 EVA1176
EVA1192.0 EVA1192
EVA1225.0 EVA1225
EVA1225.0 EVA1225
EVA1225.0 EVA1225

Data Set 2:
AA284.SS1.0 AA284
AA284.SS2.0 AA284
AA284.SS2.1 AA284
AA284.SS3.0 AA284
AA284.SS3.1 AA284
AA284.SS3.2 AA284
AA284.SS4.0 AA284
AA284.SS4.1 AA284
AA30.SS31.0 AA30
AA30.SS32.0 AA30
AA30.SS32.1 AA30
AA30.SS33.0 AA30
AA30.SS33.1 AA30
AA30.SS34.0 AA30
AA30.SS35.0 AA30
AA30.SS36.0 AA30
AA336.SS1.0 AA336
AA337.SS1.0 AA337
AA337.SS2.0 AA337
AA337.SS3.0 AA337
AA337.SS3.1 AA337
AA337.SS3.2 AA337
AA337.SS3.3 AA337
AA8.SS9.0 AA8
EVA1176.PS1.0 EVA1176
EVA1192.PS1.0 EVA1192
EVA1225.PS1.0 EVA1225
EVA1225.PS10.0 EVA1225
EVA1225.PS10.1 EVA1225
EVA1225.PS10.2 EVA1225
EVA1225.PS11.0 EVA1225
EVA1225.PS12.0 EVA1225
EVA1225.PS13.0 EVA1225
EVA1225.PS14.0 EVA1225
EVA1225.PS15.0 EVA1225
EVA1225.PS2.0 EVA1225
EVA1225.PS3.0 EVA1225
EVA1225.PS4.0 EVA1225
EVA1225.PS5.0 EVA1225
EVA1225.PS6.0 EVA1225
EVA1225.PS7.0 EVA1225
EVA1225.PS8.0 EVA1225
EVA1225.PS9.0 EVA1225


The code below will create the final data map desired by filtering on the root value from set 1 on set 2 and copying the related value back to set 1. The issue is the autofilter is slow and this is only exacerbated by the fact data set 1 is almost 32,000 rows and data set 2 is almost 10,000 records. If anyone has any ideas on how to make my code faster/more efficient or has an alternative ideas it'd be most appreciated.

My code:

Please Login or Register  to view this content.

I have taken a different approach to your problem (using Collection objects). The enclosed workbook contains 3 worksheets.

- Dataset1,
- Dataset2,
- Result

When you run the routine "pub_sub_BuildCombinedDataSet()" the Result worksheet will be populated with the combined results of dataset1 and dataset2 as per the requested logic. There is one constraint, the entries in the dataset worksheets must be in a sorted order!

I do not have the amount of rows that you have so the performance in difficult to test. Using a test data set of 50 entries in dataset1, 70 entries in dataset2 and a resulting combined dataset of 2,072 rows takes about 0.09 seconds.

I would say, give it a try if you want to

PS: This solution also does not depend on the prepared datasheets

@AB33,

Strange. I have enclosed the file once more in this post itself. Can you please let me know if the link works.

MergeDataSets.xlsm

I must admit, I am quite happy with the speed of my solution. I assume your problem is solved?

A little faster solution

Please Login or Register  to view this content.

Hi Phishie,

if you replace the following code

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

you should see a further performance improvement