Hello,
I need to copy content of cells on column A to column B. But I want to avoid cells that contain repeated text. I have created a sample. In this sample I want to avoid to copy cells that contain more than 1 time word “apple”, but to keep cells that contain 1 time word “apple”.
I have uploaded a spreadsheet.
Thanks a lot.
doubt_20160420.1207_repeated words in a cell.jpg
I can only do that using a UDF.
insert this into a macro module then the formulais all you needFormula:
Please Login or Register to view this content.
Please Login or Register to view this content.
My General Rules if you want my help. Not aimed at any person in particular:
1. Please Make Requests not demands, none of us get paid here.
2. Check back on your post regularly. I will not return to a post after 4 days.
If it is not important to you then it definitely is not important to me.
Here is with a formula
in B2 and copy down
Formula:Please Login or Register to view this content.
v A B 1 colum with words column with formula 2 apple orange car apple orange car 3 apple pie apple 4 apple cat sun apple cat sun 5 screen apple pie screen apple pie 6 apple orange apple 7 screen apple apple 8 pie cat pie cat 9 orange apple pie apple apple 10 apple apple apple 11 screen apple face screen apple face 12 key screen key screen 13 apple day apple sun apple 14 day apple day apple
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Maybe this will work for you
Formula:Please Login or Register to view this content.
Apple has 5 letters so, if the SUBSTITUTE removes more than 5 characters, there is more than 1 instance of "apple" and that cell will not be copied.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
I interpreted a bit differently. This should take care of any repeated item.
Edited Correction to the formula.
Formula:Please Login or Register to view this content.
Last edited by FlameRetired; 04-20-2016 at 02:56 AM.
Dave
An afterthought.
"apples" <> "apple"
My above formula sometimes treats them the same.
This one takes care of that. It's array-entered.If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.Formula:Please Login or Register to view this content.
All solutions work perfectly. Thanks to everybody!
You're welcome. Thanks for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks