YTL Excel #129: Zip Code, VLOOKUP & TEXT functions

Hi all,

I am new to the forum and a novice in excel so please bare with me and I appreciate your help.

I have a large list of product codes for which I need to make some product descriptions. The codes are fairly descriptive such as TN135CLC, which has the CLC standing for Cyan Low Capacity. I have been able to do this on a single basis but I want a formula that will cover all options such as TN135MLC (magenta low capacity) at the same time. Some of the codes have no C or M after. All possible options which come after the rest of the code are,

C (Cyan), M(magenta), K(black), Y(yellow), CLC(cyan low capacity), MLC(magenta low capacity) etc

The current formula is;

=IF(ISERROR(SEARCH("CLC",D29))," ","cyan low capacity")

In trying to add additional IF's, or add a VLOOKUP table of the codes and relivant text I am not able to get this to work.

Can someone help me complete this as it will save hours of work in typing out product descriptions?

I was not able to send a sample of my data so I hope the above is enough.

Many thanks

David

Hello, i'm new to excel, and i'm having a little difficulty with this piece of homework set.

(I've attached a small image file of 3 spreadsheets were dealing with0

Basically what i' supposed to do is link Unit Price from the 'orders'' sheet, to the unit cost on the prices sheet, but you have to take into account 'qty' on the orders sheet. so what i was thinking was using a IF >= 50 and <= 99 function, and do the same for the >= 100 and <=151 etc...

i was thinking about maybe nesting the IF functions

could someone please point me in the right direction, im completely lost with this section of the H.W

I need some help in getting the below formula to work. Right now excel returns the error as that there is an error in the formula but offers no suggestions. Any help would be appreciated. Thanks.


=IF(VLOOKUP($A8,BidData!$A:$BM,22,FALSE))="","No File",(HYPERLINK(VLOOKUP($A8,BidData!$A:$BM,22,FALSE),"File")))

Hey everyone!

I am using Excel 2007 at work and I have dual monitors. The problem is when I open two separate excel files and place them for each monitor, I can't do any formulas (vlookup mainly) from one excel file to the other. The only way is to make a copy of the spreadsheet and put it in one excel file, then the formula will work (but defeats having dual monitor).

Did Microsoft miss the boat on this issue?

Thanks

I have this formula below and excel returns the error that says "there is an error with the formula" but offers no suggestions on how to fix it. Any help would be appreciated.


VB:

=If(VLOOKUP($A8,BidData!$A:$BM,22,False))="","No File",(HYPERLINK(VLOOKUP($A8,BidData!$A:$BM,22,False),"File"))) 

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

Is there a way to find the code that excel uses for its built in functions? For example, excel has the betainv (and now beta.inv in 2010) function and I would like to know how the calculations work (so I can write a similar function in another program). My own made up algorithm takes a long time so I would like to see how excel does it. Is there a way to see this information? Thanks.

Hello all,

SHORT VERSION:

I want to learn how to write user-defined (worksheet) functions in VBA code, is there a way that I can see the VBA behind the existing worksheet functions? I figure they would be a resource to learn from.

LONGER VERSION:

I was asked the other day to write a formula for a colleague that linked to a worksheet that was triggered by the name of that worksheet being in the cell to the left.

I did this by fiddling with the hyperlink() function in the cells but in doing so it seemed a good idea to simply create a user defined function in a code module with a view to using that in my column of cells to get my sheethyperlink, rather than manipulating the existing hyperlink function at cell level. The problem is I have no experience really of writing functions and would like to learn. I'm sure seeing how the existing functions are coded would be very enlightening.

Thanks for reading

Jon

I feel like this is obvious, but I've done searches and couldn't find anything.

My issue is that I want to create a function that is very similar to VLOOKUP, but VLOOKUP itself doesn't have the flexibility I require, so I'm looking for some way to get to the code of the VLOOKUP function so I can copy the code and modify as needed.

It just occurred to me that MS might not actually make that code available. Is that the case?


EDIT: and just to spell out the key question he how can I access the code for built-in excel functions, eg VLOOKUP?

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

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.