Merge Columns B,C,D,E and F, If duplicates exist in Column A

Hello,

i have got a wordlist in worksheet "original" which looks like:


Before:

Please Login or Register  to view this content.

I need a macro which merges the columns B,C,D,E and F depending on if there are duplicates in Column A or not. If there are one two or more duplicates in column A,then those should be deleted and only one of them should remain in column A. The members of deleted duplicates in column B,C,D,E and F should be merged together. No duplicates should be made by the process of merging. Each member in column B,C,D,E and F has to be unique. The results are supposed to be put in worksheet "new".Columns B,C,E and F should be merged through signe "/". And Column D should be merged through signe ",".

After:

Please Login or Register  to view this content.

The macro must be able to deal with very large lists. biger than 200 000 words in column A

I will be very thankful for each help and assistance. thanks for each assistance in advance.

Here is the excel file containing the example:

excel file

I forgot to mention the column G. There is a 7th column as well, the column G. The members of G should also be merged if duplicates exist in column A. The same way as B,C,D,E and F.

Hi,

it give me an error that this object doesnt support this porperty or method

typo in 2 places for both dic1 and dic2

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

Hi

Hummm

Can you do a step debug for me?

While you are in VBE;
1) Click somewhere on the code
2) Hit F8

As you hit F8, the code will execute line by line, so find out where it stops.
Or is it highliting any line when you debug?

What I guess from the error 450 is the wrong argument either
y = Split(b(x,4) & "," & a(i, 4),",")
or
b(x,4) = Mid$(txt,2)
but I can find any wrong part in them...

Hi Jindon,
i did the debug the way have asked me to. It runs almost always between "If" and "End If" but it shows no error. As soon as i run it normaly it puts the results in "new" but the column G in "new" is empty each time. And the same error message appears!
Well the error message wont bother me if the column G was not left empty. But the problem is that the column G is left empty.
thank you very much

ColG ?

Your file has only upto Col.F.
Am I missing something ?

Hi,
yes there is a column G as well. :-) That would be cool if column G would also be processed as Column B,C,D,E and F
Sorry for the inconvenience!

Can you attach a file ?

No
I can not access to your file.

sorry! now i corrected the old link. Try again

I can only "see" your sheets, but no macro can be run due to the security here.

I need some sample for "new" sheet.

ok here:

mergeA2.xls

change that to

Please Login or Register  to view this content.

Hi Jindon,
thank you very much. Its not showing the error any more. But the results are yet wrong. I removed the whole line

Please Login or Register  to view this content.

and it still gave me the same result. It seems that it doesnt make any change if this line is there in code or not. It would be kind if you could download the file in my last message and see what the macro is doing wrong. There is the real result which is now wrong and the "supposed to" result which would be right.

thanks alot

Ahh
I didn't see that file....

Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

:-)
now the results are almost ok. The only thing remainig is that the seperator for column D in "new" is the number "4". That should be ",". And the same way the seperator for column G should be "/" and not the number "7".
sorry for being that rude! :-)

Last one (I hope)

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

wooooooooooow its working!

thanks a million times. thanks thanks thanks

Can you do a step debug?

While you are in VBE
1) Click somewhere on the code
2) hit F8

As you hit F8, the code will execute line by line so you will find when it raises an error.

Hi Jindon,
i tried F8 for 48 Duplicates in column A. But it jumps between line 21 and 22 and repeats the same process for thousands of times. Then it goes forward but comes to line 21 and 22 again and keep repeating these two lines:

Please Login or Register  to view this content.

So i waited for minutes but the step by step debug doesnt end.
When i run the macro on 48 duplicates in column A of "original" sheet, it shows me " Runtime Error 1004, Application or object defined error". Even with 31 duplicates it shows me error. But as soon as i decreas the number of duplicates to 30, then the macro doesnt show error anymore.
strange!!

Yep! missed few lines...
change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

