Group rows based on similar text

%3CLINGO-SUB%20id%3D%22lingo-sub-2457308%22%20slang%3D%22en-US%22%3EGroup%20rows%20based%20on%20similar%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2457308%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20very%20large%20spreadsheet%20with%20one%20column%20that%20start%20with%20similar%20text%20but%20pretty%20much%20every%20line%20is%20slightly%20different.%20Below%20is%20a%20rough%20idea%20on%20what%20I%20mean%20(image%20two%20columns)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EID%26nbsp%3B%20%26nbsp%3BSoftware%3CBR%20%2F%3E1%26nbsp%3B%20%26nbsp%3B%20Adobe%20Acrobat%201%3CBR%20%2F%3E2%26nbsp%3B%20%26nbsp%3B%20Adobe%20Acrobat%202%3CBR%20%2F%3E3%26nbsp%3B%20%26nbsp%3B%20Adobe%20Acrobat%203%3CBR%20%2F%3E4%26nbsp%3B%20%26nbsp%3B%20Adobe%20Flash%201%3CBR%20%2F%3E5%26nbsp%3B%20%26nbsp%3B%20Adobe%20Flash%202%3CBR%20%2F%3E6%26nbsp%3B%20%26nbsp%3B%20Adobe%20Flash%203%3CBR%20%2F%3E7%26nbsp%3B%20%26nbsp%3B%20Google%20Chrome%201%3CBR%20%2F%3E8%26nbsp%3B%20%26nbsp%3B%20Google%20Chrome%202%3CBR%20%2F%3E9%26nbsp%3B%20%26nbsp%3B%20Google%20Chrome%203%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20want%20to%20group%20these%20based%20on%20say%20the%20first%20two%20or%20three%20words%20in%20each%20row.%20So%20looking%20at%20the%20above%20example%20I%20would%20want%20a%20group%20for%20Adobe%20Acrobat%2C%20a%20group%20for%20Adobe%20Flash%20and%20a%20group%20for%20Google%20Chrome.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlus%20I'd%20like%20to%20use%20subtotal%20function%20also%20to%20get%20a%20count%20of%20how%20many%20in%20each%20group.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20issue%20is%20I%20don't%20know%20how%20to%20get%20it%20to%20group%20based%20on%20part%20of%20the%20text%20e.g.%20the%20first%20two%20word.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20help%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2457308%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2457449%22%20slang%3D%22en-US%22%3ERe%3A%20Group%20rows%20based%20on%20similar%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2457449%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F400114%22%20target%3D%22_blank%22%3E%40lfk73%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20create%20a%20helper%20column%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0517.png%22%20style%3D%22width%3A%20589px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F289520i741995ECBBCCBCDF%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0517.png%22%20alt%3D%22S0517.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EThe%20formula%20in%20C2%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DTRIM(LEFT(SUBSTITUTE(B2%2C%22%20%22%2CREPT(%22%20%22%2C255)%2C2)%2C255))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EThen%20click%20in%20the%20table%20and%20select%20Subtotal%20on%20the%20Data%20tab%20of%20the%20ribbon.%3C%2FP%3E%0A%3CP%3ESpecify%20that%20you%20want%20to%20add%20a%20subtotal%20at%20each%20change%20of%20Group%20(the%20helper%20column)%20use%20Count%20as%20summary%20function%2C%20and%20add%20the%20subtotal%20to%20the%20Group%20column.%20Then%20click%20OK.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0519.png%22%20style%3D%22width%3A%20257px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F289521i8F1D647FDA203A53%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0519.png%22%20alt%3D%22S0519.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EResult%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0520.png%22%20style%3D%22width%3A%20403px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F289522i8836772234C1223F%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0520.png%22%20alt%3D%22S0520.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2457482%22%20slang%3D%22en-US%22%3ERe%3A%20Group%20rows%20based%20on%20similar%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2457482%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F400114%22%20target%3D%22_blank%22%3E%40lfk73%3C%2FA%3E%26nbsp%3BThe%20three%20words%20in%20your%20post%20%22%3CSTRONG%3Every%20large%20spreadsheet%3C%2FSTRONG%3E%22%20would%20warrant%20to%20look%20into%20Power%20Query%20as%20a%20variant%20to%20the%20formula%20based%20solution%20with%20sub-totals%20as%20suggested%20by%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20since%20your%20example%20give%20only%20a%20%22rough%20idea%22%20of%20your%20real%20data%2C%20I%20might%20be%20wrong.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2457514%22%20slang%3D%22en-US%22%3ERe%3A%20Group%20rows%20based%20on%20similar%20text%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2457514%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F400114%22%20target%3D%22_blank%22%3E%40lfk73%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EI%20have%20a%20very%20large%20spreadsheet...I%20want%20to%20group...Plus%20I'd%20like%20to%20use%20subtotal%20function%20also%20to%20get%20a%20count%20of%20how%20many%20in%20each%20group%3C%2FEM%3E%3C%2FP%3E%3CP%3EAnother%20option%26nbsp%3Bassuming%20you%20run%20a%20Windows%20version%20of%20Excel%20%26gt%3B%2F%3D%202010%3A%20a%20Power%20Query%20%2B%20Pivot%20Table%20is%20attached%20(done%20w%2FExcel%20365)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20Excel%202010%2F2013%20the%20%3CA%20href%3D%22https%3A%2F%2Fwww.microsoft.com%2Fen-us%2Fdownload%2Fdetails.aspx%3Fid%3D39379%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EPower%20Query%20add-in%3C%2FA%3E%20must%20be%20installed%20first%2C%20paying%20attention%20to%20select%20the%20appropriate%20download%20(32%20vs%2064%20bit)%20according%20to%20the%20installed%20version%20of%20Excel%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a very large spreadsheet with one column that start with similar text but pretty much every line is slightly different. Below is a rough idea on what I mean (image two columns):

 

