Consolidation of Data

[raymaster98]

Using Excel 2003:

In column G, I have a list of names. Many of the names repeat on the list. I am looking for a formula that I can copy down in another column that will list each name only once.

Ex:

G1 Bill Smith
G2 John Adams
G3 Bill Smith
G4 Bill Smith
G5 Jessica Matthews
G6 John Adams
G7 Ralph Campbell

If correct, the formula should return:

A1 Bill Smith
A2 John Adams
A3 Jessica Matthews
A4 Ralph Campbell

Any help would be appreciated. Thanks, Kenny

 

[raymaster98]

Thanks Marcelo,

The formula works great. I overlooked #9 and #10 in the previous thread.

In my case, the following formula was the ticket in cell K2:

{=INDEX($G$2:$G$1770,MATCH(0,COUNTIF($G$2:$G$1770,"<"&$G$2:$G$1770)-SUM(COUNTIF($G$2:$G$1770,"="&K$1:K1)),0))}

One last thing would make it perfect. In column G is the list of names. In column H is whether the person showed up for their appointment or not (entered as "NO SHOW"). I need to edit the formula in K2 to return only the names in column G in which they were a "NO SHOW" in column H.

Any help would be most appreciated. Thanks, Kenny

 

[Marcelo Branco]

Kenny,

One question: if you have NO SHOW for, for example, John Smith do you have NO SHOW also for all the duplicates instances of John Smith?

M.

 

[raymaster98]

No. There may be one instance of a no show, but then he later showed, which would be reflected in another cell in column H. Thanks, Kenny

 

[Marcelo Branco]

I have to think how to adapt the formula

M.

 

[raymaster98]

Thanks Marcelo. There is no hurry. 10 minutes from now will be fine. JK

 

[Marcelo Branco]

Sorry, i need more than 10 minutes

First idea (not so good...)
Create a helper column with this formula (i'm using the data of OP)

=IF(ISERR(SMALL(IF(H1:H7<>"NO SHOW",ROW(G1:G7)),ROW(G1:G7))),"",INDEX(G1:G7,SMALL(IF(H1:H7<>"NO SHOW",ROW(G1:G7)),ROW(G1:G7))))
Ctrl+Shift+Enter

Ugly...

Hope tomorrow i can find a better solution (2:47 am in Rio...i have to sleep)

M.

 

[raymaster98]

Thanks so much for your help. I'll try it tomorrow. Only an hour earlier here, but I need sleep also. Talk to you tomorrow. Kenny

 

[Marcelo Branco]

Hi Kenny,

No success with formula ...
but i think this works

Please, try it on a test-workbook

Sub RemoveDuplicates2()
'Thread 535977 - MrExcel Forum
'Based in [URL]http://j-walk.com/ss/excel/tips/tip47.htm[/URL]
'Adapted to check the existence of "No Show" in column H
'Data in column G beginning in G1
    Dim AllCells As Range, Cell As Range
    Dim NoDupes As New Collection
    Dim i As Integer, j As Integer
    Dim Swap1, Swap2, Item
    Dim lastRow As Long
 
    With ActiveSheet
        lastRow = Cells(Rows.Count, "G").End(xlUp).Row
    End With
 
'   The items are in G1:G & lastRow
    Set AllCells = Range("G1:G" & lastRow)
 
'   The next statement ignores the error caused
'   by attempting to add a duplicate key to the collection.
'   The duplicate is not added - which is just what we want!
    On Error Resume Next
    For Each Cell In AllCells
        If Cell <> "" And UCase(Cell.Offset(, 1).Value) <> "NO SHOW" Then
            NoDupes.Add Cell.Value, CStr(Cell.Value)
'           Note: the 2nd argument (key) for the Add method must be a string
        End If
    Next Cell
'   Resume normal error handling
    On Error GoTo 0
'   Sort the collection (optional)
    For i = 1 To NoDupes.Count - 1
        For j = i + 1 To NoDupes.Count
            If UCase(NoDupes(i)) > UCase(NoDupes(j)) Then
                Swap1 = NoDupes(i)
                Swap2 = NoDupes(j)
                NoDupes.Add Swap1, before:=j
                NoDupes.Add Swap2, before:=i
                NoDupes.Remove i + 1
                NoDupes.Remove j + 1
            End If
        Next j
    Next i
 
    i = 0
 
    '   Add the sorted, non-duplicated items to Column A
    Range("A:A").ClearContents
    For Each Item In NoDupes
        i = i + 1
        Range("A" & i) = Item
    Next Item
 
End Sub

I think is better dont use ActiveSheet and be specific using the name of your sheet, something like this

With Sheets("Sheet1")
        lastRow = Cells(Rows.Count, "G").End(xlUp).Row
End With
 
Set AllCells = Sheets("Sheet1").Range("G1:G" & lastRow)

Safer!

HTH

M.