Excel 2007 Unique value from list of names 1

[mtdew]

I discovered how to pull unique values from a list of names by using:

{=IFERROR(INDEX('Daily Fails'!A:A,MATCH(0,COUNTIF($B$1:B2,'Daily Fails'!A:A),0)),"")}

but it runs much too slow.

Is there another way to pull only unique values from a list of indeterminate length? I'm working with names in column A on the Daily Fails sheet and I won't know the names ahead of time and won't have access to the files to use the Filter or Remove Duplicates features so I need the formula to handle it in real-time.

 

[SkipVought]

hi,

maybe explain what you are trying to accomplish, along with sample data that can be used with your formula.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mtdew]

I create a list of error messages and send it out to a group to make corrections. They enter their name into column A using the drop down box and they enter what they did to correct the error in column B. However, we are expanding and now in addition to the name in the drop down list we have to have a blank cell so new names can be typed in. Therefore, I don't know the list of names ahead of time but I want to create a formula that will calculate how many errors were corrected by each name. I have done someething similar with a pivot table but the file that they work off of has to be shared and the pivot table doesn't auto-refresh when the file is shared.

As far as data I have the names in column A: Kim, Sharon, Lee, August, Miranda, Kari, and then the blank cell that can have names typed in

Column B: no correction needed, steps corrected, requesting assistance

On another sheet I want to create the formula that only pulls unique names. Example Kim may work on 10 errors but her name only appears on the new list 1 time and then I will have headers for the 3 errors with the number of times Kim worked on each of the errors.

Thanks!

 

[Andrzejek]

You did not state which version of Excel you work with.

I have Excel 2010, and what I can do is:
Copy all names from column A to another sheet to column A
Highlight column A
Select: Data - in Data Tools: Remove Duplicates
A message box shows up, and I click OK

Would that work for you?

Have fun.

---- Andy

 

[mtdew]

I have Excel 2007. I can't use the Remove Duplicates or Filter functions because I send the sheet out to team members and the file is shared. The formula needs to update in real time as the team members make changes to the file.

 

[SkipVought]

Example Kim may work on 10 errors but her name only appears on the new list 1 time and then I will have headers for the 3 errors with the number of times Kim worked on each of the errors.

This is simply a COUNTIFS() is it not?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mtdew]

I don't need to know the number of times the name is on the list. I need to create a list of names.

Names Correction Steps Req
Needed Corrected Assist.
----------------------------------------------------------
Kim 0 3 5
Mary 3 9 7
Laura 2 0 0

For the three columns to count the errors I can use the countif formula. But to pull the names into the Names column I need a formula that only pulls the name over once.

 

[SkipVought]

Have you tried using a PivotTable?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mtdew]

Yes. What I am trying to do is exactly the results you get from a pivot table. However, the sheet has to be shared and updated in real time so a pivot table is not available. Neither is the Remove Duplicate because of the sheet being shared.

 

[SkipVought]

A PivotTable can be refreshed in real time, so i do not see the issue.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Deniall]

I had a similar problem a few years ago. I ended up using VBA to write a User Defined Function that took as its only input argument a columnar range of data (this being the list of names that might or might not contain duplicates). The UDF was an array function, and it returned a columnar array with the duplicates removed.

Using a function, albeit a user defined one, this meets your requirement of maintain the list in "real time".

Here is what I used, provided on a totally unsupported basis.

Public Function Remove_Dups(In_List As Range)
'
'  Takes as input a column of values.
'
'  Creates from these values a sorted list without duplicates, which
'  is returned to the calling spreadsheet as an array variable.
'
Dim InRows As Long, InCols As Long, OutRows As Long, OutCols As Long
Dim I As Long, J As Long, NumEntries As Long
Dim ErrText As String
Dim Ans() As Variant, SortedList() As Variant
Const FnName As String = "Function Remove_Dups"
Const EmptyMark As String = "-"
'
'  Get the sizes of the input range and the output range.
'
InRows = In_List.Rows.Count
InCols = In_List.Columns.Count
OutRows = Application.Caller.Rows.Count
OutCols = Application.Caller.Columns.Count
'
'  We now know the required sizes for several VBA arrays, so
'  declare them accordingly.
'
ReDim Ans(OutRows, OutCols)
ReDim SortedList(InRows, 1)
'
'  Apply a few checks to these array sizes before going any further.
'  (Have removed the "OutRows<InRows" test.)
'
If InCols <> 1 Or OutCols <> 1 Or InRows < 2 Then
    ErrText = "Problem with sizes of input or output ranges."
    GoTo ErrorReturn
End If
'
'  Create a VBA array containing the entries to be processed.
'  Skip over empty cells, and also skip cells containing
'  the "EmptyMark".
'
'  (The EmptyMark bit can be changed or dropped as required.)
'
NumEntries = 0
For I = 1 To InRows
    If Not IsEmpty(In_List(I, 1)) And In_List(I, 1) <> EmptyMark Then
        NumEntries = NumEntries + 1
        SortedList(NumEntries, 1) = In_List(I, 1)
    End If
