Combining data from 2 columns if they match each other

[fsanjaya]

Hello, I was wondering if anyone knows how to combine data from 2 separate columns into a new column only if they match each other? Attached below is a sample:

A and B are the 2 columns and C is the column containing the combined data.

 

[My Aswer Is This]

Sorry I gave an answer but then realized I did not have an answer.

 

[Anonymous1378]

This, perhaps?

Book16
	A	B	C
1	A	B	C
2		1	1
3		1	1
4	1		1
5	2
6	3
7		4	4
8	4		4
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=IF(COUNTIF($A$2:$A$8,$B2),$B2,IF(COUNTIF($B$2:$B$8,$A2),$A2,""))

 

[fsanjaya]

This, perhaps?

Book16
	A	B	C
1	A	B	C
2		1	1
3		1	1
4	1		1
5	2
6	3
7		4	4
8	4		4
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=IF(COUNTIF($A$2:$A$8,$B2),$B2,IF(COUNTIF($B$2:$B$8,$A2),$A2,""))

Hi! I tried out your formula (adjusted to the sheet I have) but it only shows me numbers from the 2nd column for some reason; for better representation, my data looks more like this:

Could it be that the formula doesn't work on the above data set because of the spaces in between 2 duplicates?

 

[Anonymous1378]

Perhaps I am misinterpreting what you want. When you say "only if they match each other", I have interpreted that to mean that if a value in column B can be found in column A, or if a value in column A can be found in column B, put it in column C.

If you mean that to put a value in column C if it appears more than once in either column A or B, try

=IF(OR(COUNTIF($A$2:$B$40,A2:B2)>1),CONCAT(A2:B2),"")

 

[fsanjaya]

Perhaps I am misinterpreting what you want. When you say "only if they match each other", I have interpreted that to mean that if a value in column B can be found in column A, or if a value in column A can be found in column B, put it in column C.

If you mean that to put a value in column C if it appears more than once in either column A or B, try

=IF(OR(COUNTIF($A$2:$B$40,A2:B2)>1),CONCAT(A2:B2),"")

Your initial interpretation is correct! If the number appears in both columns, I want to include all the instances it appears in both columns in the combined data column. 184 appears in both columns and so the 2 instances it appears in column 2 and the 1 instance it appears in column 1 are included in the combined data column. When I tested out the formula however, I keep getting numbers 185, 188, and 190 in the combined data column even though they aren't listed in the 2nd column. Sorry for the confusion!

 

[Anonymous1378]

So since 185, 188 and 190 aren't appearing in your image... the initial formula was correct after all?

 

[fsanjaya]

So since 185, 188 and 190 aren't appearing in your image... the initial formula was correct after all?

The combined data column in the second image I sent is what it should've looked like (I did that manually for representation of the result I want), so yes, the first formula should've been correct, but this is what I'm getting when I apply the first formula to my data set:

The formula inserted into the formula bar: =IF(COUNTIF($CJ$2:$CJ$40,$CK2),$CK2,IF(COUNTIF($CK$2:$CK$40,$CJ2),$CJ2,""))

 

[Anonymous1378]

Unfortunately, I am unable to replicate your error, so the problem should be with your data.

Book2
	CJ	CK	CL
2
3
4
5
6
7
8		184	184
9		184	184
10	184		184
11
12
13
14
15	185
16
17
18
19
20	188
21
22	190
23
24
25		216	216
26
27	216		216
28
29
30
31
32	220
33
34	220
35
36
37
38		221	221
39
40	221		221
Sheet2

Cell Formulas
Range	Formula
CL2:CL40	CL2	=IF(COUNTIF($CJ$2:$CJ$40,$CK2),$CK2,IF(COUNTIF($CK$2:$CK$40,$CJ2),$CJ2,""))

 

[Peter_SSs]

this is what I'm getting when I apply the first formula to my data set:

Could you give us that range of CJ1:CL40 with XL2BB so that we can have closer look at your actual data and formula?