?
Solved

Formula for excel

Posted on 2014-03-23
5
Medium Priority
?
116 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
  • 2
4 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

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Outline Suppose you have some simple text based data in Excel that you would like to display as a PowerPoint presentation. Of course it would be possible to write some fairly complex VBA code that created a new slide for each line of the Excel data…
When asking a question in a forum or creating documentation, screenshots are vital tools that can convey a lot more information and save you and your reader a lot of time
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

589 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