CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
deeps
deeps

Reputation: 33

How to find out the matching values from column A to Column B?

I have 30 values in my column A and 100 values in column B. I would like to keep values of column A only, need to find out the matching in column B and remove the others. Example of values : /x/member/info/text-column

I did try =vlookup(A2,B2:B100,4,FALSE). But it is not working. Could you assist me here.

Upvotes: 0

Views: 1424

Answers (4)

Scott Craner
Scott Craner

Reputation: 152450

Put this in C2 and copy down:

=INDEX(A:A,AGGREGATE(15,7,ROW($A$2:$A$30)/(ISNUMBER(MATCH($A$2:$A$30,B:B,0))),ROW($ZZ1)))

It will return the list of items in A that is in B without any extra steps.

If one has the Dynamic Array Formula FILTER():

=FILTER($A$2:$A$30,ISNUMBER(MATCH($A$2:$A$30,B:B,0)))

Upvotes: 0

Steven Byrne
Steven Byrne

Reputation: 134

I presume you want to keep this as a excel formula rather than VBA?

Your formula has not worked as you have put 4 when you have only referenced 1 column b maybe change the formula to =vlookup(A2,$B$2:$B$100,1,0) and see if that works. That would bring back the value of a repeat or error if it doesnt repeat. To remove the error you could do this =iferror(vlookup(A2,$B$2:$B$100,1,0),"") which would show a blank on any that are not found.

Alternatively you could do a countif which would count how many repeats are found. =countif($B$2:$B$100,A2) which may help.

Upvotes: 0

Anabas
Anabas

Reputation: 356

the forumla should be:

=vlookup(A2,$B$2:$B$100,1,0)

and then use filter to select the #NA to delete or

=countif($B$2:$B$100,A2)

and then use filter to select "0" to delete.

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

This will remove from column B items that are not in column A:

Sub RowCLeaner()
    Dim i As Long, r As Range
    For i = 100 To 1 Step -1
        v = Cells(i, "B").Value
        Set r = Range("A1:A30").Find(what:=v, After:=Range("A1"))
        If r Is Nothing Then Cells(i, "B").Delete shift:=(xlShiftUp)
    Next i
End Sub

Upvotes: 0

Related Questions