Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-17-2019, 02:46 PM
durwood durwood is offline VBA or Formulas to parse out numbers based on criteria Windows 10 VBA or Formulas to parse out numbers based on criteria Office 2016
Novice
VBA or Formulas to parse out numbers based on criteria
 
Join Date: Sep 2019
Posts: 2
durwood is on a distinguished road
Default VBA or Formulas to parse out numbers based on criteria

Hi everyone,
I need help with parsing out numbers which don't meet a specific set criteria..


I have attached a workbook with an example table .. the explanation below is keyed to that table. The table consists of numbers 1 to 60 across the top in row 3, B3 to BI3, these are the main numbers. 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 meet or not the criteria I have set. So lets take the number 59 for example. The column of numbers below it, in BH5 to BH22 all must meet the criteria in the corresponding cell in column BJ. So, the first set of criteria is in BJ5, which is “ =>0 < .3”. So the number in BH5 must meet the criteria in BJ5, which it does. .28 falls between the criteria =>0 and <.3. the same goes for all the rest of the numbers in that column BH6 down to BH22, and also for each of the Main numbers in Row 3 and the numbers in each of those columns.



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.... in the example I only show a couple numbers which have matches. Once completed, all of the main numbers, in row 3, 1 to 60 should have matches in their respective columns.

Then based on the number I have set in BK3, which is set to 4 in the example table, the matching number, and using the attached table, if a main number in row 3 has 4 or more matches, then it will be returned in a list in column BL, starting in BL4 down. 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.


I originally posted this problem on Chandoo. But only got 1 reply, no resolution. I informed in my post that I was also posting here.

Thank you for your time and help.
Attached Files
File Type: xlsx Range Example.xlsx (25.8 KB, 6 views)

Last edited by durwood; 09-17-2019 at 02:48 PM. Reason: edit
Reply With Quote
  #2  
Old 09-17-2019, 06:58 PM
durwood durwood is offline VBA or Formulas to parse out numbers based on criteria Windows 10 VBA or Formulas to parse out numbers based on criteria Office 2016
Novice
VBA or Formulas to parse out numbers based on criteria
 
Join Date: Sep 2019
Posts: 2
durwood is on a distinguished road
Default

Problem solved. thank you
Reply With Quote
  #3  
Old 09-17-2019, 06:59 PM
NoSparks NoSparks is offline VBA or Formulas to parse out numbers based on criteria Windows 7 64bit VBA or Formulas to parse out numbers based on criteria Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

This appears to be the link durwood didn't include

Need formula or VBA to weed out unwanted numbers | Chandoo.org Excel Forums - Become Awesome in Excel
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Least of Criteria based on Tenure & Age hiwire03 Excel 1 06-02-2019 12:03 AM
Rounding numbers in formulas Lamont Excel 3 01-09-2019 08:19 AM
VBA - Deleting Rows (criteria based) BobbyG Project 1 07-31-2017 08:55 AM
VBA or Formulas to parse out numbers based on criteria Sum based on multiple criteria (one of the criteria is not unique) until a threshold is reached. phillipsdp Excel 1 11-09-2016 12:53 AM
VBA or Formulas to parse out numbers based on criteria Need code to Parse and arrange data based on heading winmaxservices2 Excel Programming 1 01-01-2015 01:04 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:45 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft