Figuring out abbreviation

    Good afternoon all,


    Im kinda stumped i was trying to write a script in ways to take an entire descriptions of devices and turning it into a set abbreviation for it. Like if there was a table at the top looks like this shown at bottom. I was thinking an If statement like =IFS(CODED MANUAL STATIONS,"MS",IFS(AIR SAMPLING,"HSSD"),IFS(HALON MANUAL STATION,"HMS",IFS, and so on but I didnt know if that would be the best way to do that?




    [TABLE="width: 81"]

    [tr]


    [TD="class: xl64, width: 108, bgcolor: transparent"]NON-CODED MANUAL STATIONS – MS[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent"]AIR SAMPLING – HSSD[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent"]HALON MANUAL STATION – H-MS[/TD]

    [/tr]


    [tr]


    [TD="class: xl65, bgcolor: transparent, align: center"]ABORT SWITCH – AS[/TD]

    [/tr]


    [/TABLE]

    Re: Figuring out abbreviation


    anthonys213,

    IF or IFS would be too difficult to manage.

    Can you supply a workbook, with the raw data in one column (column A?), and, say off to the right in another column (column D?) the descriptions of devices, and in the next column (column E) the abbreviations.

    Then we could supply a macro to put the abbreviations in column B.

    To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed/changed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

    The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

    To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.

    Re: Figuring out abbreviation


    Ok sure thing, thank you for the heads up I didnt even think to attach it honestly. and if statements are a pain Ive been writing alot of them to try and go through specific options for things just making it run all the diferent variables, rather than a macro cause I can use excel well just not VBA. And what im trying to accomplish is after the abbreviation is found Im trying to write something slowly, as its coming along slowly by taking just specific sets of those abbreviations and transfering the cell and as you can see all the accompanied cells in the row onto another sheet in a workbook that that sheet is attached to. But thats a tougher one Im thinking. Any help again is greatly appreciated thank you very much for your time.


    AnthonyS :)



    forum.ozgrid.com/index.php?attachment/54326/

    Re: Figuring out abbreviation


    anthonys213,

    There is no raw data beginning in row 27?????

    Quote

    rather than a macro cause I can use excel



    In the Quick Reply box, just put the word BUMP. Then, click on the Post Quick Reply button, and someone else will assist you.

    Re: Figuring out abbreviation


    Oh meaning that its blank, yes at the time it is blank because the tech in the field gets the address from the panel itself and enters it there the physical address what may go there is something like N2L23D354 Thats, Node 2 Location 23 Device 354 or may be an easier location like Third floor west corridor smoke or something along those lines different for every system. Sorry about that.

    Re: Figuring out abbreviation


    The reason for the alteration into abbreviations is that me and another tech wrote a program that takes events from the panels themselves using another program than wrote code in java and other scripts and stand alone system that takes the events logs them into a database than tranfers into the right format for easy copy and paste onto this device tested since some hospitals have over 1000 devoces and all need to be tested

    Re: Figuring out abbreviation


    Also would vlookup method with hidden cells work, a simpler if statement to check corresponding text and input the finding found in a hidden cell. Just a thought ive been going back and forth with someone that has a bit more experience and it sounds good in thought, but is it doable is my question i guess?

    Re: Figuring out abbreviation


    I cant quiet figure out how to remove I click the # button but its not taking away the colors. But this works as well, I have the macro and the sheet i attached it too on the second page

    Re: Figuring out abbreviation


    Just to be clear about this... you want to convert a full, typed description (NON-CODED MANUAL STATIONS) to an abbreviation (MS) ... ?



    Also, thanks for posting your attempt at a solution but the rules here require the use of code tags when posting code. These colourise (so you'll have to remove the HTML colour formatting) and format the code making it easier to read and so follow the logic. Code tags are added, when editing a message, simply by highlighting the code and clicking the '#' button on the edit box toolbar.


    Please edit your post to comply.

    Re: Figuring out abbreviation


    Yes to answer your first question, that is exactly what I need to accomplish.


    Also is that what you needed me to do. that was new to me Sorry about that Ill go through the forum rules again just to make sure I have everything right. Thank you for the heads up cytop. :)

    Re: Figuring out abbreviation


    Why not create a list off to the side of all the items and the corresponding abbreviations... then you can use VLOOKUP


    e.g.


    =VLOOKUP(A2,$X$2:$Y$15,2,FALSE)


    This would look at what you have in A2, find it in X2:X15 and return the corresponding abbreviation you put in Y2:Y15.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Figuring out abbreviation


    If you want macros, then I will leave it to somebody who might be more VBA proficient than I.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Figuring out abbreviation


    Still stuped on this one its altering the entire page taking anything with the associated words and altering them all rather than just in c column. Also I have to write mutliple points on this to alter all associated abbreviations. Im trying to put in a specific order so that thsoe with certain words that dont have to do with the others trying to make a specific as possible so it does those first than onto the ones with less specific titles. as the attached file shows.

    Re: Figuring out abbreviation


    Still having trouble with the abbreviations I was thinking a lookup but its not working properly i keep having to run the macro rather than it just doing it. And I keep having to hit it to get to the next words to abbreviate. Meaning I hit run it does the first abbreviation in the string, I do it again it does the next rather than looping through them.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login