Extracting common numbers from two cells with comma-separated numbers

Usercode · Jul 4, 2020

Is there a simple way to extract common numbers from two cells with comma-separated numbers?

I have cells with 12 comma separated numbers in each cell. (They are not all unique. Some numbers can be repeated twice. but never more than twice. Numbers are all positive, and one or two digit numbers only)

My data is like so: they are in column A:

Rich (BB code):

11,11,13,15,16,18,20,20,26,27,28,29
8,9,10,12,13,14,18,20,21,22,24,28
13,13,14,14,15,17,18,19,20,21,23,25
6,6,8,10,12,14,15,17,18,20,20,25
11,13,17,18,19,19,22,25,26,28,28,31
7,9,15,16,17,18,23,24,24,25,26,27
7,9,11,12,12,15,16,16,18,18,20,23
9,11,13,15,18,22,23,24,25,28,29,29
7,9,10,11,12,12,13,14,15,16,19,22
5,10,11,12,12,16,17,18,20,22,24,25
7,10,13,16,16,17,18,19,21,23,24,24
10,14,16,18,18,19,21,23,23,25,27,28

The result I would like to have is like so:

I need a solution without separating values into different columns, please. Thanks for your help.

Since there can be numbers repeating twice in some cases, I am also open to a solution like this, too.

CA_Punit · Jul 4, 2020

Which Version of Excel do you use.??

Usercode · Jul 4, 2020

I am sorry for the late answer. My excel is 2016 edition.

CA_Punit · Jul 4, 2020

It can be done if you have Textjoin or Concat function. Other wise i don't know how to join the array with ","

Book3

A

B

C

D

E

F

1

11,11,13,15,16,18,20,20,26,27,28,29

13,18,20,20,28

2

8,9,10,12,13,14,18,20,21,22,24,28

13,14,18,20,21

3

13,13,14,14,15,17,18,19,20,21,23,25

14,14,15,17,18,20,25

4

6,6,8,10,12,14,15,17,18,20,20,25

17,18,25

5

11,13,17,18,19,19,22,25,26,28,28,31

17,18,25,26

6

7,9,15,16,17,18,23,24,24,25,26,27

7,9,15,16,18,23

7

7,9,11,12,12,15,16,16,18,18,20,23

9,11,15,18,18,23

8

9,11,13,15,18,22,23,24,25,28,29,29

9,11,13,15,22

9

7,9,10,11,12,12,13,14,15,16,19,22

10,11,12,12,16,22

10

5,10,11,12,12,16,17,18,20,22,24,25

10,16,17,18,24

11

7,10,13,16,16,17,18,19,21,23,24,24

10,16,16,18,19,21,23

12

10,14,16,18,18,19,21,23,23,25,27,28

13

Sheet4

Cell Formulas
Range	Formula
F1:F12	F1	=TEXTJOIN(",",TRUE,IFERROR(INDEX(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),(ROW(INDIRECT("A1:A"&LEN(A1)))-ROW(A1))LEN(A1)+1,LEN(A1))),AGGREGATE(15,6,1/(1/(ISNUMBER(MATCH(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),(ROW(INDIRECT("A1:A"&LEN(A1)))-ROW(A1))LEN(A1)+1,LEN(A1))),TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),(ROW(INDIRECT("A1:A"&LEN(A2)))-ROW(A1))LEN(A2)+1,LEN(A2))),0))(ROW(INDIRECT("A1:A"&LEN(A1)))))),(ROW(INDIRECT("A1:A"&LEN(A1)))))),""))

Amit Tandon · Jul 4, 2020

A solution using formulas (thought this may be a bit heavy on resources depending on the number of rows you may want to match):

26Aug19.xlsx

B

1

13,18,20,28

Sheet11

Cell Formulas
Range	Formula
B1	B1	=TEXTJOIN(",",,IFERROR(IF(FREQUENCY(IFERROR(MATCH(TRIM(MID(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),(ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))-1)LEN(A1)+1,LEN(A1))),TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1))-1)LEN(A2)+1,LEN(A2))),0),""),ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,",",""))+1))),TRIM(MID(SUBSTITUTE(A2,",",REPT(" ",LEN(A2))),(ROW(INDIRECT("1:"&LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1))-1)*LEN(A2)+1,LEN(A2))),""),""))
Press CTRL+SHIFT+ENTER to enter array formulas.

Usercode · Jul 4, 2020

Thanks a lot, guys! That was very helpful.

Peter_SSs · Jul 4, 2020

Usercode said:
That was very helpful.

Not sure how that was helpful when you don't have the TEXTJOIN function in excel 2016?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. (Thanks CA_Punit, I used your data)

You could consider a user-defined function like this. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

VBA Code:

