Posts by Desauv

    Hi All


    I have below formulas, and both return #N/A


    Formulas
    =SUMPRODUCT(ISNUMBER(SEARCH(RIGHT(B417;5);skema!$B$2:$B$4000;1))*skema!$T2:$T4000)



    =SUMPRODUCT(--(LEFT(skema!$B$2:$B$4000;5)=RIGHT($B417;5))*(skema!$U$2:$U$4000))


    Example of Data
    B417 = A68247


    e.g $B$2:$B$4000 = 68247-01


    e.g skema!$T2:$T4000 = $500,21



    Hope someone can help :)


    Regards
    Stefan

    Hi All


    I have below text string where the number in the end always is 12 characters long. But the rest of the string can vary in length.
    However there will also always be three "-".


    40647-Access-Kim the kid-060519210519


    I need a formula that extract the first 6 characters of the number in the end, so the result should be 060519.


    Can anyone help here? :)


    All help appreciated.


    Regards
    Stefan

    Hi


    Let me see if I can explain what I'm trying to achieve here.


    I'm trying to find the max value in column C based on several criteria. But the max value should repeated in those rows, where the criteria are met.
    Column A, B, C is how the data is shown in my file. The other column is only for explanatory purpose.


    In range A2:A5 the criteria is the same, however the values are different in column C. The max value in range C2:C5 is 100519, and should therefore be repeated in A2:A5.


    Column F is the output/result.


    [ATTACH=JSON]{"data-align":"none","data-size":"full","title":"excel max example.png","data-attachmentid":1219304}[/ATTACH]

    Hi


    So I'm trying to make a lookup formula which have multiple criteria and also partial match.


    This is the formula that I've been trying so far, to find the value 50. However it doesn't work.


    =INDEX(I10:I13;MATCH(1;("*"&I4&"*"=H10:H13)*("*"&I5&"*"=H10:H13);0))


    [ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Excel Example.png","data-attachmentid":1219158}[/ATTACH]


    I've tried this formula also, but here I don't know how to insert multiple criteria


    =INDEX(I10:I13;MATCH("*"&I5&"*";H10:H13;0))


    I hope someone can help :)




    Stefan

    Thanks graha_karya that fixed the problem. However, I can't figure out why the changes you made does a difference?!?


    If t not numeric will be mismatch
    try change this line become

    Code
    1. x = Filter(.Parent.Evaluate("transpose(if(" & .Columns(1).Address & "=" & t & ",row(1:" & .Rows.Count & "),char(2)))"), Chr(2), 0) x = Application.Index(.Value, Application.Transpose(x), [{2,3}]) End With

    to

    Code
    1. x = Filter(.Parent.Evaluate("transpose(if(" & .Columns(1).Address & "=" & chr(34) & t & chr(34) & ",row(1:" & .Rows.Count & "),char(2)))"), Chr(2), 0)
    2. x = Application.Index(.Value, Application.Transpose(x), [{2,3}])
    3. End With

    Below code is working as intended. However, I want to substitute the criteria "T10598" with a cell. I tried doing that with code no. 2. But I'm getting the "Type Mismatch" error. Can one of you point out what I'm doing wrong?


    I'm quite new to VBA, so sorry for the stupid questions :)


    [VBA]Option Explicit
    Sub test()
    Dim x
    Dim cell As Range
    If Application.CountIf(Sheets("Personaledata").Columns(1), "T10598") = 0 Then Exit Sub
    With Sheets("Personaledata")
    With .Range("a1", .Cells.SpecialCells(11)).Resize(, 3)
    x = Filter(.Parent.Evaluate("transpose(if(" & .Columns(1).Address & _
    "=""T10598"",row(1:" & .Rows.Count & "),char(2)))"), Chr(2), 0)
    x = Application.Index(.Value, Application.Transpose(x), [{2,3}])
    End With
    End With

    Sheets("Personale").Range("A3:B22,A27:A46,A52:A71,A77:a96,A102:a121,A127:a146").ClearContents

    Sheets("Personale").Range("a22").End(xlUp)(2).Resize(UBound(x, 1), 1).Value = x
    Sheets("Personale").Range("a46").End(xlUp)(2).Resize(UBound(x, 1), 1).Value = x
    Sheets("Personale").Range("a71").End(xlUp)(2).Resize(UBound(x, 1), 1).Value = x
    Sheets("Personale").Range("a96").End(xlUp)(2).Resize(UBound(x, 1), 1).Value = x
    Sheets("Personale").Range("a121").End(xlUp)(2).Resize(UBound(x, 1), 1).Value = x
    Sheets("Personale").Range("a146").End(xlUp)(2).Resize(UBound(x, 1), 1).Value = x


    For Each cell In Sheets("Personale").Range("A3:A22,A27:A46,A52:A71,A77:a96,a102:a121,A127:a146")

    If IsEmpty(cell) Then
    cell.EntireRow.Hidden = True
    End If
    If Not IsEmpty(cell) Then
    cell.EntireRow.Hidden = False
    End If
    Next
    End Sub
    [/VBA]



    CODE NO 2


    [VBA]Option Explicit
    Sub test()
    Dim x
    Dim cell As Range, t

    t = Sheets("Baggrundsdata").Range("B3")

    If Application.CountIf(Sheets("Personaledata").Columns(1), t) = 0 Then Exit Sub
    With Sheets("Personaledata")
    With .Range("a1", .Cells.SpecialCells(11)).Resize(, 3)
    x = Filter(.Parent.Evaluate("transpose(if(" & .Columns(1).Address & "=" & t & ",row(1:" & .Rows.Count & "),char(2)))"), Chr(2), 0)
    x = Application.Index(.Value, Application.Transpose(x), [{2,3}])
    End With
    End With

    Sheets("Personale").Range("A3:B22,A27:A46,A52:A71,A77:a96,A102:a121,A127:a146").ClearContents

    Sheets("Personale").Range("a22").End(xlUp)(2).Resize(UBound(x, 1), 1).Value = x
    Sheets("Personale").Range("a46").End(xlUp)(2).Resize(UBound(x, 1), 1).Value = x
    Sheets("Personale").Range("a71").End(xlUp)(2).Resize(UBound(x, 1), 1).Value = x
    Sheets("Personale").Range("a96").End(xlUp)(2).Resize(UBound(x, 1), 1).Value = x
    Sheets("Personale").Range("a121").End(xlUp)(2).Resize(UBound(x, 1), 1).Value = x
    Sheets("Personale").Range("a146").End(xlUp)(2).Resize(UBound(x, 1), 1).Value = x


    For Each cell In Sheets("Personale").Range("A3:A22,A27:A46,A52:A71,A77:a96,a102:a121,A127:a146")

    If IsEmpty(cell) Then
    cell.EntireRow.Hidden = True
    End If
    If Not IsEmpty(cell) Then
    cell.EntireRow.Hidden = False
    End If
    Next
    End Sub[/VBA]

    Hi Rory


    It sure doesn't. Please see updated code.


    The reason why I use transpose is because, if I don't I get the result showed in below picture.


    However, now I get the the "Type Mismatch".


    [VBA]Sub test()
    Dim x
    If Application.CountIf(Sheets("Sheet2").Columns(1), "2") = 0 Then Exit Sub
    With Sheets("Sheet2")
    With .Range("a1", .Cells.SpecialCells(11)).Resize(, 2)
    x = Filter(.Parent.Evaluate("transpose(if(" & .Columns(1).Address & _
    "=""2"",row(1:" & .Rows.Count & "),char(2)))"), Chr(2), 0)
    x = Application.Index(.Value, Application.Transpose(x), [{1,2}]) '
    End With
    End With

    Sheets("Sheet2").Range("d2:e30").ClearContents

    Sheets("Sheet2").Range("d30").End(xlUp)(2).Resize(UBound(x, 1), 2).Value = x
    End Sub[/VBA]


    [ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Capture.PNG","data-attachmentid":1211403}[/ATTACH]

    Hi All


    Can't seem to figure out why I get the "Expected Array" error in below code. So I'm hoping you can help me? :)


    The error occurs in the last line



    [VBA]
    Sub test()


    Dim x As Integer
    If Application.CountIf(Sheets("Sheet2").Columns(1), "2") = 0 Then Exit Sub
    With Sheets("Sheet2")
    With .Range("a1", .Cells.SpecialCells(11)).Resize(, 16)
    x = Filter(.Parent.Evaluate("transpose(if(" & .Columns(1).Address & _
    "=""2"",row(1:" & .Rows.Count & "),char(2)))"), Chr(2), 0)
    x = Application.Index(.Value, Application.Transpose(x) _
    , [{1,2}])
    End With
    End With


    Sheets("Sheet2").Range("d2:e30").ClearContents


    Sheets("Sheet2").Range("d30").End(xlUp)(2) _
    .Resize(UBound(x, 1), 2).Value = x
    End Sub
    [/VBA]






    [ATTACH=JSON]{"alt":"Click image for larger version Name:\tCapture.PNG Views:\t1 Size:\t58.7 KB ID:\t1211400","data-align":"none","data-attachmentid":"1211400","data-size":"full","title":"Capture.PNG"}[/ATTACH]


    Regards
    Stefan