if statment

H

hendrikbez

New member
Joined
Dec 13, 2013
Messages
7
Reaction score
0
Points
0
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.

 
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.
 
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 ! :mad:
 
Last edited:
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
 
NoS said:
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
Click to expand...
 

Attachments

  • Tapes 2013 12 17.xlsx
    40.1 KB · Views: 15
NoS said:
No point wasting time with a cross poster.
Click to expand...

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!
 
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......
 
hendrikbez said:
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......
Click to expand...

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.
 
You must log in or register to reply here.
Back
Top