Sorting and grouping data according to one and two words of a Column

[terryhenderson]

Hi Guys,

I have worksheet1 that has data of 8 columns (A-H) and nearly 7000 rows. All data in are sorted according Column D which is also sorted alphabetically.

What I am after is a VBA script that will additionally sort data in Worksheet1 according to Column D, but in this time according to the first and second words of each cell in Column D.

As per uploaded example, all items in Column D that the same first word are moved and sorted in Worksheet2 with an empty row separating each group of data according to that first word.

The same is for Worksheet3 where all items in Column D of Worksheet1 that have the same first two words are moved and sorted with an empty row separating each group of data according to both first and second words.

Items in Column D of Worksheet1 with different first, and first and second words are just ignored to stay in Worksheet1.

Can someone generously help with this matter?

Thanks in advance.list_of_subjects_example.xlsx

[daffodil11]

Here you go:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[terryhenderson]

Thank you very much daffodil11 for your invaluable input with my question. In fact, as I ran the first script it paused with run-time error '5' message "invalid procedure call or argument". Then running the second script, it did as exactly as needed like magic. Is it possible that the script will move those rows from Worksheet1 to other worksheets instead of just copying?
My deep thanking to you for all your effort in this regard.

[daffodil11]

Perhaps I misunderstood some of the logic. Are all the 2-word matches going to sheet 3, with everything else going to sheet2?

[terryhenderson]

Thanks for replying. Actually, the final product after running the script will be three portions of data divided onto three working sheets: the one-word portion in WS2, the two-word portion in WS3 and non-matching or remaining odds will stay in the first worksheet fur further manipulation. I hope this will make it much clearer. Thanks very much for your cooperation.

[daffodil11]

Cool, I'll stay late and give it a shot.

Edit: Okay, that failed. Since we need to kill the data, the detection to determine if it's recurring breaks for the last occurrence. Maybe I'll try a scripting dictionary to save the row numbers.

example: if we cut Advanced Computer 1, 2, and 3, when it gets to the 4th one it doesn't see the multiplicity and so scraps it. HRMMMMMM!

[terryhenderson]

Yes, you're right. In this case I may suggest that we would have two versions of the first script (one word): one for copying and the other for deleting. First version to run first, where the product is WS2. Then the two-word script is run to move the selected rows to WS3. The last step is to run the second version of the first script to delete the records that were copied to WS2. Does this make sense? If not, please pardon my logic, I'm just trying to help : )

[daffodil11]

Give this a shot, took a laughable 3 hours to write because my VBA-fu is terrible.

It pulls the 2 word matches first to sheet three, and then consolidates the source. Then it pulls single word matches, and consolidates a 2nd time:

Please Login or Register  to view this content.

I shall endeavor to learn code arrays in the future; but here's the general logic:

It runs through and detects if the first two words of a cell exist as any other first two words. If so, it writes down the row number into a dictionary (imaginary spreadsheet) along with those two words.
When it completes, it then goes back through the dictionary and compares each set of words to the previous set. (The first set has no previous, but I coded around that). For a matching set it copies from the memorized row number, to the next line on the respective sheet. For an unmatched set it copies from the row number and skips a row at the paste-to location. Then it deletes that row and moves on.

It repeats in a similar fashion for the single word matches.

[terryhenderson]

Thank you very much for such genius code, with its genius logic. I wouldn't ever produce something like this even in ten years.
It ran smoothly and did as exactly as needed. It just paused on records with one word only, which I had to extract on another worksheet, rather than that everything went as desired. Now the product of running the code is ready for another manipulation thanks to your efforts.
You do have a very valuable input to this world, please keep it up as this world does need you : )
ALL THE BEST..

[daffodil11]

Thanks for your feedback.

It definitely pushed some of my skills to the limit and I learned a few new bits along the way, such as that the .Keys of a Dictionary is a collection.
After I studied up on some of its properties I was able to franken-code the rest of it together.