Phrase clustering using Excel

[shashankbansal]

Hi,

I would like to know if there is some formula or macro using which we can do phrase clustering on a spreadsheet.

Suppose i have a list of 10 statements/phrases each in a different cell of a column. Now there are 4 statements which refer to a common particular thing/feature/attribute and they are randomly placed among those 10 statements.

Is there a way to get these 4 statements (and likewise for other group of statements referring to respective common features) get clustered together?

Enclosing herewith an excel file depicting what i want (phrase clustering.xlsx)

I think the method to solve this will involve checking for common words across the statements and then bringing closer those statements which are similar/ talking about similar things.


Regards,
Shashank

[ben_hensel]

I noticed that the phrases you want to cluster all have spaces in them and the strings you want to push down don't have spaces. Assuming that is true for all your data:

I'd create a helper column with the formula

d4 = Iferror(search(" ", c4, 1), 0)
(if you're running before iferror became available, like 2003, then d4 = if(iserror(search(" ", c4, 1)), 0, search(" ", c4, 1)) instead)

And then just Sort based on column D

[shashankbansal]

Hi Ben,

Thanks for your response.

How shall we do it if all the phrases/statements have spaces between the words?


Regards,
Shashank