How can I evaluate multiple text values to confirm if they are all the same or if they are different and return a set text value?

[PinPinPoola]

Hello,

I have a workbook with 2 tabs. Cabinets and Equipment.

Cabinets has a single row per cabinet
Equipment has multiple rows per cabinet

For each Cabinet Name in the Cabinets Tab, I need to evaluate all rows of the Cabinet Name in Equipment Tab and return into the Contains Equipment Owned By of Cabinets Tab if the the owner is either "Network Ops", "IT Ops" or "Shared" - where shared cabinets contain equipment owner by both Network Ops AND IT Ops.

I have tried an XLOOKUP, but this only returns the first Equipment Owner encountered for each Cabinet Name in the Equipment tab.

Cabinets Tab

Cabinet ID	Cabinet Name	Contains Equipment Owned By	Cabinet Size
1	AA01	Network Ops	48U 800x1200 HPE Adv Rack
2	AA02	Shared	48U 800x1200 HPE Adv Rack
3	AA03	IT Ops	48U 800x1200 HPE Adv Rack

Equipment Tab

Equipment ID	Cabinet Name	Equipment Owner	Equipment
1	AA01	Network Ops	Cisco Switch
2	AA01	Network Ops	Cisco Switch
3	AA01	Network Ops	Cisco Router
4	AA01	Network Ops	Cisco Firewall
5	AA02	Network Ops	Cisco Switch
6	AA02	Network Ops	Cisco Switch
7	AA02	IT Ops	HPE ProLiant Server
8	AA02	IT Ops	HPE ProLiant Server
9	AA02	IT Ops	HPE ProLiant Server
10	AA02	IT Ops	HPE SAN Storage
11	AA02	Network Ops	HPE SAN Switch
12	AA02	Network Ops	HPE SAN Switch
13	AA03	IT Ops	HPE ProLiant Server
14	AA03	IT Ops	HPE ProLiant Server

Any help, advice or pointers will be greatly appreciated.

Thx
Pin

 

[mumps]

Make sure that your sheets are named "Equipment" and "Cabinets". Try this macro. Do the following: Hold down the ALT key and press the F11 key. This will open the Visual Basic Editor. In the menu at the top, click "Insert" and then click "Module". Copy/paste the macro into the empty window that opens up. Press the F5 key to run the macro. Close the code module window to return to your sheet. There are other quicker ways to run the macro such as assigning it to a button that you would click on your sheet or assigning it to a short cut key.

Sub owner()
    Application.ScreenUpdating = False
    Dim LastRow As Long, srcWS As Worksheet, desWS As Worksheet, cabName As Range, fRow As Long
    Set srcWS = Sheets("Equipment")
    Set desWS = Sheets("Cabinets")
    LastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each cabName In desWS.Range("B2:B" & LastRow)
        With srcWS
            .Range("A1").CurrentRegion.AutoFilter 2, cabName
            fRow = .Range("A2", .Cells(.Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row
            If WorksheetFunction.CountIf(.Range("C2", .Range("C" & .Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible), "Network Ops") > 0 _
                    And WorksheetFunction.CountIf(.Range("C2", .Range("C" & .Rows.Count).End(xlUp)).SpecialCells(xlCellTypeVisible), "IT Ops") > 0 Then
                cabName.Offset(, 1) = "Shared"
            Else
                cabName.Offset(, 1) = .Range("C" & fRow)
            End If
        End With
    Next cabName
    srcWS.Range("A1").AutoFilter
    Application.ScreenUpdating = True
End Sub

 

[PinPinPoola]

@mumps Thank you so much for your help. I have left the office for the day, so I will try this when I get home.
Pin

 

[PinPinPoola]

@mumps Works like a dream, Thank you again.

 

[Peter_SSs]

You could also do this by formula.

PinPinPoola.xlsm
	A	B	C	D
1	Equipment ID	Cabinet Name	Equipment Owner	Equipment
2	1	AA01	Network Ops	Cisco Switch
3	2	AA01	Network Ops	Cisco Switch
4	3	AA01	Network Ops	Cisco Router
5	4	AA01	Network Ops	Cisco Firewall
6	5	AA02	Network Ops	Cisco Switch
7	6	AA02	Network Ops	Cisco Switch
8	7	AA02	IT Ops	HPE ProLiant Server
9	8	AA02	IT Ops	HPE ProLiant Server
10	9	AA02	IT Ops	HPE ProLiant Server
11	10	AA02	IT Ops	HPE SAN Storage
12	11	AA02	Network Ops	HPE SAN Switch
13	12	AA02	Network Ops	HPE SAN Switch
14	13	AA03	IT Ops	HPE ProLiant Server
15	14	AA03	IT Ops	HPE ProLiant Server
Equipment

PinPinPoola.xlsm
	A	B	C	D
1	Cabinet ID	Cabinet Name	Contains Equipment Owned By	Cabinet Size
2	1	AA01	Network Ops	48U 800x1200 HPE Adv Rack
3	2	AA02	Shared	48U 800x1200 HPE Adv Rack
4	3	AA03	IT Ops	48U 800x1200 HPE Adv Rack
Cabinets

Cell Formulas
Range	Formula
C2:C4	C2	=LET(ownr,UNIQUE(FILTER(Equipment!C$2:C$15,Equipment!B$2:B$15=Cabinets!B2)),IF(ROWS(ownr)=1,ownr,"Shared"))

 

[mumps]

You are very welcome.

 

[PinPinPoola]

@Peter_SSs Thank you very much for this alternative formula method. I am grateful to you and @mumps for all of your help.

 

[Peter_SSs]

You're welcome.

I'm not sure which method you have chosen but if you have used the formula method then note that I made a slight error with that formula. Whilst it works as shown, it is a very bad idea to use the worksheet name of the sheet the formula is in as that can lead to inconspicuous error in some circumstances. So since the formula is on sheet 'Cabinets' it should be corrected like this
=LET(ownr,UNIQUE(FILTER(Equipment!C$2:C$15,Equipment!B$2:B$15=Cabinets!B2)),IF(ROWS(ownr)=1,ownr,"Shared"))

PinPinPoola.xlsm
	A	B	C	D
1	Cabinet ID	Cabinet Name	Contains Equipment Owned By	Cabinet Size
2	1	AA01	Network Ops	48U 800x1200 HPE Adv Rack
3	2	AA02	Shared	48U 800x1200 HPE Adv Rack
4	3	AA03	IT Ops	48U 800x1200 HPE Adv Rack
Cabinets

Cell Formulas
Range	Formula
C2:C4	C2	=LET(ownr,UNIQUE(FILTER(Equipment!C$2:C$15,Equipment!B$2:B$15=B2)),IF(ROWS(ownr)=1,ownr,"Shared"))

 

[PinPinPoola]

@Peter_SSs You have gone over and above; a thousand thank yous!

I decided to use your formula method as it does not require the user to enable the VBA content each time the workbook is opened.

I have just made the formula correction you mentioned.

Pin