Joining/appending two columns of data

I have two columns of data. For example; one with a list of people in the IT
department and the other a list of people in the whole company.

I am after a way of joining these two lists of data into one list and
removing the duplicates; ie only include the people who are in the IT
department once as they will appear on both lists.

I am at a loss how to do this. Any ideas?

Thanks.

Richard

Hi
as a starting point:
http://www.cpearson.com/excel/duplic...#InOneNotOther
http://www.cpearson.com/excel/duplic...tractingCommon
http://www.cpearson.com/excel/duplic...tractingUnique

--
Regards
Frank Kabel
Frankfurt, Germany


Richard Edwards wrote:
I have two columns of data. For example; one with a list of people in
the IT department and the other a list of people in the whole
company.

I am after a way of joining these two lists of data into one list and
removing the duplicates; ie only include the people who are in the IT
department once as they will appear on both lists.

I am at a loss how to do this. Any ideas?

Thanks.

Richard

If they aren't already there then
if col D has whole company and col E has the IT

Sub comparecol()
For Each c In Range("e1:e14")
x = Cells(Rows.Count, "d").End(xlUp).Row + 1
If Range("d1:d14").Find(c) Is Nothing Then
'MsgBox c.Address
Cells(x, "d") = c
End If
Next
End Sub
--
Don Guillett
SalesAid Software

"Richard Edwards" wrote in message
...
I have two columns of data. For example; one with a list of people in the
IT
department and the other a list of people in the whole company.

I am after a way of joining these two lists of data into one list and
removing the duplicates; ie only include the people who are in the IT
department once as they will appear on both lists.

I am at a loss how to do this. Any ideas?

Thanks.

Richard

Hi,

Option Explicit
Sub TEST()

Dim RNG_1 As Range
Dim RNG_2 As Range
Dim CL As Range
Dim R As Long

Set RNG_1 = Range("B2:B20")
Set RNG_2 = Range("D220")

For Each CL In RNG_1

R = Cells(Rows.Count, RNG_2.Column).End(xlUp).Row

If R RNG_2.Row Then
R = RNG_2.Row
Else
R = R + 1
End If

If RNG_2.Find(What:=CL.Value, LookAt:=xlWhole) Is Nothing Then
Cells(R, RNG_2.Column).Value = CL.Value
End If
Next

End Sub


--
Regards,
Soo Cheon Jheong
_ _
^¢¯^
--

One way :-
1. put both lists into 1 long one and sort on Column A.
2. in Cell C1 put formula =IF(A1=A2,1,0). This puts number 1
against duplicates.
3. Copy formula down column c.
4. select Column C and Copy. Then Edit/PasteSpecial/Values. This
converts formulas to values.
5. Sort the list on column C to Get all rows with number 1 together.
6. Select rows with number 1 and delete them.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/