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?

 

[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