• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need formula or VBA to weed out unwanted numbers

dwrowe001

Member
Hello Excel’ers,
I come to you with another problem.. I am posting in VBA board because I have tried to do this with regular formulas and have failed miserably..
I have attached a workbook with an example table.. The table consists of numbers 1 to 60 across the top in row 3, B3 to BI3. Then from B4 to BI22 is a bunch of numbers of various values. These numbers are results of other factors and formulas and are constantly changing.

In column BJ, BJ5 to BJ22 I have matching criteria for each row. Each of the numbers in each row must either match or not the criteria I have set. So lets take the number 59 for example. Starting in row 5, the criteria in BJ5, =>0 < .3, this is the criteria each number in Row 5 must meet, I guess either making it True if it matches or False if it doesn’t. In the attached example table, the number in BH5 is .28 and falls within the set criteria, so it is true. This is the same for each row..

I have highlighted in yellow the matches for numbers 57, 58, 59 and 60 as an example. 57 has 4 matches, 58 has 14 matches, 59 has 5 and 60 has 3. But this is how all the numbers 1 thru 60 should work. As an example, I have completed row 5 also. I don't need the numbers highlighted in the final version.. but hey, if you can do it easily then why not....

Then based on the number I have set in BK3, the matching number, and using the attached table, if a number has 4 or more matches, then it will be returned in a list in column BL, starting in BL4 down, depending on how many matches of 4 or greater there are. If a number has less then 4 then it won’t be returned, 60 has 3 so it won’t get returned in the list. If I change the matching number to 3, then 57, 58 59 and 60 would be returned. If I changed the matching number to 13, then only 58 would be returned since it is the only number (in the example) with 13 or more matching numbers. Keep in mind, in a completed version, all 60 numbers would be in play as they all would have matches of different quantities.
I hope this makes sense.. please let me know if you have questions.
Thank you for your time and help.
Dave.
 

Attachments

  • Range Example.xlsx
    25.8 KB · Views: 4
If you separate the criteria into 2 cells like
62818

Then in BL5 you can use something like: =COUNTIFS(B5:BI5,BJ5,B5:BI5,BK5)
That returns 10

So I am not sure how the other checks work with 57 and 4 ?
 
Hui,
Thank you for your reply. I'm not sure if I expressed in detail enough what I need. When I set the Match number in BK3, what this means is, the numbers in rows B4 to BI22 must match or fall within the criteria parameters I have set in BJ5 to BJ22 for each row. Now we take into account how many matches there are in each column and this is were the matching number comes into play, lets assume that the number I choose for the matching number in BK3 is 4. So, taking the number 59 in BH3 for example. If there are 4 or more matches in the list of numbers in the column BH4 to BH22, then the number in BH3, which is 59, will be returned in the list of returned numbers in column BL which also have 4 or more matches in their espective columns. This scenario will be the same for all other numbers in Row 3. I hope I haven't totally confused you.
 
Hello everyone,
I am cross posting to another excel board in hopes of getting more help. Thank you all for looking at least at my original post,
If I get a solution to my quandary I will post here.
Dave
 
Hi !​
According to your attachment it's difficult to not harcode all the conditions​
so in case of any condition change you will have to amend yourself the code, the way I won't go …​
As Hui yet explained, the better is to split both conditions in two columns,​
the first one for >= and the second for < both as headers, the data conditions just need to enter numeric values​
then the code will still work in case of any condition change …​
 
Hi Marc L, I appreciate your input.. I didn't fully understand what Hui was doing with his example.. hence my reply to him. I do appreciate his efforts, I just didn't totally follow. Basically each number in the table or columns under each main numbers in Row 3 must meet the criteria in column BJ.. if a number meets the criteria, or falls within the parameters then it will be set to yes, or true. then, based on the number I set in BK (matching number) if there are that many matches or more in a column then the main number at the top of that column (numbers 1 to 60) will be returned and included in a list of other main numbers who's number in the table have met the criteria. in Column BL. Does this make sense? I seem to be having a really hard time trying to explain this for some reason.
Dave
 
The issue is your both conditions within a single column !​
So as it is I would just hardcode each condition and in case of any condition change,​
you will have to amend the code yourself following your VBA own skills : is it what you want ?!​
If not, so logically as yet explained twice, the conditions must be splitted within two columns …​
 
With both conditions in a single column, it's not an issue only if the Excel syntax is respected which is not the case here !​
As '=>0 means nothing, the correct syntax is >=0 …​
And with two conditions the separator must be a single space like >=0 <0.3 …​
 
Ok I think I gots it.... I'm a little slow, but I usually catch on... I have attached an upated and partially working example.. at the bottom of each column is a total of matches.. In Cell DV4 I have the numbers I want returned.. so if a column as 14 or more matches, then the main number at the top of that column in Row 3 will be returned in Column DU. I don't know how to do that.. could someone help me with is part?

Hui and Marc L, Thank you!

Dave
 

Attachments

  • Range Example.xlsx
    46.3 KB · Views: 1
could someone help me with is part?
According to your last attachment :​
Code:
Sub Demo0()
    Dim V
    [DU5].CurrentRegion.ClearContents
    V = Filter(Evaluate("IF(C23:DQ23>=" & [DV3].Text & ",B3:DP3)"), False, False)
    If UBound(V) > -1 Then [DU5].Resize(UBound(V) + 1).Value2 = Application.Transpose(V)
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Marc L, yes, I like it. Can you explain this code to me to help me understand how it works?
Thank you for your help.
Dave
 
Last edited:
Thanks Dave ! :cool:

  • V = Filter(Evaluate("IF(C23:DQ23>=" & [DV3].Text & ",B3:DP3)"), False, False)
It applies an Excel basic array formula returning the header when matching with DV3 cell or False when not​
and removes any False value from the array.​

  • If UBound(V) > -1 Then [DU5].Resize(UBound(V) + 1).Value2 = Application.Transpose(V)
If some values match it allocates the matching headers array to the columns …​
Do not forget to warn the other forum your need is achieved.​
 
Back
Top