Speed/Efficiency Problem

**John Bates** · 05-29-2010, 07:10 PM

Hi,

The attached workbook has a sample (the actual data is very, very large) of data. Basically I have some VBA that is concatenating all of the unique values that match for every unique VisitorID. The calculations are taking way too long and need to find a better, more efficient way of getting to the same data.

The calculations are in columns I:M and the raw data is in columns A:F. I need to dynamically (this data is changing all the time) perform this calculation.

When you open the file, you will notice that the calculations do not take very long. I have cut the data down from about 100,000 rows to a much smaller sample for you.

Thanks so much for any help you can provide me!
John

**shg** · 05-29-2010, 07:54 PM

Can it be assumed that col A is sorted?

**John Bates** · 05-29-2010, 07:59 PM

Yes, the Data Warehouse sorts it by the first column, which will always be VisitorID (DW Only).
Thanks so much for taking some time to help me this!!

Thanks,
John

**shg** · 05-29-2010, 08:03 PM

Is what this does actually what you want done?

**John Bates** · 05-29-2010, 08:10 PM

Basically, yes. Ultimately, I'm trying to consolidate every value in the data for every unique VisitorID. I'm prepping the data for some statistic analysis and I need every row to be a unique record based on a single instance of a unique VisitorID. (this is only one step in the prep).

I don't have to have the count part on the front (I can determine that by the number of "|"s in the cell's result).

I could have just the concatenated values for every unique VisitorID.

Thanks!

**shg** · 05-29-2010, 08:23 PM

In I3 and copy down and across,

=CatUnique($A$2:$A$100000, $H3, B$2:B$100000)

Please Login or Register  to view this content.

**John Bates** · 05-29-2010, 09:07 PM

This is freakin' amazing!!! You are my new hero!! This is 1,000 times faster than the way I was doing it before. Thank you so much!

BTW, I'm fairly good in Excel but I lack the VBA knowledge. Any suggestions for learning VBA (books, websites, etc.)?

Thanks,
John