Next I
'
'  If the input range contains no valid entries, go gentle into the night.
'
If NumEntries < 1 Then
    For I = 1 To OutRows
        Ans(I, 1) = EmptyMark       '  Could use "" here instead.
    Next I
    Remove_Dups = Ans
    Exit Function
End If
'
'  Sort the array.
'  Do this using some code filched from the Internet and used in
'  heaps of other places.  It appears below, as part of this module.
'
Call QuickSort(SortedList, 1, 1, NumEntries)
'
'  Scan through the sorted array, grabbing the first instance of
'  each unique entry as we go, and putting it into the output array.
'
J = 1
Ans(1, 1) = SortedList(1, 1)
For I = 2 To NumEntries
    If SortedList(I, 1) <> SortedList(I - 1, 1) Then
        J = J + 1
        If J > OutRows Then
            ErrText = "Output array needs more than " & OutRows & " rows."
            GoTo ErrorReturn
        End If
        Ans(J, 1) = SortedList(I, 1)
    End If
Next I
'
'  Fill the remainder of the output array with "Emptymark".
'
If J < OutRows Then
    For I = J + 1 To OutRows
        Ans(I, 1) = EmptyMark
    Next I
End If
'
'  It's all over, Red Rover.
'
Remove_Dups = Ans
Exit Function
'
'  Error handling area.
'
ErrorReturn:
For I = 1 To OutRows
    Ans(I, 1) = CVErr(xlErrNA)      '  Fill output cells with "#N/A"
Next I
MsgBox ErrText, , FnName
Remove_Dups = Ans
End Function


'——————————————————————————————————————————————————————————
Private Sub QuickSort(SortArray, col, L, R)
'
'  Performs a "quicksort" on a two-dimensional array.
'    SortArray  -  The two-dimensional array to be sorted.
'    col        -  The (single) column number containing the sort key.
'    L          -  The first row number of the band to be sorted.
'    R          -  The last row number of the band to be sorted.
'
'  Always sorts in ASCENDING order.
'
'  Grabbed off Google Groups by Deniall in June 2004.
'
'  Originally Posted by Jim Rech 10/20/98 Excel.Programming
'  Modified to sort on first column of a two dimensional array.
'  Modified to handle a sort column other than 1 (or zero).
'
Dim I As Long, J As Long, mm As Long
Dim x As Variant, y As Variant
'
'  Set new extremes to old extremes.
'  Get sort key for row in middle of new extremes.
'
I = L
J = R
x = SortArray((L + R) / 2, col)
'
'  Loop for all rows between the extremes.
'
While (I <= J)
    '
    '  Find the first row whose key is greater than that of the middle row.
    '
    While (SortArray(I, col) < x And I < R)
        I = I + 1
    Wend
    '
    '  Find the last row whose key is less than that of the middle row.
    '
    While (x < SortArray(J, col) And J > L)
        J = J - 1
    Wend
    '
    '  If the new "greater" row is smaller than the new "lesser" row
    '  swap them, then advance the pointers to the next rows.
    '
    If (I <= J) Then
      For mm = LBound(SortArray, 2) To UBound(SortArray, 2)
        y = SortArray(I, mm)
        SortArray(I, mm) = SortArray(J, mm)
        SortArray(J, mm) = y
      Next mm
        I = I + 1
        J = J - 1
    End If
Wend
'
'  Recurse to sort the lower then the upper halves of the extremes.
'
If (L < J) Then Call QuickSort(SortArray, col, L, J)
If (I < R) Then Call QuickSort(SortArray, col, I, R)
'
End Sub

 

[mtdew]

SkipVought

Pivot Tables cannot be refreshed when the sheet is shared. I love Pivot Tables. I tried.

Deniall

Thanks for this. I'll give it a try.

In the meantime I found something that appears to work. Removing the blanks is very slow so the solution is not ideal.

http://www.cpearson.com/excel/ListFunctions.aspx

http://www.cpearson.com/excel/NoBlanks.aspx

 

[SkipVought]

Sorry, I see that workbooks containing TABLES cannot be shared.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

I'd run a macro that 1) creates a workbook, 2) does a Query Transform (like a pivot) in that workbook, 3) Copy the results and 4) paste special -- VALUES, into your sheet.

but it seems as if Deniall has given you something that might workfor you. To install the code, you will have to UNSHARE, paste the code in a MODULE, save and then reshare, I do believe.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mtdew]

I don't get the sheet back after it has been modified by the team members so the macro wouldn't be run by me. I'm currently using the info found at

http://www.cpearson.com/excel/ListFunctions.aspx

http://www.cpearson.com/excel/NoBlanks.aspx

although removing blanks is very slow. And then sharing the worksheet makes it additionaly slow.

So nothing is really working well.

 

[Andrzejek]

mtdew,

Have you considered re-doing all of this in some kind of data base? Even in Access?

It could be very simple, easy, available to many users at tha same time, etc.

Just a suggestion....

Have fun.

---- Andy