Extract common numbers

[BenElliott]

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

 

[Marcelo Branco]

A small data sample (~10 rows) along with expected results would be helpful.

M.

 

[sandy666]

maybe use cartesian in PowerQuery

 

[mikerickson]

If the columns have the same header you can use one as a criteria range for an AdvanceFilter on the other column.

 

[BenElliott]

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]

 

[sandy666]

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]

[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]

 

[BenElliott]

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]

Try:

Excel Workbook
	A	B	C
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
Sheet

 

[Marcelo Branco]

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.

 

[Aladin Akyurek]

A different ibterpretation…

Book1
	D	E	F
1	Count
2	2
3
4			#list#
5	82	36	89
6	83	89	145
7	89	99
8	145	40
9	91	145
10	111	187
11	136	50
12	137
13	145
14	89
15	162
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))))