?
Solved

Formula for excel

Posted on 2014-03-23
5
Medium Priority
?
66 Views
Last Modified: 2016-06-04
Hello! Help me please, to find a formula for excel, which takes all the words in the text (for example, text from column A) and gives all the words from the text without repeating in a column B.

For example,

Column A      
Text       
      
Although simplicity is a virtue, theories regarding pedagogy do not work in practice if they are black and white. To say that the best way to teach is only to praise positive actions and to ignore negative ones is like saying that strawberries reduce one’s risk for cancer so people should cut apples out of their diet and only eat strawberries. In both situations, there does not have to be a choice.       



Column B - Words from text
       
        although

      simplicity

      virtue

      theories

      regarding
      …....
      ….....
0
Comment
Question by:victorya
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 2

Accepted Solution

by:
smksa earned 1000 total points
ID: 39948677
Hi,

I found it might be done in two steps:

1. put your all text in notepad file, then in Excel use Data option from get data to txt file, use all default options for next, next until finish.

Now data comeup, every word in one column

2. copy all these columns and use paste special option, Transpose, now all data will be according to your demand. :)
0
 

Expert Comment

by:RandomStu
ID: 39948823
Is your original text in a single cell, or in multiple cells going down col A?

Do you want your result in a single cell, or in multiple cells going down col B? Are you asking for a single formula that can be copied to multiple cells in col B to provide the answer, or what?

This problem can be easily solved using a custom function, or a procedure ("macro") created with Visual Basic for Applications (vba). Are you familiar with vba? If I provide a vba procedure to solve your issue, is that acceptible?

What version of Excel are you using?
0
 

Author Comment

by:victorya
ID: 39980160
Honestly, I just need to figure out how to  correctly use formulas (like =VLOOKUP() or =MATCH() ) in order to compare array of words in column A with array of words in column B and a return difference between them.


For example

Column A      Column B       Column C    

Although      positive              praise                
actions          praise                
positive         actions                  
motion           motion
0
 

Assisted Solution

by:RandomStu
RandomStu earned 1000 total points
ID: 40026296
Start with a blank sheet. In A1:A4 enter

Although
actions
positive
motion

In B1:B4 enter

positive
praise
actions
motion

In C1 enter

=ISERROR(MATCH(B1,A:A,0))

then copy down through C1:C4.

A "true" in col C indicates that the item in col B (of the same row) is NOT found anywhere in col A.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question