excel Validation list search. Can I do this?

[Synchro]

I am new to most things excel. I just want some advice and a direction. I have searched with out any luck.
I have a spread sheet I made a while ago using validation to make pull down lists. the lists were purely informational.
Now I need to be able to search the lists and if they have a value in them I need to sort that row to a new worksheet.
Now, that I have confused anyone reading this. I will see if I can make it a bit more clear.
So for example. I have a column of computer names in A
In column B I have validation table that pulls down and shows the software installed in each computer.


I want to search that will look at the validation lists, and copy the data from column A to a new worksheet.
So if the validation list has MS OFFICE in it. I want it to copy the name of that computer to the MS OFFICE worksheet. So I can easily look at that sheet and see all the computers that have MS office installed.

is this possible? I don't want to change the validation menus as there is about 600 computers that I did manually.

I hope someone can decipher my request and give me some guidance.

Thanks


Mike

 

[DonkeyOte]

It sounds as though

a) your Validation Lists are sourced via Constants with each list tailored to the associated machine - rather than via Defined Names etc...

b) you want to pull Machine values that contain a given value within the adjacent software Validation List (rather than testing the value selected)

If the above is along the right lines I think you will need to use VBA and iterate the validation cells.
You can limit the processing a little by conducting a further test based on the validation rule itself so as to process "in batches" where viable
(ie machines sharing the same software list)

The below is just a basic proof of concept (for MS OFFICE). I suspect in reality you will be looking to iterate the validation lists and populate multiple software sheets simultaneously - if so it would be a good idea to elaborate on those requirements.

Sub DVExample()
    Dim rngDVAll As Range, rngDV As Range, rngDVArea As Range, rngU As Range
    Dim boolFirst As Boolean
    Application.ScreenUpdating = False
    With Sheets("Sheet1")
        On Error Resume Next
        Set rngDVAll = .Columns("B").SpecialCells(xlCellTypeAllValidation).Cells
        On Error GoTo 0
        If Not rngDVAll Is Nothing Then
            For Each rngDV In rngDVAll.Cells
                boolFirst = False
                Set rngDVArea = Intersect(rngDV.SpecialCells(xlCellTypeSameValidation), rngDV.EntireColumn)
                If rngU Is Nothing Then
                    Set rngU = rngDVArea
                    boolFirst = True
                ElseIf Intersect(rngDV, rngU) Is Nothing Then
                    Set rngU = Union(rngU, rngDVArea)
                    boolFirst = True
                End If
                If boolFirst Then
                    If InStr(1, rngDV.Validation.Formula1, "MS OFFICE", vbTextCompare) > 0 Then
                        With Sheets("MS Office")
                            rngDVArea.Offset(, -1).Copy
                            Sheets("MS Office").Range("A1").PasteSpecial xlValues
                        End With
                    End If
                End If
            Next rngDV
        End If
    End With
    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
End Sub

Modify sheet references & ranges as necessary of course.

Note also that the InStr method as is is susceptible to False Positives regards embedded terms.
If the delimiter in your DV lists is entirely consistent you should adjust the InStr accordingly (append both DV formula & search term with delimiter)