# Assigning Same Serial Number to Duplicate Values In Two Columns

#### myawan

##### New Member
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>

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### igold

##### Well-known Member
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

##### New Member
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

##### MrExcel MVP
 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

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

You're welcome.

Replies
7
Views
120
Replies
2
Views
37
Replies
3
Views
71
Replies
2
Views
32
Replies
1
Views
80