:-(
still showing the error, when the duplicates >30 in column A. but it works with duplicates<31. After the changes the current code looks like:

Please Login or Register  to view this content.

have to go now. I will be able to reply tomorrow. Thank you alot!

Hi,
i did the debug. It shows error in this last part(red):

Please Login or Register  to view this content.

I am not sure if the error is caused by the first red line or the second. I did the defrag about four times(each time lasting 60 Minutes) but i couldnt find out if its the first or the second red line.

I hope that its solveable

Than you very much

Julia

julia

We can not output the data into one cell that exceeds 255 characters.
I'm pretty sure that one of the combined data exceeds the length of 255.
We need to split that dat into 2 or even 3, accroding to the length of the characters.

How do yoou want it?

OK

I'm too busy today, so I'll post something tomorrow.

try

Please Login or Register  to view this content.

[/code]

Hallo Jindon,
thanks for the code.
The code is not running and is showing:"incompatibly of types" (tpyen unverträglich, in german)!

1) change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

2) change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

In the second part (Procedure?!) there is a "For" without "Next" says an error message

change to

Please Login or Register  to view this content.

Now its giving the error message: "Laufzeitfehler "9":Index außerhalb des gültigen Bereichs" = Runtimeerror 9: Index exceeds the valid range

Which line ?

I will do line to line debug and will post the line as soon as it is found. Debug may last about 1 or two hours

Originally Posted by julia81

I will do line to line debug and will post the line as soon as it is found. Debug may last about 1 or two hours

just try

Please Login or Register  to view this content.

It will pause at the line of Stop, only if it run through without error.
If Error will be raised, change the position of "Stop", until no error.
Then use F8 after that line to find oud the line...

Ok!

Unfortunatley it doesnt go as far to reach the stop. It shows the error before reaching to the stop.

now i found how to run the macro up to the cursor. And through that i found out that the error starts in procedure at:

Please Login or Register  to view this content.

OOps
Change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Still showing the same error at:

Please Login or Register  to view this content.

Can you change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

No error at "temp = Left(temp, InStrRev(temp, myList(i)(1)) - Len(myList(i)(1)))" anymore

Now the error "out of valid range" is shown in line:

Please Login or Register  to view this content.

Can you just change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

strange!! still the same error at same line:

Please Login or Register  to view this content.

Humm
then change to

Please Login or Register  to view this content.

sorry!! the same error at the same line:

Please Login or Register  to view this content.

again

Please Login or Register  to view this content.

should be

Please Login or Register  to view this content.

:-( again the same error "out of valid range". Now the error at line:

Please Login or Register  to view this content.

I will be able to reply tomorrow again. And i am sorry that its got such complicated!

change to

Please Login or Register  to view this content.

And read the message when it come up ?

Hi Jindon,
here are the screenshots of the messages:

Then with this ?

Please Login or Register  to view this content.

screenshots:

try

Please Login or Register  to view this content.

Error message: " out of valid range" at line:

Please Login or Register  to view this content.

OOps

Please Login or Register  to view this content.

Erro message: "Index is out of valid range" at line:

Please Login or Register  to view this content.

Originally Posted by julia81

Erro message: "Index is out of valid range" at line:

Please Login or Register  to view this content.

That's impossible...

Well when i do the "debug up to the marked line" it shows me error in this area:

Please Login or Register  to view this content.

I start chosed the line of first debug in the main code up to the procedure "test2" and then i went line to line and after there two lines the error message comes.

If its not possible to fix the error, that would go as well if those words are over jumped which creat such an error. So that at least the other words in column A which dont creat error are merged properly.

just run this code
You may have much longer than 255 string length....

Please Login or Register  to view this content.

ok! it doesnt show any error now. But it merges only column B and not the remaining columns.

Originally Posted by julia81

ok! it doesnt show any error now. But it merges only column B and not the remaining columns.

What do you mean ?

There are many columns i the worksheet: A,B,C,D,E,F,G
Now the macro is supposed to merge corresponding columns B,C,D,E,F and G if there are duplicates in column A.

Now the problem is that the columns C,D,E,F and G are not put together.

I am uploading screenshots for the 1)original list, 2)how it looks after running the current macro which give wrong results, 3) how it should look like

Can you post yout current code?

Please Login or Register  to view this content.

Then run this with the same data

Please Login or Register  to view this content.

It shows error message " runtime error: Application or Object definition error"

after line:

Please Login or Register  to view this content.

julia

That is really strange, becuase the code I last posted is almost identical as original.
Did you change sheet structure ?

No i didnt change the structure. Its always the same file i use for testing the macro. Its the one i have posted in one of my last messages.

Now the macro runs but it shows an error. It also gives wanted results but they stop there where the one member of A is ther more than 30 times. And those duplicates who follow the problem creating members, are not processed.

No its getting really very embaracing for me!! I am sorry for giving you this head ache. I am bothering you with this stupid thing for days!! :-(

You don't need to worry about, it will be solved anyway...

So, is it working correctly with the data in small volume ?

yes! but it stops giving results as soon as a problem creating duplicate is there.

for example if aa, aaaa and a were the cells in column A as duplicates.

aa is 3 times
aaaa is 31 times
a is 3 times

it processed only "aa" and doesnt over jump the problem creating "aaaa". Therefore no results after that. In our example no results for "a" cus the macro was already stopped after "aaaa"

I know and that's why I'm here.

OK then, just run the code with the small data

Please Login or Register  to view this content.

its giving the wanted results now but only with the small number of duplicates and no problem creating duplicates between them.

How can i sort out the problem creating duplicates? The total number of cells in column A in the original list, for which i need this macro is 150 000.

julia

Are you in a hurry?
This is started as you said,

ok! it doesnt show any error now. But it merges only column B and not the remaining columns.

So, I needed to find out what went wrong.
We need to start with the correct code that satisfy your need with the small data, otherwise it will never go right.

The reason is very clear, you have the combined data that is much greater charcters then 255 in one cell to output, so we need to prepare extra columns for that. and this is a 2nd step now because your comment above.

No i have time and i can wait for solution.

Well i can imagin two types of solutions:
1) we make another makro which finds problem creating duplicates in column A and put them into third sheet. I will combine them manually latter. That way we will filter our wordlist of 250t from all problem creating duplicates and then use the macro which is ok now.


2) We modify our current macro is such a way that it overjumps the problem creating duplicates.

Hi!
My name is Andy, and I just used the macro code Jiddon created. It worked like a dream for me, and saved me lots of time.
My problem now is that I need to reverse it.
I only have column A and B.
My worksheet is a webshop database reference chart for categorys and products, where column A is for category IDs and Column B is for Product IDs.
After using Jiddons code I was able to put in new categoty IDs in ascending order (1,2,3,4... before it was like:1,4,5,7,11..)and still ceep the relationship between categorys and products.
Now I want to sort the product IDs in the same ascending order.
This is what I need the macro to do:
Put the numbers from the rows in column B which is besides eachother (like this: 1022/936/856)
below each other, like this:
1022
936
856 and create duplicates of the value from the same line in column A.

Example Tables:

From This:

| A | B |
| Category_ID | Product_ID |
| 1 | 1022/936/856 |
| 2 | 456/23/1 |
| 3 | 11 |
| 4 | 123/546/99/8 |

To this:

| A | B |
| Category_ID | Product_ID |
| 1 | 1022 |
| 1 | 936 |
| 1 | 856 |
| 2 | 456 |
| 2 | 23 |
| 3 | 11 |
| 4 | 123 |
| 4 | 546 |
| 4 | 99 |
| 4 | 8 |


I hope someone can help me with this.

My english is not the best, but I`m more worried about my programing skills.

Best regards: Andy

arauoy

You are not allowed to ask your own question in someone else thread.

If you open your own thread, you will not wait for so long to get replies, due to ease of your question.

Hi Jindon,

I have a similar issue,

I am hoping you can help with the below.

I want to convert th

Client|Email|Domain
Client1|[email protected]|test1.com
Client1|[email protected]|test2.com
Client1|[email protected]|test.com.pk
Client1|[email protected]|test.co.uk
Client1|[email protected]|test.test.com
Client1|[email protected]|test.org
Client1|[email protected]|test95.com.hk
Client1|[email protected]|test92.co.uk
Client2|[email protected]|test.ru
Client2|[email protected]|test.sk
Client3|[email protected]|test.cn
Client4|[email protected]|test.us
Client4|[email protected]|test.pro
Client5|[email protected]|test.kitchen


Client|Email|Domain
Client1|[email protected]|"test1.com
test2.com
test.com.pk
test.co.uk
test.test.com
test.org
test95.com.hk
test92.co.uk"
Client2|[email protected]|"test.ru
test.sk"
Client3|[email protected]|test.cn
Client4|[email protected]|"test.us
test.pro"
Client5|[email protected]|test.kitchen


Basically I want the last column to be merged with line breaks if there is a duplicate in column A

Thanks - Taha