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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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
 
Upvote 0
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:
Upvote 0
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))),"")
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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).
 
Upvote 0

Forum statistics

Threads
1,214,769
Messages
6,121,433
Members
449,032
Latest member
egspen2

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