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

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" wrote:
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

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" wrote:

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" wrote:
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

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" wrote:
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

All hail MAX!!!

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

"Max" wrote:

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" wrote:
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

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" wrote:
All hail MAX!!!
Dude thanks a lot...this is what I am looking for.... God bless you :)