Extract common numbers

BenElliott

BenElliott

Board Regular
Joined
Jul 19, 2012
Messages
144
I'm trying to compare two columns of numbers and extract the numbers that are common to both.

Cell A2 contains the number of filled cells in column 'A'. Column 'B' has list 2.

I can use the following to extract the numbers but it leaves blank cells in the resultant column 'C': =IF(ISERROR(MATCH(A5,$D$5:$D$200,0)),"",A5)

If I use the following:=IF(ROWS(A$5:A5)>$A$2,"",INDEX($D$5:$D$200,AGGREGATE(15,6,(ROW($B$5:$B$200)-ROW($B$5)+1)/ISNUMBER(MATCH($B$5:$B$200,$A$5:$A$200,0)),ROWS($E$5:E5)))) I get the first common value in every cell of column 'C'

My head is spinning. Where am I going wrong?

Thanks for your patience.

Ben
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Sort by date Sort by votes
A small data sample (~10 rows) along with expected results would be helpful.

M.
 
Upvote 0
If the columns have the same header you can use one as a criteria range for an AdvanceFilter on the other column.
 
Last edited:
Upvote 0
I'm s
Sorry, Marcelo. This shows how mushy my brain is this evening. The table below shows part of my two lists in Columns 'A' and 'B'. A I mentioned in my original question, cell A2 is the count of the data in Column 'A'. The full possible list extends over rows 5-200. I am trying to find the common numbers in 'A' and 'B' and put the result in Column 'C'

[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]Count of Col 'A'
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]62
[/TD]
[TD]36
[/TD]
[TD]89
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]82
[/TD]
[TD]89
[/TD]
[TD]145
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]83
[/TD]
[TD]99
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]89
[/TD]
[TD]40
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]91
[/TD]
[TD]145
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]111
[/TD]
[TD]187
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]136
[/TD]
[TD]50
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]137
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]145
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]151
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]162
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
just for fun ;)

With PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]A[/td][td=bgcolor:#5B9BD5]B[/td][td=bgcolor:#70AD47]C[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
62​
[/td][td=bgcolor:#DDEBF7]
36​
[/td][td=bgcolor:#E2EFDA]
89​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
82​
[/td][td]
89​
[/td][td]
145​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
83​
[/td][td=bgcolor:#DDEBF7]
99​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
89​
[/td][td]
40​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
91​
[/td][td=bgcolor:#DDEBF7]
145​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
111​
[/td][td]
187​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
136​
[/td][td=bgcolor:#DDEBF7]
50​
[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
137​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
145​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
151​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
162​
[/td][td][/td][td][/td][/tr]
[/table]

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom1" = Table.AddColumn(Table.ExpandTableColumn(Table.AddColumn(Source, "Custom", each Table2), "Custom", {"B"}, {"B"}), "Custom", each [A]=[B]),
    #"Renamed Columns" = Table.RenameColumns(Table.SelectColumns(Table.SelectRows(#"Added Custom1", each ([Custom] = true)),{"A"}),{{"A", "C"}})
in
    #"Renamed Columns"[/SIZE]
 
Last edited:
Upvote 0
Thanks, Mike.

I am aware I can use Advance filter but I need to do this through Array formulae because of everything else that is going on in the workbook, hence my formulae in my original question.
 
Upvote 0
Try:
Excel Workbook
ABC
1623689
28289145
38399
48940
591145
6111187
713650
8137
9145
10151
11162
Sheet
 
Upvote 0
Using your data sample in post 5


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
Count​
[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
2​
[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
82​
[/td][td]
36​
[/td][td]
89​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
83​
[/td][td]
89​
[/td][td]
145​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td]
89​
[/td][td]
99​
[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td]
91​
[/td][td]
40​
[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9​
[/td][td]
111​
[/td][td]
145​
[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
10​
[/td][td]
136​
[/td][td]
187​
[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
11​
[/td][td]
137​
[/td][td]
50​
[/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
12​
[/td][td]
145​
[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
13​
[/td][td]
151​
[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
14​
[/td][td]
162​
[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
15​
[/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in A2 (qty of numbers in A5:A200 present in B5:B200)
=SUMPRODUCT(--ISNUMBER(MATCH(A$5:A$200,B$5:B$200,0)))

Formula in C5 copied down
=IF(ROWS(C$5:C5)>A$2,"",INDEX(A$5:A$200,AGGREGATE(15,6,(ROW(A$5:A$200)-ROW(A$5)+1)/ISNUMBER(MATCH(A$5:A$200,B$5:B$200,0)),ROWS(C$5:C5))))

M.
 
Upvote 0
A different ibterpretation…


Book1
DEF
1Count
22
3
4#list#
5823689
68389145
78999
814540
991145
10111187
1113650
12137
13145
1489
15162
Sheet1


In D2 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH($D$5:$D$15,$E$5:$E$11,0)),$D$5:$D$15),$D$5:$D$15),1))

In F5 control+shift+enter, not just enter, and copy down:

=IF(ROWS($F$5:F5)>$D$2,"",MIN(IF(ISNUMBER(MATCH($D$5:$D$15,$E$5:$E$11,0)),IF(ISNA(MATCH($D$5:$D$15,$F$4:F4,0)),$D$5:$D$15))))
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Extract 8 character number from text string
2
Replies
15
Views
601
Fluff
Fluff
B
  • Solved
Extracting a specific number of characters from a multiline cell
Replies
7
Views
318
etaf
etaf
S
  • Solved
Copying Formulas with Formula.R1C1 Results in Rows and Columns Incrementing by 2
Replies
5
Views
559
Alex Blakenburg
A
Jeffrey Mahoney
  • Solved
LET Function Letting Me Down
Replies
7
Views
180
Jeffrey Mahoney
Jeffrey Mahoney
serge
  • Question Question
#N/A cell skip.
Replies
3
Views
245
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,162
Messages
6,170,431
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top