0

I have a dataset from three different sources of the same variable that needs to be consolidated. The three columns need to be combined into one based on similar rows. Three more columns need to be created showing values not in the union of the three.

Here's the data:

Column1 Column2 Column3
1       1       1
2       2       2
3       4       4
5       5       6
6       6       7
8       8       8

Desired result:

Combined_column_name  Unique_column1  Unique_column2 Unique_column3
1                     3               4              4
2                     5               5              6
8                     6               6              7

Edit: Attempted Method

I got the distinct values separate from the duplicate, but not in separate columns.

Step 1: Combine into one column

Combined
1
2
3
5
6
8
1
2
4
5
6
7
1
2
4
6
7
8

Step 2: Do =COUNTIF(A:A,A2)=1 and filter only true to show unique

Combined    TEST
3           TRUE
6           TRUE
8           TRUE
1           TRUE
2           TRUE
5           TRUE
6           TRUE

Step 3: Copy TRUE and FALSE values into other columns. Remove duplicates for FALSE

Combined    TEST    DISTINCT    DUPLICATE
1           FALSE   3           1
2           FALSE   6           2
3           TRUE    8           5
5           FALSE   1           4
6           TRUE    2           7
8           TRUE    5           6
1           TRUE    6           8
2           TRUE        
4           FALSE       
5           TRUE        
6           TRUE        
7           FALSE       
1           FALSE       
2           FALSE       
4           FALSE       
6           FALSE       
7           FALSE       
8           FALSE
Scott Davis
  • 153
  • 2
  • 2
  • 8
  • 1
    Interesting. What have you tried already? – Raystafarian Aug 13 '15 at 15:50
  • Is the data numerical? Or was that just your example? – Excellll Aug 13 '15 at 18:59
  • @Raystafarian I found a way of filtering out the duplicates after eliminating the distinct rows. I still need to separate the distinct rows into columns according to the columns they came from. First step involves putting all the columns into one, then using a COUNTIF() function and show TRUE for distinct, but FALSE for similar. Here's a link to a similar method: http://superuser.com/questions/705991/how-to-remove-non-duplicate-records-in-excel-2010 – Scott Davis Aug 13 '15 at 19:55
  • @Excellll The data is all numerical. – Scott Davis Aug 13 '15 at 20:03

0 Answers0