Assigning Same Serial Number to Duplicate Values In Two Columns

[myawan]

Please help me with achieving below task.
I want to assign same serial number to duplicate values in two columns. If the value in columnB cell matches with any cell in columnC, both will be assigned similar serial number in columnA. Below is the sample data, duplicate value 34 gets same serial number.

A	B	C
1	34	09
	56	20
	20	11
	21	56
1	57	34
	11	45
	09	57
	45	21

<tbody>
</tbody>

 

[igold]

Hi myawan,

Welcome to the MrExcel Forum.

In your above sample data you put a "1" for the duplicate value of 34. What about the other duplicates: 21, 57, 09, 45. What would your sample data look like if you followed your own given requirements. Especially for "57"...

 

[myawan]

Thanks @igold for the response.

Let me clear the problem in detail for better understanding. Basically the data which I have has 3000 rows and most of them are duplicates in reverse order. Data is combination of text plus numbers. Example is below:

Serial No.	A	B
1	A(1)	B(1)
2	A(2)	B(2)
3	A(3)	B(3)
4	A(4)	B(4)
1	B(1)	A(1)
2	B(2)	A(2)
3	B(3)	A(3)
4	B(4)	A(4)

<tbody>
</tbody>

 

[shg]

	A​	B​	C​	D​
1​	Serial No.​	A​	B​
2​	1​	A(1)​	B(1)​	A2: =IF(COUNTIF(C$1:C1, B2), INDEX(A$1:A1, MATCH(B2, C$1:C1, 0)), MAX(A$1:A1) + 1)
3​	2​	A(2)​	B(2)​
4​	3​	A(3)​	B(3)​
5​	4​	A(4)​	B(4)​
6​	1​	B(1)​	A(1)​
7​	2​	B(2)​	A(2)​
8​	3​	B(3)​	A(3)​
9​	4​	B(4)​	A(4)​

 

[myawan]

Thanks a lot @shg. You are a genius, you have saved me a lot of laborious hours.

 

[shg]

You're welcome.