Function Repeats(s1 As String, s2 As String) As String
  Dim d As Object
  Dim itm As Variant

  Set d = CreateObject("Scripting.Dictionary")
  For Each itm In Split(s1, ",")
    d(itm) = 1
  Next itm
  For Each itm In Split(s2, ",")
    d(itm) = d(itm) & "|"
  Next itm
  For Each itm In d.keys
    If InStr(1, d(itm), "|") > 1 Then Repeats = Repeats & "," & itm
  Next itm
  Repeats = Mid(Repeats, 2)
End Function

Usercode 2020-07-04 1.xlsm

A

B

1

11,11,13,15,16,18,20,20,26,27,28,29

13,18,20,28

2

8,9,10,12,13,14,18,20,21,22,24,28

13,14,18,20,21

3

13,13,14,14,15,17,18,19,20,21,23,25

14,15,17,18,20,25

4

6,6,8,10,12,14,15,17,18,20,20,25

17,18,25

5

11,13,17,18,19,19,22,25,26,28,28,31

17,18,25,26

6

7,9,15,16,17,18,23,24,24,25,26,27

7,9,15,16,18,23

7

7,9,11,12,12,15,16,16,18,18,20,23

9,11,15,18,23

8

9,11,13,15,18,22,23,24,25,28,29,29

9,11,13,15,22

9

7,9,10,11,12,12,13,14,15,16,19,22

10,11,12,16,22

10

5,10,11,12,12,16,17,18,20,22,24,25

10,16,17,18,24

11

7,10,13,16,16,17,18,19,21,23,24,24

10,16,18,19,21,23

12

10,14,16,18,18,19,21,23,23,25,27,28

Repeats

Cell Formulas
Range	Formula
B1:B12	B1	=Repeats(A1,A2)

Tom.Jones · Jul 4, 2020

@Usercode,

You say that in B1 must be 2 of 20 cause 20 is 2 times in A1
Then why in B2, is not the number 13 twice?
The same goes for the following cells in column B (if cells have pair of identical numbers)

Usercode · Jul 20, 2020

@Tom.Jones I am sorry for the late answer. I haven't noticed your message before.
The data goes upwards, i mean new data added at the top. so in the fist case, there is an increase, in the second case, there is a decrease. This works for me fine. thanks.

Usercode · Jul 20, 2020

Peter_SSs said:
Not sure how that was helpful when you don't have the TEXTJOIN function in excel 2016?

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I also suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. (Thanks CA_Punit, I used your data)

You could consider a user-defined function like this. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

VBA Code:

Function Repeats(s1 As String, s2 As String) As String Dim d As Object Dim itm As Variant Set d = CreateObject("Scripting.Dictionary") For Each itm In Split(s1, ",") d(itm) = 1 Next itm For Each itm In Split(s2, ",") d(itm) = d(itm) & "|" Next itm For Each itm In d.keys If InStr(1, d(itm), "|") > 1 Then Repeats = Repeats & "," & itm Next itm Repeats = Mid(Repeats, 2) End Function

Usercode 2020-07-04 1.xlsm
A B
1 11,11,13,15,16,18,20,20,26,27,28,29 13,18,20,28
2 8,9,10,12,13,14,18,20,21,22,24,28 13,14,18,20,21
3 13,13,14,14,15,17,18,19,20,21,23,25 14,15,17,18,20,25
4 6,6,8,10,12,14,15,17,18,20,20,25 17,18,25
5 11,13,17,18,19,19,22,25,26,28,28,31 17,18,25,26
6 7,9,15,16,17,18,23,24,24,25,26,27 7,9,15,16,18,23
7 7,9,11,12,12,15,16,16,18,18,20,23 9,11,15,18,23
8 9,11,13,15,18,22,23,24,25,28,29,29 9,11,13,15,22
9 7,9,10,11,12,12,13,14,15,16,19,22 10,11,12,16,22
10 5,10,11,12,12,16,17,18,20,22,24,25 10,16,17,18,24
11 7,10,13,16,16,17,18,19,21,23,24,24 10,16,18,19,21,23
12 10,14,16,18,18,19,21,23,23,25,27,28
Repeats
Cell Formulas
Range Formula
B1:B12 B1 =Repeats(A1,A2)

Thanks, Peter. I did what you advised. and thanks for your solution too.

Extracting common numbers from two cells with comma-separated numbers

Usercode

Board Regular

Excel Facts

CA_Punit

Well-known Member

Usercode

Board Regular

CA_Punit

Well-known Member

Amit Tandon

Board Regular

Usercode

Board Regular

Peter_SSs

MrExcel MVP, Moderator

Tom.Jones

Well-known Member

Usercode

Board Regular

Usercode

Board Regular

Similar threads

Forum statistics

Share this page

Extracting common numbers from two cells with comma-separated numbers

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Board Regular

MrExcel MVP, Moderator

Well-known Member

Board Regular

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock