Highline Excel Class 13: Text functions & AMPERSAND

I am using the following formula
Code:

=IF((AE21-(AI21+AI23))

Hello,

I have a file with a situation similar to this:

value in the cell A1 is 1
value in the cell A2 is 1

The following formulas provide the following outcomes:

Formula = (A1>0 & A2>0) ; Outcome = True
Formula = (A1

Hi,

To put things in perspective, I analyse Market research data.

Let's say I have some string type data starting from Cell A2 to Last Cell in
column A Also let's say I have some string type data starting from Cell I2
to Last Cell in column I.

The data in a sample cell of column A (let's say cell A2) would be something
like " I use C++ , Visual Basic and Win2K Server at my workplace. At home I
dabble with C++ and Qualcomm". Basically column A would be complete
sentences and out of that sentence I would be interested only in some of the
words. Like if I'm tracking usage of software tools (and if am not
interested in Operating systems) then for me only C++ and VB would be my
point of interest. This is where Column I plays its part.

With full help from NewsGroup (Tim Williams - "Generating count of unique
words in a cell or cells" ) I have been able to get a nice piece of module
which enables me to get a count of unique words ( frequency of a word in
Column A) . After running the module , I scan the results and expunge those
words which are not point of interest in my study. Like based on the above
example - the words "I" , "use" , "Win2K server" and "Qualcomm" etc. would
be removed. I then take the remaining list of unique words and paste them in
column I (starting from row 2 ). Hence, in column I would have a list of
RELEVANT words only.

The part which I explained above, I naively refer to as Text Mining.

After this I developed a macro ( by copying snippets of syntax from variety
of sources and Recording feature). This macro basically compares the CELLS
in Column A to Column I
and display the Matching words in Column B through E. What I mean is cells
in columns B thru G display a list of words which appear in the
corresponding cell of Column A AND also appears within any cell in Column I.

Taking the above example cell B2 would say "C++" and Cell C2 would say "VB"
because Column I would not be having rest of the words which are there in
Cell A2. (cell D2 and E2 would be left blank. Please note if there were no
matches then B thru E will be left blank.)

Presently the problem is the text in column A would be having TYPOS. Like
somebody may say in cell A2 "I use Visula Basic" and another person may say
in cell A3 "I use Visul Basic". Now, I wont be getting any data matches in
Column B because column I would be having "Visual Basic" but not "Visula
Basic" or Visul Basic".

So, I want to develop a TEXT Scrambler function(S) which can :-

a) First function - SCRAMBLE a single letter of the word in Column I . That
is if Column I has "Visual basic" then any 2 adjacent non empty letters are
swapped. That is function should be capable of giving out results like
"Visula Basic" , "Visual Baisc" and similar permutations of adjacent letters
only. I hope that at a time only "one" transformation of adjacent letters
would be sufficient. (first letter might not be permuted as my understanding
is that people dont commit typing errors in their first letter.) I dont want
to swap the "space" between 2 words, that is in a particular transformation
I would just swap any 2 adjacent LETTERS of a particular WORD within the
STRING.

b) Second function - MISS or remove a single letter of the word in column I.
That is if a particular cell in column I has "Visual Basic" then it could
give me permutations like "Viual Basic" , "Visual Baic" etc.

c) Third function - SUBSTITUTE a single letter of the word in column I with
any of the other 25 letters of the English alphabet. That is if column I has
"Visual basic" then it would be able to give me "Vidual Basic" , "Visual
Nasic" and similar permutations.

d) Fourth function - Am being too ambitious but.... Would like to have a
function which can combine the effects of a), b) c) simultaneously though
each of them are individually transformed only once. (Would doing this be
disastrous from computing resources point of view ?)

I want all the above to be FUNCTIONS and not macros . I'm aware about the
difference between 2 only to the extent that in case of a function I can
write a statement like :-
If StringSubsetFromColumnA = ScrambledCellofColumnI(..,...) Then
CellinColumnB = UnscrambledcellofColumnI
End if

I hope I have been able to express my needs correctly. Im posting my present
unscrambled macro in the follow-up post to this as I didnt want to make my
post too big. (Not posting everything in one mail, is that a correct
practice in Newsgroups ?)


Thanks a lot,
Hari
India

Hello,

I posted a question at excel.misc regarding using excel as a word
processor.

http://groups-beta.google.com/group/...02ea974dde7cc1

I was attempting to use text functions (trim, mid, etc. ) to "wrap"
text as it would in MS Word. And Myrna brought to my attention that I
could use edit | fill | justify option to somewhat accomplish what I'm
trying to do.

Is there another alternative using macros?

I have the word "August" in cell C3. I would like for cell I3 to be "September" based on the value in cell C3, and I would like cell O3 to be "October" based on the value in cell I3. Seems like it shoud be simple to convert it to a date format in an Excel function and add a month, but after trying too long and searching mrexcel.com, I can't seem to find a way. Also, I need to use Excel functions, not VBA. Any help would be appreciated. Thanks.

We have an excellent in house developed add-in(xlam) that automate many processes.
Unfortunately we have some excel templates with the same UDF functions locally, i.e VB:

