Quick Links - Also see the menu above and more choices on the right side of the blog (too much, but all good stuff)

\/ ...and now BIMbuilder.com Blog Posts... \/

Monday, May 9, 2011

A little formula search engine for Excel and Revit

I don't know if this will help you in any way, but since I just created it, I know someone out there will benefit from it.

Essentially, I had an Excel spreadsheet with a column of items.  I'm trying to find which cells have the text I'm looking for.  When I find that text, I want a specific word so I can add up the cells with the new text.  Part of the problem is that there are multiple similar words and I want to have a new word substitituted to match my needs for the next step.

I've done this in the past with the Find and Substititue commands in Excel.  Part of the problem is that I need multiple rows to create the formulas needed.  I got an idea about what if there was a way to have one cell at the top with the phrase I was looking for and the new word to used if I found that word.  Well, it took about 10 minutes to create what I needed.

Here's a sample of the table I made.  


1 A B C D E F
1 Description RESULT-A/WORD-A B/WORD-B Cool/IT WORD-/WORD- Got Text?/Word-
2 TEXT WITH WORD-B IN IT B Cool WORD- Got Text?
3 TEXT WITH WORD-B IN IT B Cool WORD- Got Text?
4 TEXT WITH WORD-A IN IT RESULT-A Cool WORD- Got Text?
5 WORD-C IN THIS ONE WORD- Got Text?
6 DON'T HAVE THE TEXT IN IT Cool
7 TEXT WITH WORD-B IN IT B Cool WORD- Got Text?
8 TEXT WITH WORD-A IN IT RESULT-A Cool WORD- Got Text?


Here's the formula you would put into cell B2
=IFERROR(VALUE(IF(IFERROR(IFERROR(FIND(RIGHT(UPPER(B$1),IFERROR(LEN(UPPER(B$1))-FIND("/",UPPER(B$1)),"")),UPPER($A2)),"X"),"X")<>"X",LEFT(B$1,FIND("/",UPPER(B$1))-1),"")),(IF(IFERROR(IFERROR(FIND(RIGHT(UPPER(B$1),IFERROR(LEN(UPPER(B$1))-FIND("/",UPPER(B$1)),"")),UPPER($A2)),"X"),"X")<>"X",LEFT(B$1,FIND("/",UPPER(B$1))-1),"")))

Then you can copy it to each additional row and column.
In column A is the text to search for.
At the top of Columns B through F are 3 items.
The word on the left is what we want to see as a result.
Then we have the "~" that is used to separate what's on the left and right.  If you had a "~" in your Column A, you'd need to change the Find("~" at the end of the formula to a different character and change the character at the top of each row.
The word on the right is the word or phrase we're searching for in Column A.

The reason for Columns B - F is to search for multiple different words or phrases.

How could you use this?  Say you had a long list of phone numbers.  You want to find only the ones with a certain area code.  You could have the result be the original area code, or use "1" so you can get a sum later (but you may need an extra column next to the numbers with =VALUE(D2) to convert the 1 from text to an actual number. Never mind.  I just adjusted the formula to convert a number to an actual number so you can get a SUM() calculation. 


Description 954-/(954) 954-/954- 1/(954) 1/954-
(305)555-1212
(407)555-1212
(954)555-2222 954- 1
(954)555-2222 954- 1
954-555-1212 954- 1
(954)555-1212 954- 1
954-555-1212 954- 1
954-555-1212 954- 1
Subtotal 3 3
 
Or...use it to validate good text and bad text.  You can always sort your columns so you're good results are at the top or bottom.


Description OK/(954) X/954-
(305)555-1212
(407)555-1212
(954)555-2222 OK
(954)555-2222 OK
954-555-1212 X
(954)555-1212 OK
954-555-1212 X
954-555-1212 X
 


 Any questions?  You can email me if you do.  I'm available for Excel consulting services.  Please let me know if this helped you in any way.


1 comments:

mthurnauer May 9, 2011 at 4:47 PM  

Why wouldn't you use quick filters or VLOOKUP?

  © Blogger template ProBlogger Template by Ourblogtemplates.com 2008

Back to TOP  

[Valid Atom 1.0]