Filter info from 2 columns

[jandak]

I need a hint is solving Excel task. I have two columns with data (let's say column a contains a list of people I have met in May; column b has a list of people I am planning to meet in June).
Is there a function in Excel which will compare both columns and give me a list of people which I have met in May but which are not included in list of planned meetings in June and show results in column C?
Many thanks in advance,

 

[L. Howard]

Hi jandak,

Try this in the sheet module, run from button or do alt + f8 > select the macro name > Run

Howard

Option Explicit

Sub InA_NotB_ListC()

  Dim lRowCount As Long
  lRowCount = Cells(Rows.Count, "A").End(xlUp).Row
  
Application.ScreenUpdating = False

  With Range("C2").Resize(lRowCount - 1)
  
    .Formula = "=IF(COUNTIF($B$2:$B$10,A2),"""",A2)": .Value = .Value
     Columns("C").Sort key1:=Range("C2"), _
      order1:=xlAscending, Header:=xlNo
    
  End With
  
Application.ScreenUpdating = True
End Sub

 

[MarcelBeug]

Without VBA and assuming your data starts at row 2, you can use the following array formula (confirm with CTRL-SHIFT-ENTER, not just ENTER) in C2, copy down.

=IFERROR(INDEX(INDIRECT("A2:A"&COUNTA(A:A)),SMALL(IF(ISNA(MATCH(INDIRECT("A2:A"&COUNTA(A:A)),INDIRECT("B2:B"&COUNTA(B:B)),0)),ROW(INDIRECT("A2:A"&COUNTA(A:A)))-1),ROW(C2)-ROW(C$2)+1)),"")

 

[Aladin Akyurek]

I need a hint is solving Excel task. I have two columns with data (let's say column a contains a list of people I have met in May; column b has a list of people I am planning to meet in June).
Is there a function in Excel which will compare both columns and give me a list of people which I have met in May but which are not included in list of planned meetings in June and show results in column C?
Many thanks in advance,

Row\Col	A​	B​	C​
1​	may	june	3​
2​	jon	jon	john
3​	john	damon	dan
4​	dan	deniz	lara
5​	damon	dave
6​	lara
7​	dave

1) Select A2:A7 and name the selection MayList.

2) Select B2:B5 and name the selection JunList.

3) Activate Formulas | Name Manager and define Ivec (from integer vector) as referring to:

=ROW(MayList)-ROW(INDEX(MayList,1,1))+1

4) Now invoke...

C1, control+shift+enter (cse), not just enter:

=SUM(IF(1-ISNUMBER(MATCH(MayList,JunList,0)),1))

C2, cse and copy down:

=IF(ROWS(C$2:C2)<=C$1,INDEX(MayList,SMALL(IF(MayList<>"",
    IF(1-ISNUMBER(MATCH(MayList,JunList,0)),Ivec)),ROWS(C$2:C2))),"")

 

[jandak]

unfortunately gives no result- all cells stays blank(confirmed with CTRL+SHIFT+ENTER).

 

[jandak]

Row\Col	A​	B​	C​
1​	may	june	3​
2​	jon	jon	john
3​	john	damon	dan
4​	dan	deniz	lara
5​	damon	dave
6​	lara
7​	dave

<tbody>
</tbody>

1) Select A2:A7 and name the selection MayList.

2) Select B2:B5 and name the selection JunList.

3) Activate Formulas | Name Manager and define Ivec (from integer vector) as referring to:

=ROW(MayList)-ROW(INDEX(MayList,1,1))+1

4) Now invoke...

C1, control+shift+enter (cse), not just enter:

=SUM(IF(1-ISNUMBER(MATCH(MayList,JunList,0)),1))

C2, cse and copy down:

=IF(ROWS(C$2:C2)<=C$1,INDEX(MayList,SMALL(IF(MayList<>"",
    IF(1-ISNUMBER(MATCH(MayList,JunList,0)),Ivec)),ROWS(C$2:C2))),"")

did exactly as described above, but received only 1 name in column C while in fact should be 44(have counted manually).
also in your example it shows 3- john, dan, lara. where can be the problem?

 

[Aladin Akyurek]

did exactly as described above, but received only 1 name in column C while in fact should be 44(have counted manually).
also in your example it shows 3- john, dan, lara. where can be the problem?

Did you apply control+shift+enter, that is, press down the control and the shift keys at the same time while you hit the enter key? If done properly, Excel itself puts { and } around the formula.

 

[jandak]

Did you apply control+shift+enter, that is, press down the control and the shift keys at the same time while you hit the enter key? If done properly, Excel itself puts { and } around the formula.

either its time to change my keyboard, or my fingers are not listening to me - just tried once again and now it works. now will try to apply it for my actual list of names.

thanks for the advice!

 

[Aladin Akyurek]

either its time to change my keyboard, or my fingers are not listening to me - just tried once again and now it works. now will try to apply it for my actual list of names.

thanks for the advice!

You are welcome.

 

[jandak]

You are welcome.

Just tried to reverse it in order to have also list of names which were added in June but something didnt worked out.

in c1 cse:

=SUM(IF(1-ISNUMBER(MATCH(ThisMonth;LastMonth;0));1))

in c2 cse:

=IF(ROWS(C$2:C2)<=C$1;INDEX(LastMonth;SMALL(IF(ThisMonth<>"";
IF(1-ISNUMBER(MATCH(ThisMonth;LastMonth;0));Ivec2));ROWS(C$2:C2)));"")

where Ivec2:
=ROW(ThisMonth)-ROW(INDEX(ThisMonth;1;1))+1

as a result i have a list of names corresponding to new names in June(ThisMonth) but from list of May(LastMonth).
for example, new name is in cell b4(Deniz) but in list appears name from a4(dan).