Comparing two columns

[gheyman]

I have two columns where in Column A (starting in row 7) I pasted/transposed the table headers from an old worksheet. In column B7 I pasted the Table headers from a new version of the table.

I want to have one formula in column C that would tell me

is the value in A and B the same (it exists in both tables in the same row). Then 1
Value is in A and B but not the same row. Then 2
Value is in A but not in B. Then 3
Value is in B but not in A. Then 4

A	B	Example of formula results (or something like this)
Example1	Example1	1
Example2	Example4	2,4
Example3	Example2	3,2
Example5	Example6	3,4

 

[KRice]

I don't quite follow your use of the term "value". Does "value" refer to something specifically, or merely the presence of something in the referenced cell? For example, the last line in your sample table (Example 5 / Example 6)...a value is in A and a different value is in B, so why does the desired result not include "2" also?

 

[Joe4]

I don't quite follow your use of the term "value". Does "value" refer to something specifically, or merely the presence of something in the referenced cell? For example, the last line in your sample table (Example 5 / Example 6)...a value is in A and a different value is in B, so why does the desired result not include "2" also?

Yeah, I find that example a bit confusing too. I think it has been simplified too much, to the point that we don't really understand what the actual data really looks like and what the expected result should look like.

gheyman,
Can you post an example using more realistic data, so we can get a better feel of what the data actually looks like, and what you want the actual result to look like?

 

[KRice]

I re-read the original post and think I understand...the desired result in column C is supposed to be a pair of results. The first of the pair of results uses the column A value as the baseline to determine whether it produces a 1, 2, 3, or 4. Then the second of the pair of results uses the column B value as the baseline to determine whether it produces a 1, 2, 3, or 4. But if that is the case, then the first line should be 1,1 rather than 1...unless another rule is invoked to deliver a single result if both are the same. All of the other results then make sense.

 

[KRice]

Like this maybe?

MrExcel_20240305.xlsx
	A	B	C
7	Old	New	Result
8	Ex1	Ex1	1
9	Ex2	Ex4	2,4
10	Ex3	Ex2	3,2
11	Ex5	Ex6	3,4
Sheet3

Cell Formulas
Range	Formula
C8:C11	C8	=TEXTJOIN(",",,IF(A8=B8,1,IF(ISNUMBER(MATCH(A8,$B$8:$B$11,0)),2,3)),IF(A8=B8,"",IF(ISNUMBER(MATCH(B8,$A$8:$A$11,0)),2,4)))

 

[gheyman]

Like this maybe?

MrExcel_20240305.xlsx
	A	B	C
7	Old	New	Result
8	Ex1	Ex1	1
9	Ex2	Ex4	2,4
10	Ex3	Ex2	3,2
11	Ex5	Ex6	3,4
Sheet3

Cell Formulas
Range	Formula
C8:C11	C8	=TEXTJOIN(",",,IF(A8=B8,1,IF(ISNUMBER(MATCH(A8,$B$8:$B$11,0)),2,3)),IF(A8=B8,"",IF(ISNUMBER(MATCH(B8,$A$8:$A$11,0)),2,4)))

Thank you

 

[KRice]

You're welcome...happy to help!