Searching a range for MATCHing fragments or partials

[Swordfishtrombone]

Hi,


I have a huge (100k+) list of serials numbers and I am trying to search it for fragments/partials and return the row number.


I can tell if there is a fragment match anywhere in the list
=COUNTIF(A1:A4,"*"&B1&"*")>0
but this only returns TRUE/FALSE


And I can get the MATCH if I were trying to do an exact match
=MATCH(B1,A1:A4,0)
This returns the row number.


But I don't know how to combine these to return the row number for a fragment.
I would like to see something like ColC below.


ColA___ColB___ColC (Row in ColA where the ColB number fragment appears)
1234___21____4
2345___22____0 (no match)
6789___34____1 (not sure how multiples would be handled)
4321___8_____3


Hoping it's something simple that I am missing.


Also not sure what would happen if there are multiples, as in the example above the fragment "34" appears in both the first and the second entries in ColA. I suppose an additional ColD that contained the COUNT would be possible, but knowing the MATCH for all occurences would be ideal.

PS, the actual numbers being searched contain letters, numbers as well as dashes, if that matters...


Thanks in advance.

Mike

 

[C with no eyes]

You're going to kick yourself...

In your countif you are searching for a 'fragment' within a cell by using "*"&B1&"*"

But in your match you are searching for the exact value!! Use =MATCH("*"&B1&"*",A1:A4,0)

 

[Aladin Akyurek]

Care to be a bit more specific? If 4 in column C is a count, what is counted exactly? Please try to answer in words, not in Excel formulas.

 

[C with no eyes]

Hmmm... sorry should've checked it first. That doesn't work. Give me a minute.

Watch this space!

 

[Swordfishtrombone]

Aladin, The 4 in ColC indicates that the "fragment" in ColB (21) can be found in row 4 of ColA ("21" is contained within "4321").
Sorry, "fragment" is probably not the best term...

 

[Swordfishtrombone]

C, the problem is that the COUNT is looking for a "partial" but syntax for MATCH() requires a type...

 

[C with no eyes]

Are you sure your countif is working? I copied yours and it doesn't seem to be working in my spreadsheet. If your COUNTIF works as "*"&B1&"*" try substituting it into MATCH as mentioned in my first post.

 

[Aladin Akyurek]

Aladin, The 4 in ColC indicates that the "fragment" in ColB (21) can be found in row 4 of ColA ("21" is contained within "4321").
Sorry, "fragment" is probably not the best term...

[TABLE="class: grid, width: 320"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1234[/TD]
[TD="bgcolor: transparent, align: right"]21[/TD]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2345[/TD]
[TD="bgcolor: transparent, align: right"]22[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6789[/TD]
[TD="bgcolor: transparent, align: right"]34[/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4321[/TD]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

In C2 control+shift+enter, not just enter, copy across, and down:

=IF(COLUMNS($C2:C2)>SUM(IF(ISNUMBER(SEARCH($B2,$A$2:$A$5)),1)),"",SMALL(IF(ISNUMBER(FIND($B2,$A$2:$A$5)),ROW($A$2:$A$5)-ROW($A$2)+1),COLUMNS($C2:C2)))

 

[Swordfishtrombone]

Aladin, Wow, it's going to take me a little time to unpack THAT formula, but it looks like it is working.
C, Yea, I copied it out and tried it and it didn't work, but the original place where I was testing it, it IS working, so it looks like there is something else to investigate here.
Thanks to you both.

 

[Aladin Akyurek]

You are welcome. Thanks for the update.