Solved

Formula for excel

Posted on 2014-03-23
5
49 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 250 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 250 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
This video walks the viewer through the process of creating a watermark for their document, customizing it, and saving it for viewing/printing needs.
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…

738 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