Running a For Loop within another For Loop

Hello,

I am working on consolidating data from a report and I need help finding a faster way to do what I'm doing.

I have a database full of customer numbers - each customer number will appear one or more times in the list. I also have a product code for each line item (which means I could have many product codes for each unique customer number). I'm trying to put together a tab with consolidated information - for example, one column that contains each unique customer number and a corresponding column that contains all of the product codes for the unique customer number.

I already have code that does this, but the original database (the one that contains repeating customer numbers) can contain up to 20,000 line items. Which means, for each unique customer number in the consolidated records, I will have to check each of the 20,000 records for Product code matches. This seems to take much longer than I expected.

Here is the code I have (from the original macro - I have adjusted accordingly for the sample file attached):

Please Login or Register  to view this content.

However, there is a lot more to the code than just this piece. So, I have provided a sample file that uses simplified data and code. Please let me know if you can think of a way to speed up the process.

Also, there is one more thing I want to throw out there - sometimes, when I run a macro on my computer that I've also written, it takes a long time for certain things to happen (I don't know why). So, if the macro runs fairly quickly when you test it, then I may not need additional assistance. If this is the case, please just let me know.

Thanks!

Hi

Sorry the server won't let me upload files. So bear with me.

I would take a fresh look at this.

I put the customer name in column A and the product code in column B [ Use row 3 and downwards ]

I created a list of customers in row 1 [ See Below ]. Starting at C1

To speed things up you need to use excels built in functions wherever possible.

For example to get a list of unique customer numbers. Duplicate the list and use the remove duplicate data function in the data tab.

Now you need to map all the products that each customer has bought

Select the list of customers paste into another column, use remove duplicates to get did of duplicates
Copy and paste transpose to paste the customer names into one row.

A simple formula finds all the products used by each customer.

Please Login or Register  to view this content.

I haven't done it but you could use remove duplicates for each customer to reduce the list.

Then use my new concat function to concatenate the data.

Please Login or Register  to view this content.

event21 - sorry about that . . .

I've actually got one more thing I need to throw into the mix. It seems I oversimplified my sample file. For the Original Data tab, the colors in the sample file (which are in col B, next to the customer number) are actually in Col K on the real file, separated from the Customer Number by 10 columns. Also, the resulting consolidation in the second tab occurs in Col C & D, not A & B like in the first file.

I've added a new sample file to reflect these changes that includes the code you've already sent me, with some minor changes to adjust the final placement of the data (Col C & D), but I can't figure out how to grab Col A & Col K from the original data tab.

Please Login or Register  to view this content.

I thought it was just a matter of changing the offset number from 1 to 10, but that gives me an error.

Can you guide me once more?

Thanks!!!!

@RandomlySet : Anyone what? I think you should be bumping your own thread ... and looking at it ... to see that there have been replies which address your question.

Your posts in this thread could be considered a) hijacking and b) unnecessary

Regards, TMS

Actually, I unmarked this thread as Solved because I am running into an error too (maybe the same as RandomlySet).

When I first open the sample worksheet and apply the code, it works fine. However, when I run it again after making changes to the code that do not affect the dictionary, I get a "Run-time error '13': Type mismatch" error when I try to transpose the items. Does anyone have an idea why this is happening?

Look at the other thread: http://www.excelforum.com/excel-form...ml#post3554813

Probably the same issue if you have a lot of data.

Regards, TMS

You're welcome. Thanks for the rep.


The issue is resolved by replacing that part of the code and is caused by the volume of data being collated and then dumped (transposed) into the cells.


Again, as I said there, the credit is down to event21 ... all the clever work is his. I just tweaked it a little.


Regards, TMS