Help with Loop! Plurals and singulars

[cacos]

Hi everyone!

Hoping you can help me rethink/rewrite properly the following bit of code; scenario is the following:

In column "A" I have a bunch of words, singular and plural both, and next to each word (column "B") there's a number indicating the number of occurrences for each of them.

What I need basically is to find plurals and singular of the same word (like "House" and "Houses") in column "A", and leave just 1 word adding up the total occurrences for both versions (both plural and singular, House + Houses).

The plural/singular identification doesn't need to be very sophisticated, as long as it recognizes word+s it's ok (as in House + s = Houses).

So to sum it up, it could just merge in one word, let's say the plural version (Houses) and next to it in column B sum the total of occurrences for both.

Here's the bit of code (poorly written, I know) that I though of to identify singular/plural and add the plural version in column "E", counting the number of occurreces:

For Each CellA In Sheet2.Range("A2:A" & Sheet2.Range("A65536").End(xlUp).Row).Cells

LookV = CellA

    For Each CellB In Sheet2.Range("A2:A" & Sheet2.Range("A65536").End(xlUp).Row).Cells

    If CellB = LookV & "s" Then
           Sheet2.Cells(Rows.Count, "E").End(xlUp)(2).Value = CellB
    End If
  
    Next CellB
  

Next CellA

The rest of the code is completed with a vlookup to find the number, and an autofilter to remove the plural version from column "A".

Anyone think of an easier/better approach?

Thanks a lot!

 

[Nebu]

Hi:

What if a word end with "S" and if it is not plural but singular like News, bus ,gas etc...

 

[cacos]

Actually the objective is to identify if a word comes up twice (one in singular, one in plural), and if it does, sum the number of occurrences for each.

So if you have "House" with 4 occurrences, and "Houses" with 10 occurrences, the idea would be to have a single word for both that adds up to 14 occurrences.

It's basically to consolidate the number of occurrences for both plural and singular versions of the same word. Occurrences are listed in column "B" next to each word (words are in column "A").

 

[Nebu]

Could you please upload a sample file with some dummy data...

 

[cacos]

Attaching an example.

There will be far more words on the actual file, another problem about my previous approach is that it took some time to process.

You'll see in this example that some words appear twice (singular and plural, like House/s or Cat/s); the idea would be to sum the number of occurrences for both words in a single cell (it can be anywhere).

Thanks!

 

[NARAYANK991]

Hi ,

Can you not sort your list , so that when you comes across a word such as House , checking to see if the list contains the plural Houses will not need to rescan the entire list ?

The file you have uploaded is a file which contains the output ; you have shown us how you want the code to display the output ; for the purposes of developing code , it would be better if you could upload the file which contains the input data. Even if the file contains 100000 words , I don't think it can be that big in size.

Narayan

 

[Nebu]

Hi:

Please find the attached.

I have basically copied a function(sorry forgot to take the reference from where I copied the function from) and modified a bit to make all your words into plural, this way I guess you will be able to count the occurrences. I have not put the formula to consolidate the number of occurrences. I guess that will be fairly easy for you.However, the function is not fool proof it takes care of most of the cases, but if you come across anything unique you will have to add that case to the macro then it should work fine, the formula is in the column "C"..

Thanks

 

[cacos]

Thanks Nebu! I'll see now how I can consolidate the function with getting the end result.

@NARAYANK991, that's not the output, it's the actual data. You'll notice you'll find plural and singular versions of the same word in that list (House and Cat). The goal is to consolidate a single entry, leaving the word in either singular or plural, that sums the number of occurrences for both the singular and plural version.

So in the attached file, the result for "House" would be 161 (adding up "House" and "Houses"). The code would go through the list, identify the singular and plural version, and add up the number of occurrences for both.

Also, the list can be sorted or not, no problem there.

Thanks!

 

[cacos]

Hi Nebu, I've tried the function and it works great.

Can anyone think of a efficient way of handling the operation of consolidating both the plural and singular version of the word, and summing the number of occurrences?

Thanks!

 

[Nebu]

Hi:

Please find the attached, I guess this is what you are looking for, I have modified the Macro to accommodate more cases as well..

Thanks
Nebu

 

[cacos]

Yess sorry for responding so late but I wasn't approaching it the right way. Some rethinking plus a pivot table did it.

Thanks @Nebu !

 

[Nebu]

Glad you could solve it...