Results 1 to 9 of 9

Thread: if statment

  1. #1

    if statment



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I need some help with the following,

    I have a excel file that I import 3 different files in each day, then I put all of them in one sheet, , on C I have Server names (now there are 3 of them, but it could get more)

    The labels are at this stage 12 different ones. eg Pra001 to pra999 or aag001 to aag999, plus 10 more.

    So what I need is that when I put a label number in A, it must put one of the 3 servers names in C.
    I want to use an if statement, but I want to use it like PRA* or AAG* eg.

    I then rename the sheet to today's date.


  2. #2
    More info

    Here are some labels and servers
    eg

    LABEL SERVER
    PRA001 UNIX
    AAG325 UNIX
    NSW009 UNIX
    PRA090 UNIX
    NSW100 UNIX
    000110 VM
    076656 GP
    065456 GP
    AAG456 UNIX

    There will be about 30-50 labels every day. with your formula, will this work, so when I put a label (any label in, will it
    change the server name to that label eg (on A4 I put in AAG456, then in c4 it must put in Unix as server.) Tommorow A4
    can be with label CTB345 and that Server will be VM.

  3. #3
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Hi
    With this formula below, Ive assumed:
    a) that your label and server data are in cols F and G starting on row 2
    b) starting in A4 you will enter a partial string to match with the labels in F. You
    might need to enter 2 or more characters to get the correct match.
    c) The matching Server is returned starting in C4.
    d) Ive used IFERROR to block N/A messages. You may need IF(ISERROR( if your
    Excel is pre 2007.
    e) Because of the leading zeros in your labels, I formatted col A as Text.
    d) The formula allows for 1000 rows of data:
    The formula is:

    =IFERROR(IF(ISBLANK($A4),"",(INDEX($G$2:$G$1001,MATCH($A4&"*",$F$2:$F$1001,0),))),"")
    Copy down col c
    I don't know why, but the editor insists on chopping up the formula and putting it into a smaller box !
    Last edited by Hercules1946; 2013-12-15 at 12:52 PM.

  4. #4
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    681
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Where do the labels and server names originate from? Are they always the same?

    You manually enter labels into col A? How many rows are you doing this for?

    Might it not be easier to deal with server names at the imported file level using VBA rather than after combining everything that's imported and using IF in a formula?

    Uploading an example workbook with imported files prior to combining into one sheet would probably go a long way towards achieving your desired end results.

    You should also see this http://www.excelguru.ca/content.php?184

  5. #5
    [QUOTE=NoS;10597]Where do the labels and server names originate from? Are they always the same?

    You manually enter labels into col A? How many rows are you doing this for?

    Might it not be easier to deal with server names at the imported file level using VBA rather than after combining everything that's imported and using IF in a formula?

    Uploading an example workbook with imported files prior to combining into one sheet would probably go a long way towards achieving your desired end results.


    The labels are imported from 3 txt files, one for each server in to a sheet for each one of them (Unix, VM and GP", I then put them all in on sheet "ALL" in column A4 I have this "=Unix!A44"., so I need to then when I have a Label in A4 in must put the server name in C4.
    I can make extra sheet with all labels and Server names to the labels, but I do not know how to let on "ALL" when I put in A4 (=Unix!A44") to let C4 then know it must be Unix.

    The labels is not the same every day.
    How can I then Use VBA if this will work.
    At this stage I put in the server manually. I use a file then add all the info and rename the file, so that the clean file will always work.
    Here are the file
    Attached Files Attached Files

  6. #6
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    681
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Winon

    Thank you this will work.
    No point wasting time with a cross poster.

  7. #7
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by NoS View Post
    No point wasting time with a cross poster.
    There is a way to do this with a recent function, but as you say why bother? Its a pity that the major sites can't combine to remove users that don't follow the rules!

  8. #8
    Sorry did not mean to cross post, I did this because not all people are using the same form, and I want this to work, but if that is how you feel......

  9. #9
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    767
    Articles
    0
    Excel Version
    2010
    Quote Originally Posted by hendrikbez View Post
    Sorry did not mean to cross post, I did this because not all people are using the same form, and I want this to work, but if that is how you feel......
    I can assure you that if the position was reversed you would feel exactly the same. If you weren't getting a free service, you would not pay several different proffessionals to provide a solution for you. We don't object to the cross posting per se. Its leaving people working on a solution for you when you already have one that we don't like.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •