CODEWITHSUNDEEP
excel
val
val

Reputation: 1709

Matching wildcard arrays in Excel or R

I'm trying to match the A column with any values in B but I can't figure out the correct formula to get the Match results. The values in A match if B has a wildcard value that corresponds.

A             B     Match
1             2     NO
2_1           3     YES
3             13    YES
3_1           14    YES
3_2                 YES
9                   NO
12                  NO
14                  YES
14_3                YES
16                  NO
19                  NO
20                  NO

I've tried to add a wildcard to the B array but this does not work as I intended 

C2=MATCH(A2,B2:B5&"*")=#VALUE!

Upvotes: 2

Views: 84

Answers (1)

Matthew Lundberg
Matthew Lundberg

Reputation: 42679

Using R, and including the Match column from your data.

You want rows where the value of A occurs anywhere in the column B?

> x[x$A %in% x$B,]
   A  B Match
3  3 13   YES
8 14 NA   YES

It looks like you might want to match everything before the _:

> with(x, x[sub('_.*', '', A) %in% B,])
     A  B Match
2  2_1  3   YES
3    3 13   YES
4  3_1 14   YES
5  3_2 NA   YES
8   14 NA   YES
9 14_3 NA   YES

Upvotes: 3

Related Questions