Extract common numbers

BenElliott

Board Regular
Joined
Jul 19, 2012
Messages
142
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
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,806
If the columns have the same header you can use one as a criteria range for an AdvanceFilter on the other column.
 
Last edited:

BenElliott

Board Regular
Joined
Jul 19, 2012
Messages
142
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'

A
B
C
Count of Col 'A'
1
62
36
89
2
82
89
145
3
83
99
4
89
40
5
91
145
6
111
187
7
136
50
8
137
9
145
10
151
11
162

<tbody>
</tbody>
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
3,837
just for fun ;)

With PowerQuery

ABC
62​
36​
89​
82​
89​
145​
83​
99​
89​
40​
91​
145​
111​
187​
136​
50​
137​
145​
151​
162​

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:

BenElliott

Board Regular
Joined
Jul 19, 2012
Messages
142
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.
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,345
Office Version
365
Platform
Windows
Try:
Excel Workbook
ABC
16236
28289145
38399
48940
591145
6111187
713650
8137
9145
10151
11162
Sheet
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,317
Using your data sample in post 5


A
B
C
1
Count​
2
2​
3
4
5
82​
36​
89​
6
83​
89​
145​
7
89​
99​
8
91​
40​
9
111​
145​
10
136​
187​
11
137​
50​
12
145​
13
151​
14
162​
15

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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,138
A different ibterpretation…

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">Count</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">#list#</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">82</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">36</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">89</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">83</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">89</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">145</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">89</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">99</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">145</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">40</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">91</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">145</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">111</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">187</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">136</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">50</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">137</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">145</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">89</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;">162</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #333333;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p>

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))))
 

Forum statistics

Threads
1,082,371
Messages
5,365,055
Members
400,819
Latest member
Gossow

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top