how to combine phrases - remove duplicates - and remove words with specific length
The problem I have is simple for a human to solve, but I'm having a hard time figuring out how to make excel do the same thing.
I have phrases in each cell B2:F2
...and what I want to do is the following:
take all of the individual words between B2:F2
remove duplicate words
remove words under 4 characters long
take remaining words and post one word per cell between I2:R2
I have en example in the excel file that shows the outcome I would like to have excel give.
Thank you for the help
Last edited by niktodorov1; 10-19-2018 at 02:34 PM. Reason: edit title
title updated
Last edited by protonLeah; 10-19-2018 at 05:44 PM.
Ben Van Johnson
Here is how I would do this:
B4 =TRIM(MID(SUBSTITUTE(B$2," ",REPT(" ",LEN(B$2))),(ROWS($1:1)-1)*LEN(B$2)+1,LEN(B$2)))
Drag through F4 then down as far as needed. I went through row 10.
H2 =INDIRECT(TEXT(MIN(IF((B$4:F$10<>"")*(COUNTIF(H$1:H1,B$4:F$10)=0),ROW($4:$10)*100+COLUMN(B:F),7^8)),"R0C00"),)&"" Ctrl Shift Enter
Drag down as far as needed. I went through row 20.
J2 =IFERROR(INDEX($H:$H,SMALL(IF(LEN($H2:$H100)>3,ROW($H2:$H100)),COLUMNS($A:A))),"") Ctrl Shift Enter
Drag to the right as far as needed. I went through column S.
See attachment.
Source:
https://www.extendoffice.com/documen...e-columns.html
I'm not sure how I would apply this on bigger scale once I have multiple rows of phrases that would need to be transformed as well.
This is very long formula, other might have shorter one.
I2 press Ctrl+Shift+Enter then drag to R2 and drag down
Formula:
Please Login or Register to view this content.
Much simpler solution (compared to post #3):
H2 =TRIM(MID(SUBSTITUTE(B$2&" "&C$2&" "&D$2&" "&E$2&" "&F$2," ",REPT(" ",1000)),(ROWS($1:1)-1)*1000+1,1000))
Dragged down as far as needed.
J2 =IFERROR(INDEX($H2:$H100,MATCH(0,IF(LEN($H2:$H100)>3,COUNTIF($I2:I2,$H2:$H100),""),0)),"") Ctrl Shift Enter
Dragged to the right as far as needed.
See attachment.
Edit: I just saw post #5. Very nice. I would go with that solution over mine.
Last edited by 63falcondude; 10-19-2018 at 02:44 PM.
thank you that works perfectlyOriginally Posted by Bo_Ry
This is very long formula, other might have shorter one.
I2 press Ctrl+Shift+Enter then drag to R2 and drag down
Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks