Filter info from 2 columns

jandak

New Member
Joined
May 30, 2015
Messages
5
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,
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

L. Howard

Well-known Member
Joined
Oct 16, 2012
Messages
4,514
Hi jandak,

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

Howard


Code:
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

Well-known Member
Joined
Apr 25, 2014
Messages
1,811
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.

Code:
=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)),"")
 
Last edited:

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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​
mayjune
3​
2​
jonjonjohn
3​
johndamondan
4​
dandenizlara
5​
damondave
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:
Rich (BB code):

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

4) Now invoke...

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

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

C2, cse and copy down:
Rich (BB code):

=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

New Member
Joined
May 30, 2015
Messages
5

ADVERTISEMENT

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

jandak

New Member
Joined
May 30, 2015
Messages
5
Row\Col
A​
B​
C​
1​
mayjune
3​
2​
jonjonjohn
3​
johndamondan
4​
dandenizlara
5​
damondave
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:
Rich (BB code):

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

4) Now invoke...

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

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

C2, cse and copy down:
Rich (BB code):

=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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203

ADVERTISEMENT

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

New Member
Joined
May 30, 2015
Messages
5
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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

New Member
Joined
May 30, 2015
Messages
5
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,129,803
Messages
5,638,441
Members
417,025
Latest member
MusterDuster

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
Top