Multiple results in a filtered range

[Rosstamon]

I have a spreadsheet the runs Columns A - AF and Rows 2 - 1529 and increasing.
In Column B I have the project numbers which are currently in use. Sometimes when I sort by other columns, for instance Column M contains the Part Number, when I sort by the part number the result will return multiple rows from multiple different projects. Thus Column B will contain multiple project numbers.

At the bottom of my spreadsheet I have the following formula

=VLOOKUP(1,A1:B1529,2,FALSE)

This formula looks pulls out the project number from Column B, Row 1. What I would like this to do is compare all of Column B and if there is more than 1 project number, I would like it to return a statment like "Multi". But if there is only one project number I would like it to return that project number.

Possible?

Thanks,
Rosstamon

 

[pboltonchina]

This will split all your info into separate sheets so you can view everything together. Change sheet name to suit

Option Explicit

Sub ParseItems()
'Jerry Beaucaire  (11/11/2009)
'Based on selected column, data is filtered to individual sheets
'Creates sheets and sorts sheets alphabetically in workbook
'6/10/2010 - added check to abort if only one value in vCol
Dim LR As Long, Itm As Long, MyCount As Long, vCol As Long
Dim WS As Worksheet, MyArr As Variant, vTitles As String, Oops As Boolean

Application.ScreenUpdating = False

'Column to evaluate from, column A = 1, B = 2, etc.
   vCol = 2
 
'Sheet with data in it
   Set WS = Sheets("Data")

'Range where titles are across top of data, as string, data MUST
'have titles in this row, edit to suit your titles locale
    vTitles = "A1:E1"
   
'Spot bottom row of data
   LR = WS.Cells(WS.Rows.Count, vCol).End(xlUp).Row

'Get a temporary list of unique values from column A
    WS.Columns(vCol).SpecialCells(xlConstants).AdvancedFilter Action:=xlFilterCopy, _
        CopyToRange:=WS.Range("EE1"), Unique:=True

'Sort the temporary list
    WS.Columns("EE:EE").Sort Key1:=WS.Range("EE2"), _
        Order1:=xlAscending, Header:=xlYes, OrderCustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

'Check for more than one value in list
    If WS.Range("EE" & Rows.Count).End(xlUp).Row > 2 Then

'Put list into an array for looping
'(values cannot be the result of formulas, must be constants)
        MyArr = Application.WorksheetFunction.Transpose(WS.Range("EE2:EE" _
            & Rows.Count).SpecialCells(xlCellTypeConstants))

'clear temporary worksheet list
        WS.Range("EE:EE").Clear

    Else
        WS.Range("EE:EE").Clear
        Oops = True
        GoTo ErrorExit
    End If
    
'Turn on the autofilter, one column only is all that is needed
    WS.Range(vTitles).AutoFilter

'Loop through list one value at a time
    For Itm = 1 To UBound(MyArr)
        WS.Range(vTitles).AutoFilter Field:=vCol, Criteria1:=MyArr(Itm)
    
        If Not Evaluate("=ISREF('" & MyArr(Itm) & "'!A1)") Then    'create sheet if needed
            Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = MyArr(Itm)
        Else                                                      'clear sheet if it exists
            Sheets(MyArr(Itm)).Move After:=Sheets(Sheets.Count)
            Sheets(MyArr(Itm)).Cells.Clear
        End If

    'customize this section as needed for copy/paste targets
        WS.Range("A" & WS.Range(vTitles).Resize(1, 1).Row & ":A" & LR) _
            .EntireRow.Copy Sheets(MyArr(Itm) & "").Range("A1")

        
        WS.Range(vTitles).AutoFilter Field:=vCol
        MyCount = MyCount + Sheets(MyArr(Itm)) _
            .Range("A" & Rows.Count).End(xlUp).Row - 1
        Sheets(MyArr(Itm)).Columns.AutoFit
    Next Itm
    
'Cleanup
    WS.AutoFilterMode = False
    MsgBox "Rows with data: " & (LR - 1) & vbLf & "Rows copied to other sheets: " _
                & MyCount & vbLf & "Hope they match!!"

ErrorExit:
    If Oops Then MsgBox "Only one value found, aborting parse process..."
    Application.ScreenUpdating = True
End Sub

 

[Rosstamon]

pboltonchina,
This is probably a very good solution, but I have no clue how to use VB solutions in Excel, I was hoping for just a simple formula that would solve my query.

I would prefer a formula that would evaluate one column and determine if there is more than one piece of unique data in that column. If TRUE it should return one word like "Multiple". If FALSE, in other words there is only one unique piece of data in that column, it should return that unique piece of data.

The data will be a number between 5 and 10 digits in length.
An example of 2 of the numbers: 40001, and 2040123456.

I appreciate your help.
Thank you,
Rosstamon