row 1 value of any column

[Pappygt]

I might have posted this in wrong forum initially, sorry - I need to build a table with cell 1 of each column containing the server name with all the cells below it showing nodes on that server. I want to search all nodes and return the row 1 value of that column containing the node name. vlookup, hlookup, etc - all wont work - help. I could build 1 very long column with all the nodes then in column 2 put the server name repeating it every row but that seems inefficient. Multiple columns (servers) with the nodes listed in that column.

 

[Smitty]

Welcome to the Board!

You might want to post an example of the data that you have now and what your desired output is (see the HTML Maker link in my sig to post some screen shots).

 

[Pappygt]

I removed this question from the 'Lounge' and am leaving it here for an answer (I hope). I made a picture but was unable to get it uploaded, the link to your html maker did not work. Let me clarify what I need. Enter a city and have the formula / Excel return the state name so yoiu have a table of cities and states. In my mind, I wanted to put the state name(s) in row 1 with a different state in each column, cell 1. Then in each column cell 2 - n, I would add city names. The button would ask : Enter city name. The formula or ? would return the state name by looking in the table for the city then returning the row 1 value for the column it found the city on. Is that more clear?

 

[Smitty]

the link to your html maker did not work

The link works fine. Once you select an HTML option (Default, User Defined Range, etc.), the HTML Maker creates and copies HTML code to the clipboard, but it won't give you a notification. From there you can come back here and Ctrl+V to paste the code in your post. When you submit the board will render the code as an image.

As to the question at hand, now you're talking about cities/states? I thought it was servers/nodes?

In the case of duplicate cities how do you determine which goes where? E.G. Dallas, TX/Dallas, GA/Dallas, OR/Dallas, PA...

 

[Pappygt]

Thanks for your quick reply. I work in a very secure environment and while I could probably get the picture thing to work, I'd rather keep it simple while at work and avoid any hassle. Cities .vs servers - yes, I was just trying to simplify it. The quick tech info is I'm working with a product called Control-M by BMC and running batch jobs in a Unix environment. When you add new jobs you need to specify the control-m server and the table. Requests come in with just the table name, we need to 'look up' the control-m server name. There are about 10 Control-m servers and several hundred tables on those servers. I want to use excel to ask: What is the table name? Then calculate the Control-M server that the table resides on. I crew a correlation to cities in a state. In 1 array, I simply wanted to list the 10 servers on line 1 in columns A-J - row one. Then in row 2 thru xx of each column, list the nodes. Have excel look for the NODES then response with the row1 cell of the column it found the node in (which would contain the server name). In the example below, if you enter: Node-F, Excel would answer: Server 2.
Col A Col B Col C Col D
server1 server 2 server 3 server 4
node-a node-b node-c node-d
node-e node-f node-g node-h
node-i node-j

 

[Smitty]

I'm not quite sure how you'd have excel efficiently look at multiple rows like that.

Is it possible to get a list that would show Server # & Node on individual lines? You could easily summarize with a Pivot Table that way.

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

1​

[/td][td]server 1[/td][td]server 2[/td][td]server 3[/td][td]server 4[/td][td][/td][td]node-f[/td][/tr]

[tr][td]

2​

[/td][td]node-a[/td][td]node-b[/td][td]node-c[/td][td]node-d[/td][td][/td][td]server 2[/td][/tr]

[tr][td]

3​

[/td][td]node-e[/td][td]node-f[/td][td]node-g[/td][td]node-h[/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]node-i[/td][td]node-j[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

F2, control+shift+enter, not just enter:

=INDEX($A$1:$D$1,1/(1/MIN(IF($A$2:$D$4=F$1,COLUMN($A$1:$D$1)-COLUMN($A$1)+1))))