# Similar Texts With Minor Differences - How To Get Just ONE (standard One)

May 26, 2014

I have a very large database that contains names with minor differences on column A such as:

A
EXCEL AMERICAN COMPANY S.A.
EXCEL AMERICAN COMPANY SA.
EXCEL AMERICAN COMPANY, S.A.
ASSOCIATION, INDUSTRIAL
ASSOCIATION INDUSTRIAL
L.A. COMPANY CO
LA COMPANY CO

As you can see, this makes a mess when dealing with numbers associated with them and makes analysis more difficult.

What I've been trying to figure out is how to quickly get in column B a standard name for each one, for example:

B
EXCEL AMERICAN COMPANY S.A.
ASSOCIATION INDUSTRIAL
L.A. COMPANY CO

I was thinking of using a combination of the FIND and EXTRACT formula or maybe LEFT as well. (Maybe VB?)

## Having A Minor Issue With SUM And AVERAGE

Nov 16, 2008

I am having a bit of an issue with SUM and AVERAGE of a particular column. The sum is giving me zero total, and the average is giving me a DIV/0 error. Have a look at the attached spreadsheet. I am trying to sum and Average column "I", but as you can see, it is giving me a total of zero, and an average of "DIV/0" error. Also min and max calculations are not working for that column either. Obviously it has something to do with the formula or formatting in column I, but I am not sure what or why... The formula in column I works as I like it to.

## Adding Weight Major And Minor

Sep 15, 2014

I have two columns. One with the major weight(pounds and one with minor with ounces. The first column would have a 1 the second 8 for 1 lb 8 ounces which I need to combine to say 1.5 Every way I do it it is coming up with 10.5.

## Insert Photo VBA Minor Adjustment

Jun 29, 2009

I have Office 2007 and i use this code on my word.docm to insert selected photos. the problem I'm having is that it insert photo at top of page. can additional code be added so that it will insert photo in same table as command button. and in front of button, so that it will hide button

Private Sub CommandButton1_Click()
Dim sFileName As String
Dim ilImage As InlineShape
.Display
If .Name "" Then
sFileName = .Name
Set ilImage = ThisDocument.InlineShapes.AddPicture(sFileName, , True)
With ilImage
'set any additional properties such as left, top, etc., here
End with
Else
Exit Sub
End If
End With
End Sub

## Formula / Algorithm For Identifying Minor Changes In Text Strings?

Feb 17, 2012

I have a database of approximately 250000 email addresses and I would like to see if there is some way to use Excel to analyse the strings in order to identify potential 'non-unique' individuals.

For example, if I have the following email addresses:
12345@example.com
12346@example.com
12347@example.com
or
example1@example.com
example2@example.com
etc

Is there some way of extracting common text strings, lets say where there is a difference of 1 character / digit between strings (rather than alphabetising and searching manually)?

## Vba Shade A Cell Once Its Moved ( And Add New Rows Beneath ) Minor Code Adjustment

Nov 7, 2009

I've adjusted a jonmo code to add an item in col B which is not in col A to the bottom of col A. - fab code, thanks jonmo.

But.. i want to:

insert rows beneath those in column A to accommodate the added items and shade those cells in list A once they been added ( so the users now they've been moved )

I've posted the code below ( including my attempts at colour change where it shade the right cell but in the wrong column ) ...

## Sum Value From Texts

Oct 14, 2009

I have the following in cells a1,a2,a3 & a4 as follows:

A = 14
B = 15
C = 16
D = 20
Total = 65

I need to sum the total of the above, a,b,c & d and show the total. How do I add only the figures and get the total?

## Sum Of Numbers Between Texts

Apr 29, 2009

I've a cell that contains numbers and texts and I want to sum the numbers and get a result in another cell.

This is an example for my cell:

arando-3,12; hegymeg-4,3; vas-7; aranyoska-1,8

and the result: 16,22

## Substituting Texts From Specified List

Dec 1, 2009

Got the following data in different cells:

And on a different sheet in the same workbook, have 45 different values to be converted:

What is required?

Knowing that using a VLOOKUP function, i can compare a value in the first cell to the list and return the desired value, i need to substitute the strings of characters with those the the list of 45 values so that i can proceed with my work.

Basically, for the 1st Cell for eg..

2232-2232-

I will need a formula to replace "2232-" by the value which a VLOOKUP function will give when comparing to the list of 45.

Result shall look like below:

20-20-

Looks simple when have the same set of characters in the cell, but for a cell which has multiple sets:

2210-22G1-45R1-45G1-20R1-20GP-
It becomes difficult for me. What i need exactly is:

Formula to substitute using the vlookup function so that it performs a lookup of the value in the specified cell with the list of 45 values and returns the value (20- or 40-) to be substituted in the cell. What i need finally is a set of 20- & 40- replacing the sets of present data.

## Hyperlinking Texts In Hierarchy Chart?

Jul 2, 2013

I have created a hierarchy chart using smartart. Is there a way to hyperlink the texts in each box to other sheets in the workbook?

[URL]

## Create A Formula Where Using Different Texts To Display A Value

Jan 11, 2010

I would like to create a formula that allows the destination cell display a number depending on a word that is typed in the source cell. The source cell and the destinating cell will remain the same two cells.

example:A2=IF(A1="WORD1",1.5,0)

If i type WORD1 in A1 (source cell) then A2 (destination cell) will display 1.5, but how do i make this formula work for a multiple of different words.

example:A2=IF(A1="WORD1",1.5,0 IF(A1="WORD2",3.0,0 IF(A1="WORD3",2.5,0)))

this formula doesn't work, but is basically what i am looking for the formula to do - WORD1 displays 1.5, WORD2 displays 3.0, WORD3 displays 2.5 etc where the source cell and the destinating cell remain the same two cells, it is just the word and its value that change.

## Delete Rows That DO NOT Contain Criteria Texts

Dec 15, 2012

I am looking for a macro to delete rows that DO NOT contain criteria. But what I could achieve so far is deleting all rows except the first word ie ACC here. My code so far is as follows

[Code] ..........

## Combined Numbers And Texts Without Delimiter

Aug 23, 2012

My excel sheet contains inventory of products and quantities, each is in a form of combined number followed by text without a delimeter. For example 2AWS means Two (2) of AWS.

Each cell in my excel database may contain pair of products, each is defined in the above form but the pair of products are separated with a comma.

I need to calculate the total number of each product and split in two different cells.

For example:

Cell A1: 3GFG, 2AWS
Cell A2: 2NG, 1AWS
Cell A3: 1NG, 4GFG

Desired Output
==============

GFG7 (i.e. Cell A5: GFG , Cell B5:7 etc.)
AWS3
NG3

## Concatenate Function- Two Texts In Two Cells

Aug 1, 2008

I have two texts in two cells. e.g in A1 I have JOHN, and in B1 I have SMITH.

I need to cocatenate these two texts in a third cell (=CONCATENATE(A1, B1) /or I can use = A1 & " " & B1). It's fine till I concatenate.

But I need the output in the following format:

JOHN SMITH

The second text needs to be in ITALICS.

## Extracting First Number From Numbers And Texts In Cell

Jun 25, 2014

We are changing management systems here and in order to do so we need to provide the names and addresses of over 400 different people. Our current management system stores people's addresses as a whole for example; 32, Example Street, Town, County, Postcode.

I cant extract just the numbers because I only need the house number and not the postcode.

After searching several forums I thought I had solved it with this.

Cell A1 is the extracted address from current management system reading 32, Example Street, Town, County, IU13 8TY

Cell B1 is =LEFT(A1, SEARCH(" ",O15,1))

Cell C1 is =SUM(MID(0&B1,LARGE(ISNUMBER(--MID(B1,ROW(INDIRECT("1:"&LEN(B1))),1))*ROW(INDIRECT("1:"&LEN(B1))),ROW(INDIRECT("1:"&LEN(B1))))+1,1)*10^ROW(INDIRECT("1:"&LEN(B1)))/10)

That only gives me the number "3" though and I need 32.

Basically A1:A400 will be addresses as above and in cell B1:B400 I need just there house number at the start.

## Conditional Formatting Based On Numbers And Texts?

Jun 27, 2014

I have column A with Different status like Open, closed, available etc. Column B have numbers. i am looking for a formula in condition formatting for column B.

If the cell value in column B is 2 or more, the same cell should be highlighted in Green.

If the cell value in column B is 1, the same cell should be highlighted in Yellow.

If the cell value in column B is 0, the same cell should be highlighted in Amber.

If the cell value in column B is less than 0, the same cell should be highlighted in Red.

If the cell value in column A is "Available", cell in column B should be highlighted in Amber.

## Search For Occurrence Of Multiple Texts In Same Row / Cell?

Jul 12, 2014

I have a lookup table in which names of students and the planned session dates are available.

I need to create a chart that has Student Name and Each date of month. The chart should have value of 1 wherever a match is found in the look up table.

I am wondering if combination of searching for multiple strings (student name and date ) will work somehow. The below image explains my problem

StudentTimeTable.png

## Remove Numbers From Texts In Excel Column?

May 7, 2013

I have excel data where it runs like below:

Lumina
006
Ali Rashid Al Amin
61.40
Date
03/04/2013
11/04/2013
14/04/2013
check
20
Raja

I need a result column containing as below but where the numbers are removed those cells has been

Lumina

Ali Rashid Al Amin

Date

check

Raja

## Search For Different Texts In Column And Copy To Another Cell

Jul 29, 2013

What I have: (Table1) A table with multiple columns. One column consists of cells that can contain different types of information in a non-consistent way. One thing is for sure: if a cell contains data, it will always contain a name of a person. E.g. a cell in that column could contain the following: "838374987343 based on 98989 John 990999", or "000000000john", or "William 39843984 more text and even more text"

(Table2) A separate table that contains the names of people in a column, e.g. "John", "William"

What I want:A formula that searches for the names (mentioned in Table2) in Table1 and copies this name in a new column.

Result:A new column with the copied names as content.

## How To Split Texts Into Words (collecting Word And Compounds)

Jan 10, 2008

i have wordlists which i would like to enhance through texts. For that purpose i need a macro which reads a text and splitts it into words and put the words into three columns. Lets assume the column where the words are supposed to be put is columns A,C and E. The column A should contain single words containing no spaces from Text, column C should contain compound words which have one space between them and in column E those compounds which have two spaces. Now i will put my existing wordlist in column G before i start splitting texts. The macro should put only those words from text into column A,C and E which dont already exist in columns A,B,C,D,E,F and G. In Column B i will place the corrected or checked words from column A. In column D i will type the corrections and checked versions of those compunds existing in column C. And the column F will contain the compound words which are corrected and checked from Column E.

Column A--> single words splitted from text through macro (containing no spaces)
Column B--> corrected and checked versions of words from column A through user
Column C--> compound words containing one space splitted from text through macro (for example:"etwas möchten" or "do something")
Column D-->corrected version of C through user
Column E-->compound words containing two space splitted from text through macro (for example:"sich uerberzeugen lassen" or "down in dumps")
Column F-->corrected E
Column G--> existing dictionary wordlist through user

Signes like ".", ":",";", "/" or quotation signe itself should be seen as seperator. And the words or compounds splitted from the text should not contain these signs.

The text will be put in another sheet in the same excel file. Lets call that sheet "text". The macro should search for words through whole sheet ignoring empty cells or columns.

None of the columns should be filtered or ordered. The columns shound not be deleted as well. That way the user wont have to correct the same word each time cus the corrected as well as the original words will be there.

## Calculating Frequency Of Unique Texts Within Month Under Certain Criteria

Feb 25, 2013

I need to get a general formula which if possible, automatically calculates the frequency of unique texts (no duplicates) under a particular month (January) without manually inserting the cells which is under january. If possible, the formula will also check to see if the company is either Cash or Cheque.

I have three columns; Date, Company name and Payment mode.

Date ranges from 1 jan 2010 to 31 dec 2012. Cell A2 to A234

## Color Cells For Some Specific Texts In Excel Sheet

Feb 16, 2014

I want to color the cells for some specific texts in RED, and some in BLUE and some in GREEN.

For Eg: I want to color the cells in RED, which contains below specific texts in a sheet.
UNIX
LINUX
MS OFFICE
POWER POINT....like this for nearly 100 specific texts.

and I want to color the cells in BLUE, which contains below texts in a sheet.
JAVA
.NET
SQL
MSDOS....like this for nearly 100 more specific texts.

If i create a rule in conditional formatting for each text,I have to create nearly 400+ rules for my task now, which is not possible.

## Fill Cells With Some Specific Texts Based On Multiple Conditions

Jun 18, 2014

I have a worksheet ("ALL JOBS") with huge row data and the row data will vary every day. My problem is there will be a lot of blank cells which I need to fill manually and its tacking lot of time of mine.

Code to fill these blank cells automatically with some specific texts based on the attached conditions.

I have attached the workbook for more details. FillCellsTest.xlsmâ€Ž

## Combined Numbers And Texts Without Delimiter - Split And Perform Calculations

Aug 24, 2012

My excel database sheet contains inventory of products and quantities, each is defined in the form of combined number followed by text without a delimiter. For example 2AWS means Two (2) of AWS.

Each cell of the inventory field may contain up to two products, each product is identified in the above format; but the pair of products are separated with a comma.

The inventory field is occupying from cell \$A\$2:\$A\$5000; and is defined as a named range "INV_QTY" with scope within the entire workbook. I need to calculate the total inventory for each product.

Here's an example:

Cell A2: 3GFG, 1AWS
Cell A3: 2NG, 2AWS
Cell A4: 3AWS
Cell A5: 4QLK, 4GFG

etc.

The desired output should be calculated as follows (assume Input will be on Column C & D)

Product - QTY
GFG - 7 (i.e. Cell C2: GFG , Cell D2:7 etc.)
AWS - 6
NG - 2
QLK - 4

## Texts Automatically Change The Currency Depending On The Choice Made In The Previous Userform

Jul 3, 2008

I have options buttons in a userform, first is "\$" the other is "€"

When somebody chooses one of them, it writes the choice to a cell in the data sheet. But when clicked on the next userform I want some of the texts automatically change the currency depending on the choice made in the previous userform.

The formula below was is example

=""&Data!\$A\$2&" / m³"

=""&Data!\$A\$2&" / kWh"

## Value Differences

Nov 22, 2007

I have two colums with 1000 rows for column 1 and 500 for column 2 that shows gps coordinates. I want to work out the distances between the gps coordinates but want the answer only if it is less than 30 kilometres.The formulae must do the following

a1-b1 and compare the answer to a1-(b2:b1000) and give me the smalles answer in column c.

I want the closest gps coordinate to A1. Even if i do vlookup first there might still be a closer gps coordinate in row 501 than just subtracting A1-B1

## Dates And Differences Between

Jun 8, 2009

I have a list of 109 dates in one worksheet (call it A) and in another worksheet I have a list of other dates (call it B). I need list B to compare itself to list A and find the closest date in list A and then subtract the difference between the two.

## Summing Differences

Apr 13, 2007

1. I need to find the average monthly meter reading say for the whole year...this is what I have, =(C2-B2)+(D2-C2)+(E2-D2)+(F2-E2)+(G2-F2)+(H2-G2)/6 is there an easy way to do this?

2. Also need to get the total monthly reading of all the machine, the formula I have is; =C2-B2+C3-B3+C4-B4+C5-B5+C6-B6

## Counting Differences Between Column A And B?

Feb 27, 2014

is there an IF formula to compare values in two columns, say Column A and Column B have "Action recommended" and column B has "Actual action taken", both columns should have the same texts, in cases where there is no similarity, the formula should count the cases.

## Matches And Differences Between Two Lists

Sep 21, 2009

I am trying to compare matches and differences in two lists in order to pull statistics from them.

I have a production schedule I make at the start of the week (list 1) and compare that to what I actually ran at the end of the week (list 2). I would like to produce the following from these two lists;

- a list (list 3) that shows all the orders I didn't run in my original schedule
- a way of summing the number of matches (of work order #s) in the two lists (list 1 and list 2)
- and way of extracting the corresponding machine hours I did / did not run from the two lists

I have provided text boxes on the attached excel file to better explain my problem.