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
