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

Never mind.

Found this formula, and it did the trick!

=MID(A1;FIND(CHAR(1);SUBSTITUTE(A1;"-";CHAR(1);3))+1;6)

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

I have attached my example

[ATTACH]n1219361[/ATTACH]

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?!?

Quote from graha_karya

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

Code

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

x = Filter(.Parent.Evaluate("transpose(if(" & .Columns(1).Address & "=" & chr(34) & t & chr(34) & ",row(1:" & .Rows.Count & "),char(2)))"), Chr(2), 0)
x = Application.Index(.Value, Application.Transpose(x), [{2,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