fetching multiple fields data

[abadd0n]

Hi,

I have a database with IP address and hostname of a server. Now the problem
here is some of the servers are running in cluster and have duplicate
hostname. I am looking for a command/suggestion that incase if i type a
hostname. It automatically shows all the corresponding IP Address of the
given hostname.

Please help. Attached below is the sample database.

IP Address Hostname
10.1.1.2 Juni
10.1.6.8 Ora
10.1.5.6 Tetli
10.1.1.6 Juni

Thanks,
abadd0n

 

[Max]

Think a pivot table can immediately serve your interests
Place Hostname, IP Address (below Hostname) into the ROW area
Double click on Hostname, set Subtotals to None
Place IP Address into DATA area (it'll show as COUNT)

You'd get this pivot output:

Count of IP Address
Hostname IP Address Total
Juni 10.1.1.2 1
10.1.1.6 1
Ora 10.1.6.8 1
Tetli 10.1.5.6 1
Grand Total 4
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik

 

[abadd0n]

Hi Max,

Thanks for your reply and apologies that I did not provide complete
information in the original thread.

Actually there is more information attached in this excel database. like
criticality, OS and application.

I am afraid that pivot will not be able to solve my purpose. Right now I am
using Vlookup to display the information. However, Vlookup only displays the
first entry from the table, let me show you the actual structure

Hostname IP Address Criticality OS Application
Juni 10.1.1.2 Crit 1 Sun ABC
Ora 10.1.6.8 Crit 3 Windows DEF
Tetli 10.1.5.6 Crit 4 UNIX TR
Juni 10.1.1.6 Crit 2 SUN TYS

 

[Max]

Fine, here's a simple way to get the multiple lines enquiry that you seek up
and running smoothly in another sheet

Illustrated in this sample:
http://freefilehosting.net/download/42fcl
Fetching Multiple Lines.xls

Source table assumed in sheet: x,
cols A to E, data from row2 down
Key col = col A ( hostname)

In another sheet, eg: Enquiry
In A2 is a droplist to select the host, eg: Juni, Ora, ...

In B2: =IF($A$2="","",IF($A$2=x!A2,ROW(),""))
Leave B1 empty

In C2:
=IF(ROWS($1:1)>COUNT($B:$B),"",INDEX(x!A:A,SMALL($B:$B,ROWS($1:1))))
Copy C2 to G2. Select B2:G2, copy down to cover the max expected extent of
data in x. Minimize col B. Cols C to G will return all the lines for the
hostname selected in A2, all neatly packed at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik

 

[abadd0n]

All hail MAX!!!

Dude thanks a lot...this is what I am looking for.... God bless you

 

[Max]

Welcome. Could you take a moment to press the YES button in that earlier
response (like the one below)? Thanks.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik

 

[abadd0n]

Hey Max,

One more thing, if it possible to design the same database to fetch data
using hostname or ip.

Now there are times when we need to search data using ipaddress. Is it
possible to get a formula which get me data either from ip or hostname
whatever I type i the cell A2 of enquiry sheet?

abadd0n

 

[Max]

Easy. Just make a copy of Enquiry. In the copy, change the criteria formula
in B2 to point to the ip col (col B in x), ie in B2:
=IF($A$2="","",IF($A$2=x!B2,ROW(),""))
Copy B2 down. Then change the DV in A2 to point to a named range for the
ips. That's it. No change is required to the rest of the extract formulas.
Hey, don't forget to press all the YES buttons, won't you?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik

 

[abadd0n]

Thanks max what i am looking for not another sheet, but in the same enquiry
sheet if I put an IP address/host name in A2 of enquiry sheet, it should
display all the corresponding results.

id it possible?

 

[Max]

Nope, not possible.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik

 

[Max]

From your profile, I see you asked the same original question in access
group. So which "sys" did you ultimately go with?
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---

 

[abadd0n]

I have this database in excel. I was planning to go for Access database and
make an application rather than keeping an excel file.

However, now I will stick to excel file, with old vlookup formula. Incase
if you are able to figure out how to swtich between the IP and hostname with
the same sheet, please let me know. I was able to do it with vlookup, but it
is not that effective as the list parameter. If you want, I can share my
sheet with you, to look at it and you can provide suggestion or definitely
improve it.

let me know.
abadd0n

 

[Max]

let me know ..
You can always put in a fresh new query for the stretched scope
Try garner thoughts from other responders

I've backed you up fairly solid in this thread,
as far as your original posting goes.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---

 

[abadd0n]

Guess what Max!!

I am able to do the ip and hostname in one cell. I changed your formula a
bit by adding another if statement in cell B of enquiry sheet

Here it goes like this:

=IF($A$2="","",IF($A$2=x!A2,ROW(),IF($A$2=x!B2,ROW(),"")))

check it.

abadd0n

 

[Max]

That's great. Nothing beats the learning, wonder & delight of doing it up on
your own.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik