If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Joining/appending two columns of data
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 |
#3
|
|||
|
|||
Joining/appending two columns of data
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 |
#4
|
|||
|
|||
Joining/appending two columns of data
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 _ _ ^ąŻ^ -- |
#5
|
|||
|
|||
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/ |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Crosstab query in report | Vincent DeLuca | Setting Up & Running Reports | 19 | July 13th, 2004 04:02 AM |
Merging multiple columns of data into one column | rhett miller | Worksheet Functions | 2 | June 10th, 2004 09:43 PM |
Five columns of data into a two bar chart | Scott | Charts and Charting | 2 | May 27th, 2004 09:31 PM |
possible to chart two sets of data (4 columns) with common X & Y axes? | Todd Ogasawara, MVP Mobile Devices | Charts and Charting | 2 | November 12th, 2003 08:55 PM |