InStr search function for long string text in cells, how to differentiate nested text

[arg123]

Hello all thanks for taking a look.

I am running a masking macro that uses data on a sheet (Sheets1) where column A has the User ID, ex. A1: A) 123456789, and then an offset 2 columns with a sing cell with a long list of associated targets, ex. C1: ABC-1, ABC-2... ABC-10, ABC-11, etc...to about ABC-20.

The macro looks at a raw data sheet with a User ID in a column and all targets in an offset cell. User ID in A1:A20 "123456789" and in B1:B20 is the target ABC-1...ABC20.

The macro loops through the raw data User ID column. When Raw data User ID is found in the Sheets1 User ID column A, it looks secondarily at the offset in the raw data and deletes anything not present in the long list on on the Sheets1 offset target list.

The issue I'm having is that sometimes -- and only sometimes -- the macro will read ABC-1 and take it as ABC-1x where x is any number making that target 10, 11, ... 18, 19.

Is there a way to correct for this without changing the target ID to ABC-01 (which I know is the easiest way but I' handcuffed there.

Macro loops from the bottom.

Sub Masking()

On Error Resume Next

Dim sampname As Range
Set sampname = Worksheets("CopyRawDataHere").Range("D23:D3094")
Dim i As Integer

For i = 3071 To 1 Step -1
    
'For Each cell In sampname

sampid = sampname.Cells(i).Value
trgt = sampname.Cells(i).Offset(0, 1).Value

    If trgt = "B_atroph" Or trgt = "RNaseP" Or trgt = "16s" Then
    Else

        Dim ordid As Range
        Set ordid = Worksheets("Orders").Range("A1:A400")

        For Each cell In ordid
            If InStr(1, cell.Value, sampid) > 0 Then
                
            orders = cell.Offset(0, 2)

                If InStr(1, orders, trgt) > 0 Then
                Else: sampname.Cells(i).EntireRow.Delete
                End If
            End If
        Next
    End If
Next i

End Sub

 

[Anthony47]

I didn't understand your data structure, but to avoid that confusion I would search not "ABC-1" but "ABC-1,", that is the Id & ","
To make sure that you can identify up to the last Id in the row, you need to append a "comma" also to the content of the row.

If you don't succede in adapting this concept to your situation the please tell us wich is the line of the code that now create the problem

Bye

 

[arg123]

I didn't understand your data structure, but to avoid that confusion I would search not "ABC-1" but "ABC-1,", that is the Id & ","
To make sure that you can identify up to the last Id in the row, you need to append a "comma" also to the content of the row.

If you don't succede in adapting this concept to your situation the please tell us which is the line of the code that now create the problem

Bye

Thanks Anthony, that's a great idea. It would mean creating a concat to add the comma to the original reference cell "trgt" which is what is being looked for in the Sheets1 offset that has a long list (in a single cell) of the targets ordered. I'll update to include the two sheets that are being cross referenced. Perhaps that will help. Thanks again!

 

[arg123]

"Orders" is the Sheets1 tab I reference above.

CopyRawDataHere is the raw data.

The macro loops through the raw data. If the value in Worksheets("CopyRawDataHere").Range("D23:D3094") is found in Worksheets("Orders").Range("A1:A400"), then the macro looks at the target ID (UTI-xxx) in the raw data that is offset from the original loop cell and makes it a "trgt" value. That value is looked for in the offset of the original ID matching cell in the Orders sheet. It deletes the row in raw data if that trgt isn't in the list of trgts in column C of Orders for that sample ID. Yeah, a little confusing. Thanks for the patience.

Ex. UTI-NAx is not an ordered target for any sample, so it will always be deleted from the raw data. The only raw data that should remain are those lines that have the sample ID and the lines with corresponding ordered targets.

Sample 123456789 should only have lines remaining for targets ABR-1 through ABR-8, UTI-2, UTI-8, UTI-10, and UTI-14, UTI-15, UTI-17, and UTI-18.

The macro works nearly perfectly. The issue is that it left in UTI-1 and deleted UTI-10 because UTI-1 is seen within UTI-10. Re-run, it replaced UTI-17 with UTI-1. and sometimes it doesn't replace at all. Which is odd.

The commas idea is great. But it would mean I'd have to add the "," to the end of every target (in cell or in code). Which may work very well. I'm curious if there are any other ideas that may be more easily implemented.

Thanks again for stopping by and in advance for any assistance offered!

 

[Anthony47]

You have just to modify somewhere from InStr(1, CellContent, IDNum) to InStr(1, CellContent & ",", IDNum & ",")
That should be all
That way ID-1 will not be confused with ID-10

But for sure there are other ways...

 

[arg123]

You have just to modify somewhere from InStr(1, CellContent, IDNum) to InStr(1, CellContent & ",", IDNum & ",")
That should be all
That way ID-1 will not be confused with ID-10

But for sure there are other ways...

That is very simple. Adding the "," with the code. Thank you so much! As usual, its the simple things that just pass you by when you've been looking at it too long. Cheers!

 

[Anthony47]

That is very simple. Adding the "," with the code. Thank you so much! As usual, its the simple things that just pass you by when you've been looking at it too long. Cheers!

Next time I shat try something much more difficult

(thank you for the feedback)

 

[arg123]

Next time I shat try something much more difficult

(thank you for the feedback)

Conceptually, I mean. Of course, as soon as it’s explained, the logic always makes sense and seems easy. Getting there though….couldn’t have done it without you. ???