Filter based on dictionary

[jaryszek]

Hi Guys,

i have array of part of strings like here:

_Minor_Version (the full string will be : 10_Minor_Version)
Build_Version_ (the full string will be: Build_Version_Master)

ans so on

So those strings are the same as:
*_Minor_Version
Build_Version_*


and now i have list of full strings in another worksheet (TargetRange):

10_Minor_Version_Minor_Version
String1
String2
Build_Version_Master

now i am goind and creating dictionary from parts and want to check if specific part exists in another worksheet full strings column.

How can i achevie that?
I was thinking to filter all keys from TargetRange and compare somehow with parts of string but i have no idea for this.

In other words - i want to take all parts of strings from Source worksheet and check if they are exists in TargetRange in second worksheet.
Please find also attachment.

can anyone help?

Best,
Jacek

[Marc L]

Hi,

without a Dictionary it needs only two codelines just using Excel basics, interested enough ?

[jaryszek]

Hi,

please help,

Jacek

[Marc L]

First : it can be done without any VBA code just with a formula in column B for example …

According to your attachment a VBA beginner starter
where V array variable contains the source elements found in the other worksheet
as you can easily check in the VBE Locals window :

Sub Demo1() 
    F = "TRANSPOSE(IF(ISNUMBER(MATCH(""*""&#&""*""," & Sheet2.UsedRange.Address(, , , True) & ",0)),#))" 
    V = Filter(Evaluate(Replace(F, "#", Sheet1.UsedRange.Address(, , , True))), False, False) 
    Stop 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[jaryszek]

Interesting approach.

Thanks!

Everyone also thinks that this is the best efficient approach?
I have to be sure.

I would use something like use Match worksheet function and loop....

Best,
Jacek

[Marc L]

As you do not understand the purpose of a Dictionary (exact match only ‼), as avoiding to loop may be better, …

Another way may be the Find method but as the best approach depends on what your forgot again to explain in the initial post
as without any global sight it falls to a guessing challenge …

[jaryszek]

Ok thanks.

Avoiding loop i think can be better.

What you want to know?

The macro is going through used ranges like in example and based on condition checking if variable exists.
If exisits i have to skip this somehow.
The issue is that i have to loop through each cell in sheet2.range.
Why? Because i have different conditions which i have to apply.

So your code can be very useful because i will get the subset of variables which i have to exlude from loop.
The issue will be that still i have to use loop on your array, add this to dictionary and use in condition. But this is fine i think, small loop only.

Couple question:
What does it mean? "#" in your formula?

Jacek

[Marc L]

# is where the range address stands, see second codeline …

You may use another formula to loop only the necessary.

[jaryszek]

Hi,

the code is not working for me.

Code:

Please Login or Register  to view this content.

Result:

Attachment 678919

As you can see i got results from Sheet1 instead of array :

10_Minor_Version_Minor_Version
Build_Version_Master

Please help,
Jacek

[jaryszek]

and the second question can anybody write a function which will work in Excel formula based on below string:

Please Login or Register  to view this content.

i tiried with:

Please Login or Register  to view this content.

but i am getting:

Screenshot_38.png

thank you,
Jacek

[Marc L]

As your post #9 attachment is invalid - like you can easily check - so just follow the top page yellow banner …

[jaryszek]

I did changes in the attachment from 1st file.

Your formula is not working.
It is outputting instead od filtered sheet2 variables sheet1 variables...

Please help,
Jacek

[Marc L]

'Cause it's what you asked for in your initial post but in fact you need exactly the opposite
the reason why the initial post requires a smart enough explanation & attachment with an explicit sample result
so if you still needs an array as result - if not just clearly explain what ! - then what should contains exactly this array
just to be sure to not waste time anymore (as you already could find yourself a solution with an easy formula in column B) …

And attach your new workbook with changes you have made …

[jaryszek]

MArc L,

example is in attachment in first post.
Tell me what you not understand.

You are taking Sheet1 variables.

