Get unique records by first column using dictionary

Hello everyone
I have the following working code

Please Login or Register  to view this content.

The code loops through the rows and based on unique entries in column A get all the records for similar IDs and put the results in G2 as start point
I am sure it can be compacted with more intelligent approach .. Can you provide me with the compact version so as to make use of the smart approaches?

sample file...

Any data in range("A2:E10") .. In column A put some IDs 100,200,300 ..

We are learning from you Mr. Expert Jindon

We are learning from you Mr. Expert Jindon

If this is the case and the initial code was jindon's then there can be no improvement!

Thanks a lot.

Not sure if performance would be improved but this is how I'd do it based on my understanding of the initial code

Please Login or Register  to view this content.

Thanks a lot nankw83 for the positive sharing.
Best Regards

@YasserKhalil ... You are welcome. I’m learning as well

@jindon ... Thanks for pointing that out for me, I wasn’t aware of it. I guess this is just another limitation of Application.Transpose. What alternative do you suggest to use ?

Excellent. Thanks for your valuable input

@jindon, I put a small excel file (attached) to test & see what is the error message/behavior excel will show but I didn't get any & it works Did I misunderstand your comment in post #11 ?

Please Login or Register  to view this content.

Thanks jindon for your feedback. It seems that it is indeed related to the version. Looks like Microsoft has fixed the 255 character limit as it is working flawlessly in my excel. Regarding the second code, I was testing it the other day as I read about transpose limitation of 65k records HERE & it seems that in older version you get an error whereas now the user doesn't even though excel doesn't provide the expected results ! If you run the transpose with anything below 65,536 it works as expected once the size exceeds the aforementioned number of records you get messed up data with many #N/A as show in the attached file.

By the way, when I run your 2nd code, I don't get any error but I get 34,465 & strangely enough if I run your code with 70k I get 4,466 !

Thanks for the clarification as it seems that we learnt that Microsoft has fixed one thing

Yeah, I knew that already, thanks.

So, it still works properly less than 2^16, but the limitation of 255 characters are expanded.

PS. It returns Actual Upperbound Mod (2 ^ 16 -1) without Error, and that's a problem.