Hello,
I would like to look for a value in each column of data and count the match with another corresonding column. Then the total of the values that have matched (are the same), is displayed in the result. I am attaching a sheet example as its a little difficult to explain. If anyone could help it would be amazing.
Thank you,
Sans
The easy would be with formula:
Please Login or Register to view this content.
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Would the range to check always be 1 row? Also, would there always be one row in J to P?
Edit: I posted my question at the same time as NBVC. His solution works amazingly.
Last edited by arlu1201; 05-07-2012 at 10:08 AM.
If I have helped, Don't forget to add to my reputation (click on the star below the post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
Use code tags when posting your VBA code: [code] Your code here [/code]
The VBA approach. Put this function into a regular code mode (insert > module):
Please Login or Register to view this content.
Now save. This gives you a new function you can enter into AE42... a facny COUNTIF.
=RangeCheck(B23:H42, J42:P42, W42:AC42)
=RangeCheck(DataTable, OffsetValues, CompareValues)
The main requirement is all three references have to have the same number of COLUMNS, if they do not you'll get the error answer of -99999.
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Excellent guys, thank you very much for your replies.
NBVC's formulas are always amazing. I cannot count how many times they have saved me!His solution works amazingly.
JBeaucaire, thank you, the macro works great!
Would it be possible to also calculate the latest 10 columns for every range that is being checked?
Thank you all,
Sans
Jerry, if I extend the middle range in your macro, will it function as in my attachment above?
I think it does, apart from the counting part.
Last edited by sans; 05-07-2012 at 10:56 AM.
Not sure if you necessarily desire a macro... but if I understand correctly, then this formula might work:
confirmed with CTRL+SHIFT+ENTER not just ENTER.Please Login or Register to view this content.
This returns a result of 7 for the current setup.
No, I just thought that the above is not possible to achieve with a formula.
The formula I think works correct apart from one part. Even if more than one match is found between any of the values i.e. in the 1st column and 1st column in RANGE TO CHECK, the count is 1. So the max count that can be found for each column is 1.
So essentially, the check for a match for a column happens 10 times, and if 1 or more matches occur, the count is 1 for that column.
If you like I can attach another workbook example to make it more clear
Thank you,
Sans
Perhaps?
confirmed with CTRL+SHIFT+ENTERPlease Login or Register to view this content.
Yes, that's exactly what I meant NBVC! Thank you very much for your help. Did you actually understand what I was trying to convey or was it a guess?
Last edited by sans; 05-07-2012 at 01:55 PM.
I don't get the added stuff, but your original question to me... you can expand the function I gave you to as many columns of data as you wish. Just make sure all three parameters you feed into the function I provided have the same number of columns, in which case it will work the same for a 7 column table as 100 column table.
Excellent, thanks Jerry!
Last edited by sans; 05-08-2012 at 01:57 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks