Hello,
I am trying to find duplicate values in one column and combine the values of another column and separate them with a delimiter e.g. ";". I may have more than 3 columns.
Before
before.JPG
After
after.JPG
I appreciate your help
Nick
Hello,
I am trying to find duplicate values in one column and combine the values of another column and separate them with a delimiter e.g. ";". I may have more than 3 columns.
Before
before.JPG
After
after.JPG
I appreciate your help
Nick
Last edited by Niclal; 08-29-2014 at 06:05 AM.
You need to post more information about the input data etc.
Hope this helps
Sometimes its best to start at the beginning and learn VBA & Excel.
Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
Available for remote consultancy work PM me
I have uploaded a test file.
As I mentioned earlier, I need something that I could use even if I have more than 10 columns.
Thank you so much to everyone.
N
Apologies, i completely missed that, just saw the images.
Please Login or Register to view this content.
You need to add a reference to microsoft scripting runtime.
You can use a collection instead of the scripting.dictionary
Cheers
I had a bash at this, I'm not the most experienced coder, but adapted a few bits of code I have used in the past to come up with a solution. Looks pretty inefficient compared to nathansav's response but maybe there is something useful you can use in here anyway!
Relies on the data being sorted first to work (which is within the code):
All the best.Please Login or Register to view this content.
Last edited by Chriz; 08-29-2014 at 07:27 AM. Reason: made an error with column sortation
Hi,
How can the code be changed in case I have 10 columns instead of 3? Is there a way of making the code more flexible when it comes to number of columns?
Cheers,
N
You'll need to change all the arr(i,x)'s in my code, so arr(i,1) ...... arr(i,10)
You could have a value for the number of columns, and add a for next loop in to build this arr(1,x)....arr(1,y) line.
Hi N,
I have made my code a bit more flexible using the 'last column' as a variable, but I'm not sure if I can make the part where it concatenates the data any more flexible, sadly I do not have the knowledge myself with that kind of thing. I'll have a think about it, new code:
Please Login or Register to view this content.
Hi Both,
Many thanks for all your efforts.
I am still wondering how to add a value for the number of columns, and add a for next loop in to build this arr(1,x)....arr(1,y) line
I am not very proficient in VBA.
Cheers,
N
Hi N,
I think I've sussed it, but you may want to do some testing to see if it performs as required, as I say I'm no pro, but this seemed to work on a couple of test runs:
CheersPlease Login or Register to view this content.
Chriz
Hello everyone,
Anybody with other ideas?
Very much appreciated
N
Hello Everyone,
Could someone please help me revise the code such that it can be used for more than 2 columns and also duplicates are removed:
Thank you so much
Please Login or Register to view this content.
@Niclal
It would also be nice if you respond on the solution of Chriz in #11.Please Login or Register to view this content.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Hi Criz,
I did really appreciate all your efforts, and your code solved the problem; I just had to revise it a bit.
I am just looking for alternatives to make the solution more flexible, i.e. for more than 3 columns and also using Scripting.Dictionary.
Any thoughts from anyone?
N
I answered you back in post 8 for how to change my code.
you'll need to change
andPlease Login or Register to view this content.
Did the post nor the 3 not give you any hints?Please Login or Register to view this content.
If you had alook at my code, did some investigation you'd see that passing an array to a range, gives the same amount of columns. Keep posting back isnt the way, you could have solved this yourself sooner.
Sorry Nathansav,
But your code didn't help. I have already tried it. That's why I put my code and hope that you can help me extend it to more than 2 columns.
Thank you
N
I tested my code and it did work on your sample data.
All the best in finding someone to do it for you.
Cheers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks