• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Maybe a too complicated task???

okuchanuk

okuchanuk

New Member
Hello Everyone.
I almost turned the internet upside-down to find a way to resolve this task. Chandoo is literally my last hope.

In the attached document I have 2 tabs, "DATA" and "RACK LAYOUT".

In the DATA tab I have all the information that i need and I'm trying to create a more visual example of the information I have.

In the highlighted columns are the criteria which is important. I want that data to be copied inside the RACK LAYOUT. But I need some formulas to: first, filter the cabinet (as per example Cabinet A1), then from A1 to filter the RU, and after everything was sorted to copy the name (server 1 for eg) in Rack Layout tab in the correct RU and CABINET. Please let me know if I wasn't clear enough.

I'll try to explain again, this time look at Rack Layout as you read.

IF the cabinet has the name A1 matches column 1
THEN check the RU and COPY column3
THEN PASTE column 3 in RACK LAYOUT in the correct position.
 

Attachments

I think you had Switch b Rack U wrong. Also don't use merged cell, it's going to wreak havoc when using formulas etc.

Also if you want server 12 to occupy U20 to 22 you need entry in the table for each U.

See attached, if this meets your need.
 

Attachments

Chihiro said:
I think you had Switch b Rack U wrong. Also don't use merged cell, it's going to wreak havoc when using formulas etc.

Also if you want server 12 to occupy U20 to 22 you need entry in the table for each U.

See attached, if this meets your need.
Click to expand...
Woaww. Chihiro you are a genius! I've been trying to find a solution like crazy. I really appreciate your help. Yes it does meet my need. Is there anywhere a tutorial where I can learn this thing? Are you doing tutorials in Excel? I would love to learn how to do it on my own.

At one point I really thought is impossible but now that I've seen I really want to learn.

Once again, many thanks.
 
Cihiro, I was going through the formula and I noticed you used cabinet B END, can that not to be included? Because not always I have the same cabinet in B END. I'm asking because I don't understand what you actually did. So I'm trying to create the Rack only from the A END data I have.

Many thanks and I hope I'm not asking too much.
 
Hi,

I needed to use Cabinet (B END) for Switch A & B as that's not in Cabinet (A END).

Other than that, there is no need to use Cabinet (B END).

The formula is basically combination of few things. Most important being Index,Match.

Taking D11 for example.

The formula concatenates Rack U & Cabinet and match that in list range, looking for exact match (looks for 40A1).
MATCH(B7&D$2,VRACKU&VCABINET,0) this evaluates to 1.

Index portion finds value in DIVNAME where row# matches 1 (from above).
INDEX(DIVNAME,1) evaluates to server 1 (Row#1 in range DIVNAME).

IFERROR is used to do additional operation when above calculation comes up as error (unable to find match).

So, if match is not found in Cabinet (A END), it looks for match using Cabinet (B END).

Finally, outer IFERROR is used to return blank (""), if no match is found in either list.

Formula is confirmed as array (CSE).

Hope that explains it.
 
Chihiro said:
Hi,

I needed to use Cabinet (B END) for Switch A & B as that's not in Cabinet (A END).

Other than that, there is no need to use Cabinet (B END).

The formula is basically combination of few things. Most important being Index,Match.

Taking D11 for example.

The formula concatenates Rack U & Cabinet and match that in list range, looking for exact match (looks for 40A1).
MATCH(B7&D$2,VRACKU&VCABINET,0) this evaluates to 1.

Index portion finds value in DIVNAME where row# matches 1 (from above).
INDEX(DIVNAME,1) evaluates to server 1 (Row#1 in range DIVNAME).

IFERROR is used to do additional operation when above calculation comes up as error (unable to find match).

So, if match is not found in Cabinet (A END), it looks for match using Cabinet (B END).

Finally, outer IFERROR is used to return blank (""), if no match is found in either list.

Formula is confirmed as array (CSE).

Hope that explains it.
Click to expand...

It's much appreciated Chihiro. I'll have to search for some tutorials for each formula you used so I can understand it properly. I'm not very advanced in Excel. Thanks for the tips you gave not to merge the cells that is really helpful.

Many thanks again.
 
okuchanuk said:
It's much appreciated Chihiro. I'll have to search for some tutorials for each formula you used so I can understand it properly. I'm not very advanced in Excel. Thanks for the tips you gave not to merge the cells that is really helpful.

Many thanks again.
Click to expand...
Chihiro, sorry I disturb you again. I'm wondering why this formula

=INDEX(DIVNAME,MATCH(B4&$D$2,VRACKU&VCABINET)) is not working?

Logically I think I did everything right. I used only the A END from DATA.
Thanks in advance.
 
Did you commit as array formula?

Instead of hitting just enter, you need to confirm the formula with Ctrl + Shift + Enter (CSE).

Oh and also nest it in IFERROR(), as the formula will result in error if match is not found.

=IFERROR(INDEX(DIVNAME,MATCH(B4&$D$2,VRACKU&VCABINET)),"")
 
You must log in or register to reply here.
Back
Top