Assigning Same Serial Number to Duplicate Values In Two Columns

myawan

New Member
Joined
Mar 2, 2019
Messages
5
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>
 

Some videos you may like

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
Joined
Jul 8, 2014
Messages
2,503
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Mar 2, 2019
Messages
5
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
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
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
Joined
Mar 2, 2019
Messages
5
Thanks a lot @shg. You are a genius, you have saved me a lot of laborious hours.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
You're welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,969
Messages
5,525,960
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top