logo
down
shadow

Pulling cell text based on multiple criteria


Pulling cell text based on multiple criteria

By : Logger
Date : November 22 2020, 10:48 AM
wish help you to fix your issue Spreadsheet , Can you check if this (rather convoluted) formula works ?
code :
=iferror(query(ArrayFormula({filter(Data!A2:A, Data!C2:C="Need It!"), isna(match(filter(Data!B2:B, Data!C2:C="Need It!")&"Got It!", query(Data!B2:B&Data!C2:C, "select* where Col1 contains 'Got It!' "), 0))}), "select Col1 where Col2 =TRUE "))


Share : facebook icon twitter icon
Excel IF formula pulling text cell from multiple blank cells

Excel IF formula pulling text cell from multiple blank cells


By : Ramiro Lynch
Date : March 29 2020, 07:55 AM
wish helps you I'm trying to use a formula that will take the cell in a row that contains text from multiple other blank cells. , If your range is A1:C1, try this array formula:
code :
=INDEX(A1:C1,MATCH(FALSE,ISBLANK(A1:C1),0))
=IFERROR(INDEX(A1:C1,MATCH(FALSE,ISBLANK(A1:C1),0)),"")
Selecting records based on multiple criteria in a single cell

Selecting records based on multiple criteria in a single cell


By : user1836864
Date : March 29 2020, 07:55 AM
will be helpful for those in need You should not be storing lists of values in a comma-delimited string. That is not the SQL way of doing things. The resulting queries cannot be optimized, and string functions are not SQL's forte.
If you are stuck with this format, you can add up the number of matches:
code :
where ( (case when ',' + txtSiblingsYearList + ',' like '%,R,%' then 1 else 0 end) +
        (case when ',' + txtSiblingsYearList + ',' like '%,N,%' then 1 else 0 end) +
        (case when ',' + txtSiblingsYearList + ',' like '%,1,%' then 1 else 0 end) +
        . . .
      ) >= 2
Pulling multiple criteria from another cell

Pulling multiple criteria from another cell


By : user2697584
Date : March 29 2020, 07:55 AM
it helps some times A basic table: , You can have all the conditions in a single cell (in this case C6):
code :
{=SUM(SUMIFS(B2:B5,A2:A5,TRIM(MID(SUBSTITUTE(C6,",",REPT(" ",255)),255*(ROW(INDIRECT("1:"&1+LEN(C6)-LEN(SUBSTITUTE(C6,",",""))))-1)+1,255))))}
Copying Multiple Cells based on a criteria and printing all of them onto one cell

Copying Multiple Cells based on a criteria and printing all of them onto one cell


By : user2998127
Date : March 29 2020, 07:55 AM
wish of those help Here's a solution based on what I understood. Hopefully it provides extra strategies for you that you can use in other VBA going forward.
code :
Sub DoStuff()

    '' Set Source and Target Sheets
    Dim srcSheet As Worksheet
    Set srcSheet = ThisWorkbook.Worksheets("Source") '' Source Data store on sheet called Source

    Dim targetSheet As Worksheet
    Set targetSheet = ThisWorkbook.Worksheets("Result") '' Sheet where I want to store results

    Dim SingleCell As Range
    Dim ListOfCells As Range

    Set ListOfCells = srcSheet.Range("D2", srcSheet.Range("D2").End(xlDown))

    Dim foundColor As Range
    Dim nextAvailableCell As Range

    For Each SingleCell In ListOfCells

        'If SingleCell.Value = "red" Then
        '    SingleCell.Offset(0, 2).Copy
        'End If

        'Worksheet.Add
        'Range("A1").PasteSpecial

        '' Find where the current color is on targetSheet in column A
        '' Find result must be a whole cell match ie red doesn't match red-orange
        Set foundColor = targetSheet.Range("A:A").Find(what:=SingleCell.Value, lookat:=xlWhole)

        '' If it found a cell with that color append it to the existing text
        If Not foundColor Is Nothing Then
            '' the new value is the current value PLUS a new line PLUS the appending text
            '' using .Value method skips the clipboard and is much faster than copy/paste
            foundColor.Offset(0, 1).Value = foundColor.Offset(0, 1).Value & _
                                            vbCrLf & _
                                            SingleCell.Offset(0, -3).Value & " " & _
                                            SingleCell.Offset(0, -2).Value

        '' Otherwise create a new "Entry"
        Else
            '' Get the first available (blank) row
            '' ASSUMING NO HEADERS NEEDED ON RESULT SHEET
            '========================
            If targetSheet.Cells(1, 1).Value = "" Then
                Set nextAvailableCell = targetSheet.Cells(1, 1)
            Else
                Set nextAvailableCell = targetSheet.Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0)
            End If
            '========================

            '' Copy the Info
            nextAvailableCell.Value = SingleCell.Value
            nextAvailableCell.Offset(0, 1).Value = SingleCell.Offset(0, -3).Value & " " & _
                                                   SingleCell.Offset(0, -2).Value

        End If

    Next SingleCell


End Sub
How to extract a line from a text in a cell based on certain criteria

How to extract a line from a text in a cell based on certain criteria


By : user3056888
Date : March 29 2020, 07:55 AM
I hope this helps . I would suggest writing a User Defined Function (UDF) in VBA and utilizing Regular Expressions.
To enter this User Defined Function (UDF), opens the Visual Basic Editor. Ensure your project is highlighted in the Project Explorer window. Then, from the top menu, select Insert/Module and paste the code below into the window that opens.
code :
Option Explicit
Function triageComplete(S As String) As Date
    Dim RE As Object, MC As Object

Set RE = CreateObject("vbscript.regexp")
With RE
    .Global = True
    .ignorecase = True
    .Pattern = "\b(\d{4}-\d{2}-\d{2}\s+\d{2}:\d{2}:\d{2})\b.*\n.*Triage Completed"
    .MultiLine = True
    If .Test(S) = True Then
        Set MC = .Execute(S)
        triageComplete = MC(0).submatches(0)
    End If
End With
End Function
shadow
Privacy Policy - Terms - Contact Us © ourworld-yourmove.org