Public Function rng(ByVal dx As Double, ByVal dy As Double) As Double 
    rng = Sqr(dx ^ 2 + dy ^ 2) 
End Function 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



The above function exists in both add-in and local workbook.
In Excel 2003 this was not a problem.
But in Excel 2007 the formula is referenced to the add-in (xlam) and not the local workbook VBA i.e
VB:

= 'C:\Documents and Settings\xx\Application Data\Microsoft\AddIns\isXtools.xlam'!rng(.....

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



.Is there a method for setting the priority of which the formula is referenced?

I have tried disabling the add-in in the workbook_open() procedure and enable on the workbook_Beforeclose() without luck.

The AVERAGEIFS function returns the average (arithmetic mean) of all cells that meet multiple criteria.
The SUMIFS function adds the cells in a range that meet multiple criteria.
The COUNTIFS function counts the number of cells within a range that meet multiple criteria.



The AVERAGEIFS function in cell A8 returns the result of 12.5
1.First Argument: The Averaging range, 12.5 is an average of 10+15 appearing in that range.
2.Second & Third Argument boxes: Criteria range 1 + Criteria1, 10, 15 & 20 meeting the criterion ">2" in List range.
3.Fourth & Five Argument boxes: Criteria range 2 + Criteria2, 10 & 15 meeting the criteria ">5" in Number range and ">2" in List range.



The SUMIFS function in cell A9 returns the result of 25
1.First Argument: The Sum range, 25 is the sum of 10+15 appearing in that range.
2.Second & Third Argument boxes: Criteria range 1 + Criteria1, 10, 15 & 20 meeting the criterion ">2" in List range.
3.Fourth & Five Argument boxes: Criteria range 2 + Criteria2, 10 & 15 meeting the criteria ">5" in Number range and ">2" in List range.



The COUNTIFS function in cell A10 returns the result of 2
First & Second Argument boxes:
Criteria range1 + Criteria1, 2 is the number of cells meeting two criteria in List range.
Third & Fourth Argument boxes:
Criteria range 2 + Criteria2, 10 and 15 meeting the criterion ">5" in Number range.

I have coded class objects for multiple textboxes that all run the same code. This is the code that I initially wrote before I was using classes, then I just copied for the rest of the instances.
Code:

Private Sub txtCPU1_Change()
    
    If txtQty1.Value = "" Then Exit Sub
    If txtCPU1.Value = "" Then Exit Sub
    txtCost1.Value = Format(txtQty1.Value * txtCPU1.Value, "###,##.00")
    
End Sub
Private Sub txtQty1_Change()

    If txtQty1.Value = "" Then Exit Sub
    If txtCPU1.Value = "" Then Exit Sub
    txtCost1.Value = Format(txtQty1.Value * txtCPU1.Value, "###,##.00")
    
End Sub

But I want to clean up the code and use classes for the boxes. The purpose of the code is to calculate the total cost of something, and update when the Qty or Individual Cost changes.

Here is what I wrote for the class module
Code:

Option Explicit

Public WithEvents txtQtyGroup As MSForms.TextBox

Private Sub txtQtyGroup_Change()
Dim i As Integer
    
    With frmEditRecipe
        If Val(.Controls("txtQty" & i)) = "" Then Exit Sub
        If Val(.Controls("txtCPU" & i)) = "" Then Exit Sub
        Val(.Controls("txtCost" & 1)) = Format(Val(.Controls("txtQty" & i)) * Val(.Controls("txtCPU" & i)), "###,##.00")         
    End With

End Sub

And I have this in the Intialize for the frm Code:

With Me
        For i = 1 To 20
        Set txtCost(i).txtCostGroup = .Controls("txtCost" & i)
        Set txtCPU(i).txtCPUGroup = .Controls("txtCPU" & i)
        Set txtQty(i).txtQtyGroup = .Controls("txtQty" & i)
        Next i
    End With

The problem is I get a compile error form the code that is in bold from the class module. Any help?

Looking for the difference in a formula, one that contains the AMPERSAND, and the other doesn't.

Here is a list of numbers, say they are in column M12:M16.
238
412
530
711
989

I'm checking for the two closest numbers to a value, e.g. 688. That number is in cell N12.

I have the following two formulas that yield different results because of the presence of the ampersand, &:

Code:

=SMALL($M$12:$M$16,COUNTIF($M$12:$M$16,"<"&N12)+1)

This yields the value 711.

Code:

=SMALL($M$12:$M$16,COUNTIF($M$12:$M$16,"<N12")+1)

This yields the value 238.

Whatb is the function of the ampersand?

Thanks for the help.

I am looking to connect the values from multiple cells in to one cell and I think there is a way to do it using the ampersand sign, but I don't know exactly how. More specifically, if I have "12/31" in cell A1 and "2007" in cell A2, what is a formula that I can enter in to cell A3 that would combine these two values to read "12/31/2007" (note: I would also prefer to have the second "slash" between "31" and "2007"). Any help would be greatly appreciated! Thanks.