copy cells from one sheet to another if...

Hello every one,

I need help in processing a very large data. I will be very thankful if someone could help me with it.

i have two worksheets:
first one is: "ws1" containing of col.A(140 000 cells) and col. F
second one is: "ws2" containing of col. A(109 000 cells) and col. B

I want to copy content of col. B from ws2 to col. F ws1, if col.A ws1 exists as part of string in col.A ws2. If there are more than one matches then only the first 10 results should be copied and sperated by ";;" in col.F ws1. After one search is finished then the search should be continued from last position and not from start. There should be no duplicates in col.F.

for example:

ws1 before:
col.A(contain of words and compunds)
back
backer
backwards

ws2
col.A(very long text)...............col.B(long text)
dkd backen...............................m1
fdlkbackisl.................................m2
asdbacker adsl...........................m3
backwards slsbacker...................m3
backerlsl sls so...........................m4

ws1 after:
col.A...................................col.F
back...................................m1;m2;m3;m4
backer................................m3;m4
backwards...........................m3


Thanks for each help in advance.

This should do it for you, variation on our previous macro:

Please Login or Register  to view this content.

dear JBeaucaire,
first of all thank you very much for your answer and code.

The code is updating ws2 and not ws1. Correct will be if puts col. B 's content to col. F in ws1 seperated by ";;". The example file i have uploaded in my thread contains a sheet "ws1 should be" which shows how i need the results to be. I hope you find time to modify the code and thank you very much in advance.

1) You put the code INTO the ws2 module. That's incorrect.

Insert > Module to add a normal module and put the code in there.

2) Your original uploaded book showed the results as going in column C. I see now that your original question did mention column F, but your uploaded workbook had a sheet titled "ws1 should be"...so I followed that. Understandable, yes?

Change every instance of:

Please Login or Register  to view this content.

...to

Please Login or Register  to view this content.

Amended code for regular module:

Please Login or Register  to view this content.

Oops! sorry you are right. I was puting the code in ws2 sheet and not in ws1.

2) Your original uploaded book showed the results as going in column C. I see now that your original question did mention column F, but your uploaded workbook had a sheet titled "ws1 should be"...so I followed that. Understandable, yes?

yes! i had confused c with F. In my problem discription and example i had used F and mistakenly chosen C in attachment which i corrected latter.

But now it works. I dont want to be rude :-) but only two more things more would solve my problem complletely.

The first one is the problem of duplicates in F. For example in new attached file which i have modified word2 in col. A ws1 has two times the same refernce.

Secondly some times there are more than 100 references which explode the column F. Can we put a limit to that for example highest 5 references or may be 10 and no duplicates?

The macro doesn't really go WS1 either. I'm trying to give specific instructions and somehow you're missing them.

Insert > Module to add a normal module and put the code in there.

Fix that first.

========
I was sure I'd put in the no duplicates thing, but there is an error in the logic, sorry about that.

Please Login or Register  to view this content.

...that needs to be corrected.

This adds back in the "Max matches" thing like we did previously on the other macro. Just change the MAX to whatever value you want.

I also added back in the randomization thing so it doesn't always start at the top, it starts where the previous row matching left off. This should insure you get a nice cross-section of matches from your larger dataset.

Please Login or Register  to view this content.