Return Unique Entries Only

[Matt]

Hello, best to show in an example what I'm looking for help on:

Sheet 1:

A1 Names
A2 Bob
A3 Ralph
A4 Jane
A5 Jo

Sheet 2:

A1 Names
A2 Jo
A3 Julie
A4 Ralph
A5 Roger

What I am trying to return is each person's name in sheet 3, noting
that some names are repeated and I only want one return per name, e.g:

Sheet 3:

A1 Names
A2 Bob
A3 Ralph
A4 Jane
A5 Jo
A6 Julie
A7 Roger

I need to use equations only, and can't use filters, or 3rd party
software add ins etc. Thanks in advance for looking into this.

 

[Don Guillett Excel MVP]

Hello, best to show in an example what I'm looking for help on:

Sheet 1:

A1 Names
A2 Bob
A3 Ralph
A4 Jane
A5 Jo

Sheet 2:

A1 Names
A2 Jo
A3 Julie
A4 Ralph
A5 Roger

What I am trying to return is each person's name in sheet 3, noting
that some names are repeated and I only want one return per name, e.g:

Sheet 3:

A1 Names
A2 Bob
A3 Ralph
A4 Jane
A5 Jo
A6 Julie
A7 Roger

I need to use equations only, and can't use filters, or 3rd party
software add ins etc. Thanks in advance for looking into this.

Homework?

 

[Ron Rosenfeld]

Hello, best to show in an example what I'm looking for help on:

Sheet 1:

A1 Names
A2 Bob
A3 Ralph
A4 Jane
A5 Jo

Sheet 2:

A1 Names
A2 Jo
A3 Julie
A4 Ralph
A5 Roger

What I am trying to return is each person's name in sheet 3, noting
that some names are repeated and I only want one return per name, e.g:

Sheet 3:

A1 Names
A2 Bob
A3 Ralph
A4 Jane
A5 Jo
A6 Julie
A7 Roger

I need to use equations only, and can't use filters, or 3rd party
software add ins etc. Thanks in advance for looking into this.

Perhaps someone will come up with a way of doing it with formulas, but it can
be done simply with a VBA Macro (which would get distributed with the
workbook).

To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro
by name, and <RUN>.

Note that you may have to change the Sheet Names and range specifiers if they
don't match what you've written above:

========================================
Option Explicit
Sub UniqueNames()
Dim ws As Worksheet
Dim rg As Range, c As Range
Dim rDest As Range
Dim col As Collection
Dim i As Long

Set rDest = Worksheets("Sheet3").Range("A1")
Set col = New Collection

For Each ws In Worksheets
If ws.Name = "Sheet3" Then Exit For
With ws
Set rg = .Range("A1", .Cells(Rows.Count, "A").End(xlUp))
For Each c In rg
On Error Resume Next
col.Add c.Text, c.Text
On Error GoTo 0
Next c
End With
Next ws

For i = 1 To col.Count
rDest.Offset(i - 1, 0).Value = col(i)
Next i
End Sub
================================
--ron

 

[Pete_UK]

If you want a formula solution, put this in B2 of Sheet2:

=IF(A2="","",IF(ISNUMBER(MATCH(A2,Sheet1!A:A,0)),"",MAX(B$1:B1)+1))

and copy this down as far as you think you will need (i.e. it can go
beyond the number of names, as it will return blanks).

Then in A2 in Sheet3 you can put this:

=IF(Sheet1!A2<>"",Sheet1!A2,IF(ISNA(MATCH(ROW(A1)-COUNTA(Sheet1!A:A)
+1,Sheet2!B:B,0)),"",INDEX(Sheet2!A:A,MATCH(ROW(A1)-COUNTA(Sheet1!A:A)
+1,Sheet2!B:B,0))))

and you can copy this down as far as you need.

I've tested it with more names in Sheet1 than Sheet2, and vice versa,
and it seems to work in all cases (well, assuming that you don't have
duplicated names on one sheet).

Hope this helps.

Pete

 

[Pete_UK]

Slightly more efficient if you put this in B1 of Sheet2:

=COUNTA(Sheet1!A:A)-1

the same formula in B2 of Sheet2:

=IF(A2="","",IF(ISNUMBER(MATCH(A2,Sheet1!A:A,0)),"",MAX(B$1:B1)+1))

(copied down as before),and this amended formula in A2 of Sheet3:

=IF(Sheet1!A2<>"",Sheet1!A2,IF(ISNA(MATCH(ROW(A1),Sheet2!B:B,
0)),"",INDEX(Sheet2!A:A,MATCH(ROW(A1),Sheet2!B:B,0))))

again, copied down.

Hope this helps.

Pete

 

[Matt]

Slightly more efficient if you put this in B1 of Sheet2:

=COUNTA(Sheet1!A:A)-1

the same formula in B2 of Sheet2:

=IF(A2="","",IF(ISNUMBER(MATCH(A2,Sheet1!A:A,0)),"",MAX(B$1:B1)+1))

(copied down as before),and this amended formula in A2 of Sheet3:

=IF(Sheet1!A2<>"",Sheet1!A2,IF(ISNA(MATCH(ROW(A1),Sheet2!B:B,
0)),"",INDEX(Sheet2!A:A,MATCH(ROW(A1),Sheet2!B:B,0))))

again, copied down.

Hope this helps.

Pete







- Show quoted text -

Thanks for both solutions. Up and running now

 

[Pete_UK]

Glad to hear that, Matt - thanks for feeding back.

Pete