Copy and paste from one sheet to another if the criteria met

[wali]

Hello every one,
I have a dictionary for which’s words I want to find poem verse pairs as references. I want to add 3 different verse pairs of poems as reference to each word from my wordlist. There are two worksheets:
“ws-1” col. A contains those words I want to find 3 verse pairs as ref. for (ca. 65000 rows)
And
“ws-ref” col. A and B contain verse pairs and col. D has start value “0”(ca. 80 000 rows)
I need a code which reads the word in column A “ws-1” and searches for that in col. A and B of “ws-ref”. If the searched word exits in content of A or B of “ws-ref” then this is an acceptable result. Now three different results from all the found ones should be copy and pasted from “ws-ref” to “ws-1” and 1 should be added to col. D value after each completed pasting. Adding 1 is for the purpose to be able to know latter how many times that specific verse pair was chosen and copied as reference. Criteria for choosing 3 from many results should be the lowest value in col. D “ws-ref”.
1rst result should be copy pasted from “ws-ref” A,B,C,D to “ws-1” col. F,G,H,I (*only if F and G empty)
2nd result should be copy pasted from “ws-ref” A,B,C,D to “ws-1” col. J,K,L,M(*only if J and K empty)
3rd result should be copy pasted from “ws-ref” A,B,C,D to “ws-1” col. N,O,P,Q (*only if N and O empty)
I will be very grateful for each help.

[JBeaucaire]

This should do it for you. If you want to make the FIND more accurate so that "word1" does not match to "word10", we can do that, too.

Please Login or Register  to view this content.

[wali]

Dear JBeaucaire,

first of all thank you very very much for your code. I am very thankful that you took the time to understand my strange problem and that you solved it. The cool thing is that the code works. I tested it with a small list and now its running on a larger list (12000 words in ws-1 and 55000 Rows in ws-ref). I guess it will finish its work duiring the day today. It has been running for 9 hours now and about 3900 words are yet processed. I will then Post the info about the results as soon as its finished.

Now there are two things I alreday could find out during the test with smaller word list, if those two can be improved that would make the code even more valuable for the solution of my problem:

1- to find more accurate results as you have mentioned
1 should not be equal to 10; love not equal to lovely...

2- If a word from ws-1 exists in many rows of ws-ref then those 3 should be chosen which are the least times already used (have the smallest col. D value). The current code choses those which are already used many times and that while there are many others where also the searched word exists and are not used yet at all. May be because the code now choses the first found results and not the least used. If this can be fixed, then we will have a larger variety of references.

Thank you very much.

[wali]

Hi,

the whole list of 12000 words was processed succesfully. It took around 37 hours. But the two issues which i mentioned in my last post remain: accuracy and too oft usage of the same reference though other not used exist.

[JBeaucaire]

OK, first an apology, a simple line of code at the top may have cut the processing time down by HOURS. I've added them in now.

The accuracy thing has been added. Since all the words being searched for exist IN the data with a dash "-" after it, by adding the dash to the search/match function, we keep Word1- from matching Word10-. That should solve that and it's done in the code so you need not adjust your search list.

I've made an attempt to resolve frequency issue. Originally, each "loop" started at the top of the "ws-ref" page, so the first matches were found again and again. Now, we are starting each search loop from the point the last match was found in the prior loop so it should keep searching its way down the sheet.

I'm not 100% sure of the distribution effectiveness, but my tests were encouraging. Worth trying it out.

I also fixed a coding error that would have built the list on the wrong sheet based on which sheet you had active, now it will work correctly regardless of which sheet is active when you start.

Please Login or Register  to view this content.

[wali]

Hi,
i had forgotten to mention in my thread and posts that the excel file i am attaching is just a simplified example. The real strings of ws-ref are normal poem parts and normally contain only spaces.
But i solved that problem by replacing each "space" in sw-ref through "-". It should now work properly. I have started the macro and will post the results as soon as its finished.

Thanks a million times.

[JBeaucaire]

Even better would be to leave your data as is and change the macro to search for "word" + "space" like so:

Please Login or Register  to view this content.

[wali]

ok! i did it
may be that way it will be little faster cus now i removed over 1.8 million "-"s. :-) I started the macro and will come with results as soon as its finished.

There is another issue which really gives me hard time and i realized it now after running the macro:

Is it possible to copy content of col. C,D,E & F from ws-ref instead of A,B,C and D? Word from ws-1 should be searched as earlier in A and B. But copied should be the cells from C,D,E & F.

Why this?

Because, I am using this macro for a language where in older literature one letter was used for four different sounds and vowels.

"ی" was used for "i", "e", "ai", "ie"

But now there are five diferrent letters for that "ی","ي","ئ","ې","ۍ". The ws-1 has completly the new 5 different letters but as ws-ref contains of older and new literature, it some times differ between these letters and sometimes not. So it has old and new letters.

Now i want to solve it by copying col. A,B ws-ref to ws-ref col. E,F. I want to change then all five different letters to one in col.A and B. The same thing i want to do with col. A ws-1. Now i would need the code to search in A,B but copy C,D,E,F instead of A,B,C,D. I will then replace the whole col.A ws-1 with original data after macro is run completely. According to this solution i will have may be more wrong results but it still will be better then missing many right ones.

So, is it possible to modify the code in such a way that E,F are copied instead of A,B. But search is still done in A,B?

[JBeaucaire]

I'm hoping you're spending some time looking through this code and trying to see what each line does. By doing that, you could probably spot the line of code that is copying A:D to the other sheet and adjust it.

Please Login or Register  to view this content.

Change that to:

Please Login or Register  to view this content.

Hehe, and there's such a thing as too much information... I think this suffices:
"i would need the code to search in A,B but copy C,D,E,F"

=============
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated. It is found across from the "time" in each of our posts.)

[wali]

Wooow it works!!! cool
thank you veeeeeeeeeeeery much! thanks a million times! You are the guru!!thanks thanks