ID   Software
1    Adobe Acrobat 1
2    Adobe Acrobat 2
3    Adobe Acrobat 3
4    Adobe Flash 1
5    Adobe Flash 2
6    Adobe Flash 3
7    Google Chrome 1
8    Google Chrome 2
9    Google Chrome 3


I want to group these based on say the first two or three words in each row. So looking at the above example I would want a group for Adobe Acrobat, a group for Adobe Flash and a group for Google Chrome.

 

Plus I'd like to use subtotal function also to get a count of how many in each group.

 

My issue is I don't know how to get it to group based on part of the text e.g. the first two word.

 

Any help?

4 Replies

@lfk73 

I'd create a helper column:

S0517.png

The formula in C2 is

=TRIM(LEFT(SUBSTITUTE(B2," ",REPT(" ",255),2),255))

Then click in the table and select Subtotal on the Data tab of the ribbon.

Specify that you want to add a subtotal at each change of Group (the helper column) use Count as summary function, and add the subtotal to the Group column. Then click OK.

S0519.png

Result:

S0520.png

@lfk73 The three words in your post "very large spreadsheet" would warrant to look into Power Query as a variant to the formula based solution with sub-totals as suggested by @Hans Vogelaar 

But since your example give only a "rough idea" of your real data, I might be wrong.

@lfk73 

 

I have a very large spreadsheet...I want to group...Plus I'd like to use subtotal function also to get a count of how many in each group

Another option assuming you run a Windows version of Excel >/= 2010: a Power Query + Pivot Table is attached (done w/Excel 365)

 

For Excel 2010/2013 the Power Query add-in must be installed first, paying attention to select the appropriate download (32 vs 64 bit) according to the installed version of Excel

@Riny_van_Eekelen 

 

All relative I suppose.  The initial spreadsheet is 115k lines.  Is that large to you?  To me its the largest I've dealt with.  I crunch it down a lot in various ways.  However maybe the method you suggest would add extra benefits in the crunching that I am doing also.  However for now I don't have the time to invest is trying it out, just looking for a simpler solution to the grouping.