And i want to use them and get array filtered based on them from Sheet2 variables, based on partial string.
I think that in your method is not possible.

You can use advaned filter only.

Jacek

[Marc L]

(removed, see next post)

[Marc L]

My post #13 question still expects an answer …

[jaryszek]

Especially for you

And now watch out:

I do not want the result as range like in attachment. I want to have array in memory (it is clear for you?)

Jacek

[AliGW]

Are you saying that the attachment is showing us what you DON'T want?

[jaryszek]

No Ali,

Marc wants to have description with result because he doesnt understand. Please read whole topic.
So i made for him especially attachment with result (which i also described in topic) but i am highliting that i do not want range as output, only result array in memory.

Jacek

[AliGW]

I have read the whole topic. Please don't make assumptions.

[jaryszek]

Returning to the topic,

anyone can help?

Jacek

[Marc L]

Especially for you

Thanks to answer to my question : now it's clear,
I will give it a try despite some moderator didn't understand - or badly read - why it's normal & necessary !

[AliGW]

Marc - please stop making critical and unnecessarily personal jibes at me and others. It will NOT be tolerated, so stop doing it.

[jaryszek]

Thank you Marc for giving this try, maybe with your formula it is possible?

Best wishes,
Jacek

[jindon]

Dictionary?

Please Login or Register  to view this content.

[jaryszek]

Wow!!!! Thank you so much awesome code!!!!!
Brilliant and genious!

jindon, i can not add you rep i do not know why , so sorry for that

One question:
why column number in index function is 1, not 0 ?

Jacek

[AliGW]

Jack - there is only so much rep you can give someone - the message you will havee seen tells you that you must award other people rep before you can reward Jindon again. Hope this explains it.

[jindon]

0 or 1 both should work, but not other.
when 2nd argument is an array(2D) and need to return all the related columns, the 3rd argument must be an array(1D), otherwise you can place 0 or column index.

[Marc L]

So Ali do the same for me, thanks …
If a helper can't ask any question, this forum may die !


jariszek,

I can't find a direct formula evaluation like in my first demonstration (Edit : Jindon did it, well done !)
but as I yet wrote it can be done just using a formula in column B for example
- as a helper column so temporary, can be cleared at the end of the VBA procedure -
so I though you at least give it a try as the formula seems not difficult
- formula returns column A value for data needed to be in the result array and FALSE for data to exclude -
and at moderator level so Ali may find a better worsheet formula than the formula array I'm thinking of …

So before any formula way, according to your attachment with so few data
the classic VBA looping arrays way well does the job :

Sub Demo2() 
    Dim V, W, R&, S& 
        V = Application.Transpose(Sheet2.UsedRange.Value2) 
        W = Sheet1.UsedRange.Value2 
    For R = 1 To UBound(V) 
        For S = 1 To UBound(W) 
            If InStr(V(R), W(S, 1)) Then Exit For 
        Next 
            If S > UBound(W) Then V(R) = False 
    Next 
        V = Filter(V, False, False) 
    Stop 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[jaryszek]

Thank you Marc for your solution.
It is nice!

thanks, i gave jindon rep

Jacek

[AliGW]

If a helper can't ask any question, this forum may die !

You can ask questions, nobody said that you can't, but you need to do it in a way that does not imply criticism of the member for their lack of VBA skills. It is not just me: your posts have, over the years, been reported many times for their negative and critical tone, by members you've made them to AND by members who have simply seen them when reading threads that you participate in. You know this because you have been asked by me and other moderators in the past not to do it, yet you persist. It needs to stop.

There need be no further discussion of this here: take it up with me privately if you still don't get it.

[Marc L]

jaryszek,

thanks for the rep' !
Try both ways with much data with some matching the same source criteria …

According to the personal message we both received and according to previous post, any comment ? Thanks.

[AliGW]

According to the personal message we both received and according to previous post, any comment ? Thanks.

NOT here. If you wish to discuss it further, go to private message.

I am closing this thread now.