keyword, matching and breaking up??

Hi

How about example file showing your setup, and expected results for example data.

rylo

hello again rylo

sure thing, I have populated dummy data in "sheet2" and "sheet3" .... i have simulated a search and put the searches result in the 'Results' WS.

I have put some dummy cross reference data in the 'Cross Ref data' WS in which the user would click the button to start the cross reference.

I have simulated manually what the expected output would be in the 'Cross Ref Results' WS

Hi

Want to explain how those results in Cross Ref Results were obtained? You have entries for wap7, will and work, but those words don't appear in the Cross Ref Data listing.

Also the headings found in both and Found in only Results. How are they determined? As you only want to reference Results for your search where does the both come into things? The source sheets? OR both columns D and E?

rylo

Sure,

your comments:
You have entries for wap7, will and work, but those words don't appear in the Cross Ref Data listing.

ans: I need the search to reverse as well.. so it needs to check also if what is in 'Results' (which is actualy a databases tables entries) and what you enter in 'Cross Ref Data' (which is what you expect the database to have, but it doesnt always hence the ability to check from the other direction).

So you are correct, wap7, will and work appear in 'Results' but not in cross reference. This means that possibly the database as incorrect entries as they do not match up with what the user thinks should be in..


your comment2:
Also the headings found in both and Found in only Results. How are they determined? As you only want to reference Results for your search where does the both come into things? The source sheets? OR both columns D and E?

- As you only want to reference Results for your search where does the both come into things?
ans: this means that there is a match in both the 'results' & 'cross ref data' WS's when comparing Column D and E.

Found only in results: means it appears in the results sheet in column D and\or though you didnt not enter it in the 'cross ref data' WS in column A.

hope that helps rylo, let me know

This must be a tricky one! I had a feeling it could be a hardy

Hi

OK, first cut. Lets see. Also had to keep an eye on the cricket scores....

Please Login or Register  to view this content.

rylo

thanks rylo,

from what you coded it almost did every thing that the coded intended. the only thing that happens which should is that one of the headings appears twice -- i have the out put in the attachment.

Also which im sure you know, the last check which i know isnt in your first draft which is "only appears in cross ref data"


when the 2 things are above are done then i think it will be 100% what i need!!

Hi

Oops - missed the bit about the items not found. Think I have the headings resolved. Remember that they have to come from the results sheet headings.

Please Login or Register  to view this content.

rylo

got an error at this point:

Please Login or Register  to view this content.

its right down the bottom when it gets to an item in the 'cross ref data' data WS that isnt in the 'results' WS.


Also that funky heading issue is still there. I think it is having issues with the last entry and is duplicating it and then creating a new header. (exact same as the last excel doc i attached)

Hi

Can you send the file you are testing this on, as I am not getting any problems with the original example file.

rylo

this was the error: "the information cannot be pasted because the Copy area nad the paste area are not eh same size and shape."

sure thing

Hi

Here goes for another one. There were a couple of differences between the original file and the one you have used for testing. The header names were different and I was using them as a reference point, and this one only had 1 "NOT FOUND". I should have covered that! but got caught up with the multiple items.

See how this goes.

Please Login or Register  to view this content.

rylo

Thanks a heap! this is great.

Working 100% except for the results duplicated on the results page. I think because it is checking in both columns for a match. I was hoping that if it founds a match that it would move to the next item to find, or, if it found 2 matches that it could be removed. thats okay though I think, I can try and use the remove duplicate sub to fix that up.

Thanks a always rylo

Hi

I'm surprised about the duplicate. What it is doing is looking in column D for a match. If it finds one, it marks it as found then moves on. If it doesn't then it looks in column E. If it finds one, it marks it as found then moves on. If it doesn't then it marks it as not found.

Got an test file that is producing duplicates so I can have a look???

rylo

hey rylo,

I just figured it out..

its column A and B... the name columns.

If the name in Column A is 'Sarah' and in column B on the same line is 'Sarah.com' then both of the lines are copied.

But it looks like it finds for example 10 matches in column A then loops and finds 10 matches in column B.

ive just mad a dummy example of test data attached and have also attached the master document.

Hi

I've changed sub aaa to removing the casing differences for the INSTR function. See how this goes.

I've also commented out the call to the progress bar as it isn't really doing what I think you want to do. All it does is run AFTER all the processing has been completed, not AS the processing is being done.

rylo

Please Login or Register  to view this content.

lol - i did notice the progress bar wasnt doing much, but it did look nice, hehe I guess it slowed the sub down though.

Thanks so much for all your help ... it worked like a charm. im thrilled