Hello,
This post relates somewhat to a post I have created a little while ago here:
http://www.excelforum.com/excel-prog...er-column.html
which has been solved.
I would like to search for values in each column, and count the matching results with another value. The end result is the total match found between all columns.
I am attaching an example workbook. If anyone could help it would be amazing. If I am not being clear in any way please let me know.
Thank you
On row 42, this formula will return the value as indicated:
=INDEX(B$21:B$42,COUNTA(B$21:B$42)-J42+1)
Not really sure where you want to go from there.
Regards, TMS
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I did modify NBVC's amazing formula in the above thread to achieve the correct result
The formula checks only the 2 values from each column. As I would like to check 10 or more values in each column, using a formula is a little difficult. Thank you
=SUM(SUM(--(N((OFFSET(C5013,-J5013+1,0))=R5013))+SUM(--(N(OFFSET(C5012,-J5012+1,0))=R5013)))>0,SUM(--(N((OFFSET(D5013,-K5013+1,0))=S5013))+SUM(--(N(OFFSET(D5012,-K5012+1,0))=S5013)))>0,SUM(--(N((OFFSET(E5013,-L5013+1,0))=T5013))+SUM(--(N(OFFSET(E5012,-L5012+1,0))=T5013)))>0,SUM(--(N((OFFSET(F5013,-M5013+1,0))=U5013))+SUM(--(N(OFFSET(F5012,-M5012+1,0))=U5013)))>0,SUM(--(N((OFFSET(G5013,-N5013+1,0))=V5013))+SUM(--(N(OFFSET(G5012,-N5012+1,0))=V5013)))>0,SUM(--(N((OFFSET(H5013,-O5013+1,0))=W5013))+SUM(--(N(OFFSET(H5012,-O5012+1,0))=W5013)))>0,SUM(--(N((OFFSET(I5013,-P5013+1,0))=X5013))+SUM(--(N(OFFSET(I5012,-P5012+1,0))=X5013)))>0)
If there is anyone who knows how to solve this with vba it would be great. If I am not being clear at any point in the attachment, please do let me know. Thank you.
Last edited by sans; 05-23-2012 at 06:44 AM.
I am not sure why my original formula in the other workbook won't do this here:
confirmed with CTRL+SHIFT+ENTER not just ENTER=SUM(SUM(--(N(OFFSET(B42,-J33:J42+1,0))=W42))>0,SUM(--(N(OFFSET(C42,-K33:K42+1,0))=X42))>0,SUM(--(N(OFFSET(D42,-L33:L42+1,0))=Y42))>0,SUM(--(N(OFFSET(E42,-M33:M42+1,0))=Z42))>0,SUM(--(N(OFFSET(F42,-N33:N42+1,0))=AA42))>0,SUM(--(N(OFFSET(G42,-O33:O42+1,0))=AB42))>0,SUM(--(N(OFFSET(H42,-P33:P42+1,0))=AC42))>0)
This looks at the last 10 numbers in column J and compares them to column B, then it takes the last 10 number in column K and compares to column C, etc... so in the end 70 values are compared...
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.
In the attachment below, I've used your formula to check the latest 2 ranges in J:P and count the matching values with "Range To Check". Please let me know if I'm missing something. Thank you
Hi NBVC,
Thank you for your reply. Basically this time, the finding of the numbers in DATA is done a little differently. When the values in J42:P42 look to find the corresponding values in DATA, the counting starts at row 42. When the values in J41:P41 look to find the corresponding value in DATA, the counting starts at row 41. When the values in J40:P40 look to find the corresponding values in DATA, the counting starts at row 40 and so on...
Last edited by sans; 05-23-2012 at 02:03 PM.
What should the final result be in this case? Do you know?
The result in the attachment below should be 4.Thank you
Do you mind going through the logic again on how you get 4 here?
I just made a quick example, I think this will made it clear. Thank you for your help.
Yes, of course. Just give me a couple of minutes to colour code and write a quick explanation. Thank you
The RangeCheck() function I gave you can do this, just use more cells. Put this in the original cell AE42:
=RangeCheck($B$23:$H$42, $J42:$P42, $W$42:$AC$42)
then copy/paste UP through cell AE33. Add those up.
_________________
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!)
Hi Jerry,
I did gave the macro a try but unfortunately I cannot get it to produce the desired results. This is because I want to avoid counting duplicate matches. The maximum match each column can have is 1. So the max total result in AE that can be achieved in any circumstance is 7.
By taking for example the first column, if all 10 values in J33:J42 look for the numbers in the B column in order to count a possible match with number 3 in W42, and two cells in column B match number 3 in W42, the count for the 1st column is still 1, not 2.
I hope I am making sense. Thank you for your reply
Here's a new version of the UDF you can use with more than one row in the second parameter:
Option Explicit Function RangeCheck(dataRNG As Range, Offsets As Range, MyValues As Range) As Long Dim col As Long, r As Range If dataRNG.Columns.Count <> Offsets.Columns.Count Or dataRNG.Columns.Count <> MyValues.Columns.Count Then RangeCheck = -99999 Exit Function End If For col = 1 To dataRNG.Columns.Count For Each r In Offsets.Cells(1, col).Resize(Offsets.Rows.Count) If dataRNG.Cells(dataRNG.Rows.Count - r.Value + 1, col) = MyValues(col) Then RangeCheck = RangeCheck + 1 GoTo NextCol End If Next r NextCol: Next col End Function
Used as: =RangeCheck($B$23:$H$42, $J33:$P42, $W$42:$AC$42)
Thank you very much Jerry! That's exactly what I meant with the duplicates.
I am still encountering a little problem though. What I would like to do is when the values in J42:P42 (row 42) look to find the corresponding values in DATA, the counting up in DATA starts at row 42. When the values in J41:P41 (row 41) look to find the corresponding value in DATA, the counting up in DATA starts at row 41. When the values in J40:P40 look to find the corresponding values in DATA, the counting up in DATA starts at row 40...and so on.
You can see the formula in AE42 where I tried to emulate this. The result in the attachment should be 3. Currently the result is 9.
Thank you very much for your help.
Hi sans,
I was working on a formula last night, but time passed and interruptions occurred and then before you knew it, the day ended... anyway... I see Jerry has provided a macro solution... I thought I'd still give you a possible formula option... so here it is:
CSE confirmed.=SUM(SUM(--(N(OFFSET(B33,SMALL(ROW(INDIRECT("1:10")),ROW(INDIRECT("1:10")))-(J33:J42),0))=X42))>0,SUM(--(N(OFFSET(C33,SMALL(ROW(INDIRECT("1:10")),ROW(INDIRECT("1:10")))-(K33:K42),0))=Y42))>0,SUM(--(N(OFFSET(D33,SMALL(ROW(INDIRECT("1:10")),ROW(INDIRECT("1:10")))-(L33:L42),0))=Z42))>0,SUM(--(N(OFFSET(E33,SMALL(ROW(INDIRECT("1:10")),ROW(INDIRECT("1:10")))-(M33:M42),0))=AA42))>0,SUM(--(N(OFFSET(F33,SMALL(ROW(INDIRECT("1:10")),ROW(INDIRECT("1:10")))-(N33:N42),0))=AB42))>0,SUM(--(N(OFFSET(G33,SMALL(ROW(INDIRECT("1:10")),ROW(INDIRECT("1:10")))-(O33:O42),0))=AC42))>0,SUM(--(N(OFFSET(H33,SMALL(ROW(INDIRECT("1:10")),ROW(INDIRECT("1:10")))-(P33:P42),0))=AD42))>0)
Now I see that the last sample to Jerry had only 3 rows in the J:P columns. Are you saying that it not necessarily 10 rows always? And that if there are 3 rows in J:P, then only 3 rows in B:H should be checked?
In case it does matter, try this monster:
=SUM(SUM(--(N(OFFSET(INDEX(B33:B42,10-COUNT(J33:J42)+1),SMALL(ROW(INDIRECT("1:"&MIN(COUNT(J33:J42),10))),ROW(INDIRECT("1:"&MIN(COUNT(J33:J42),10))))-(INDEX(J33:J42,10-COUNT(J33:J42)+1):J42),0))=X42))>0,SUM(--(N(OFFSET(INDEX(C33:C42,10-COUNT(K33:K42)+1),SMALL(ROW(INDIRECT("1:"&MIN(COUNT(K33:K42),10))),ROW(INDIRECT("1:"&MIN(COUNT(K33:K42),10))))-(INDEX(K33:K42,10-COUNT(K33:K42)+1):K42),0))=Y42))>0,SUM(--(N(OFFSET(INDEX(D33:D42,10-COUNT(L33:L42)+1),SMALL(ROW(INDIRECT("1:"&MIN(COUNT(L33:L42),10))),ROW(INDIRECT("1:"&MIN(COUNT(L33:L42),10))))-(INDEX(L33:L42,10-COUNT(L33:L42)+1):L42),0))=Z42))>0,SUM(--(N(OFFSET(INDEX(E33:E42,10-COUNT(M33:M42)+1),SMALL(ROW(INDIRECT("1:"&MIN(COUNT(M33:M42),10))),ROW(INDIRECT("1:"&MIN(COUNT(M33:M42),10))))-(INDEX(M33:M42,10-COUNT(M33:M42)+1):M42),0))=AA42))>0,SUM(--(N(OFFSET(INDEX(F33:F42,10-COUNT(N33:N42)+1),SMALL(ROW(INDIRECT("1:"&MIN(COUNT(N33:N42),10))),ROW(INDIRECT("1:"&MIN(COUNT(N33:N42),10))))-(INDEX(N33:N42,10-COUNT(N33:N42)+1):N42),0))=AB42))>0,SUM(--(N(OFFSET(INDEX(G33:G42,10-COUNT(O33:O42)+1),SMALL(ROW(INDIRECT("1:"&MIN(COUNT(O33:O42),10))),ROW(INDIRECT("1:"&MIN(COUNT(O33:O42),10))))-(INDEX(O33:O42,10-COUNT(O33:O42)+1):O42),0))=AC42))>0,SUM(--(N(OFFSET(INDEX(H33:H42,10-COUNT(P33:P42)+1),SMALL(ROW(INDIRECT("1:"&MIN(COUNT(P33:P42),10))),ROW(INDIRECT("1:"&MIN(COUNT(P33:P42),10))))-(INDEX(P33:P42,10-COUNT(P33:P42)+1):P42),0))=AD42))>0)
Last edited by NBVC; 05-24-2012 at 09:18 AM.
Ok, this version will look UP in the main table starting at the row that coincides with the current offset value in parameter #2. Again, it will only check one column of offsets until a match is found, then move to the next columns, so the highest number you'll get would be the total number of columns, in this case max = 7.
Option Explicit Function RangeCheck(dataRNG As Range, Offsets As Range, MyValues As Range) As Long Dim col As Long, r As Range If dataRNG.Columns.Count <> Offsets.Columns.Count Or dataRNG.Columns.Count <> MyValues.Columns.Count Then RangeCheck = -99999 Exit Function End If For col = 1 To dataRNG.Columns.Count For Each r In Offsets.Cells(1, col).Resize(Offsets.Rows.Count) Debug.Print Cells(r.Row, dataRNG.Columns(col).Column).Address If Cells(r.Row, dataRNG.Columns(col).Column).Offset(-r.Value) = MyValues(col) Then RangeCheck = RangeCheck + 1 GoTo NextCol End If Next r NextCol: Next col End Function
@ NBVC and JBeaucaire
Thank you both very much for the replies.
@ NBVC
No, I just made a smaller example. If we are using 3 ranges in J:P for matches, it doesn't mean that only 3 rows in B:H are available to be checked.
The rows in J:P that I would like to check are sometimes 3 or 4 or 5 etc... So I have set up different configurations.
If what I just said doesn't make any sense, please disregard it
@JBeaucaire
Yes that's correct. The max count that each column can produce is 1 so if there are 7 columns the max result that can be achieved is 7 - and the lookup in Data begins at the same row as the value in J:P.
I'll try both solutions now and get back to you both. I've made another small example. Hopefully this will be the one that will clear things up.
I didn't mean that only 3 rows would be available in B:H, I meant that there would only be 3 "offsets" for each column in B:H according to the fact that there are 3 rows in J:P.... my second formula is designed on that basis....Originally Posted by sans
@ NBVC
No, I just made a smaller example. If we are using 3 ranges in J:P for matches, it doesn't mean that only 3 rows in B:H are available to be checked.
The rows in J:P that I would like to check are sometimes 3 or 4 or 5 etc... So I have set up different configurations.
If what I just said doesn't make any sense, please disregard it
Oops...I apologise, I misunderstood of what you were trying to say.
I am still a little bit lost with though. I am trying to modify the formula to get the result for every range in W42:AC42. When I extend the range to cover the whole "Data" values I receive a REF# error. Could you please guide me as to what I am doing wrong? For every range in W42:AC42 I would like to use the 10 ranges in J:P.
Thank you
@JBeaucaire
I tried the macro for the example in post #19 but I receive as a result 3. The correct result in the example is 4. I'll double checked the attachment and the correct result is 4. Thank you
Try:
=SUM(SUM(--(N(OFFSET(INDEX(B10:B19,10-COUNT(J10:J19)+1),SMALL(ROW(INDIRECT("1:"&MIN(COUNT(J10:J19),10))),ROW(INDIRECT("1:"&MIN(COUNT(J10:J19),10))))-(INDEX(J10:J19,10-COUNT(J10:J19)+1):J19),0))=W19))>0,SUM(--(N(OFFSET(INDEX(C10:C19,10-COUNT(K10:K19)+1),SMALL(ROW(INDIRECT("1:"&MIN(COUNT(K10:K19),10))),ROW(INDIRECT("1:"&MIN(COUNT(K10:K19),10))))-(INDEX(K10:K19,10-COUNT(K10:K19)+1):K19),0))=X19))>0,SUM(--(N(OFFSET(INDEX(D10:D19,10-COUNT(L10:L19)+1),SMALL(ROW(INDIRECT("1:"&MIN(COUNT(L10:L19),10))),ROW(INDIRECT("1:"&MIN(COUNT(L10:L19),10))))-(INDEX(L10:L19,10-COUNT(L10:L19)+1):L19),0))=Y19))>0,SUM(--(N(OFFSET(INDEX(E10:E19,10-COUNT(M10:M19)+1),SMALL(ROW(INDIRECT("1:"&MIN(COUNT(M10:M19),10))),ROW(INDIRECT("1:"&MIN(COUNT(M10:M19),10))))-(INDEX(M10:M19,10-COUNT(M10:M19)+1):M19),0))=Z19))>0,SUM(--(N(OFFSET(INDEX(F10:F19,10-COUNT(N10:N19)+1),SMALL(ROW(INDIRECT("1:"&MIN(COUNT(N10:N19),10))),ROW(INDIRECT("1:"&MIN(COUNT(N10:N19),10))))-(INDEX(N10:N19,10-COUNT(N10:N19)+1):N19),0))=AA19))>0,SUM(--(N(OFFSET(INDEX(G10:G19,10-COUNT(O10:O19)+1),SMALL(ROW(INDIRECT("1:"&MIN(COUNT(O10:O19),10))),ROW(INDIRECT("1:"&MIN(COUNT(O10:O19),10))))-(INDEX(O10:O19,10-COUNT(O10:O19)+1):O19),0))=AB19))>0,SUM(--(N(OFFSET(INDEX(H10:H19,10-COUNT(P10:P19)+1),SMALL(ROW(INDIRECT("1:"&MIN(COUNT(P10:P19),10))),ROW(INDIRECT("1:"&MIN(COUNT(P10:P19),10))))-(INDEX(P10:P19,10-COUNT(P10:P19)+1):P19),0))=AC19))>0)
Thank you. I already got the results to show from your formula in post #17. Basically my question is, lets say the formula checks the first range W19:AC19, taking the 1st column J for example, the value in J14 is 12, so will the formula find number 13 in B3? The reason I am asking is because the blue range in the B column reaches only up to row 10 and number 13 is found in row3.
That's why I was trying to extend all ranges in B:H before and I was getting the ref# error.
Thank you
The highlighted range in columns B:H are indicating the rows that are being offset from... so, yes, if one of the values in Column J is 20, then it should go up 20 rows from which relative position in B:H rows is needed....
I see...I thought that the selected ranges in B:H were indicating the available range for each column in J:P. Silly me I was trying to extend the ranges up to row 3 and lockand was wondering why I was getting an error.
Thank you for your help
If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.
Sorry, I got carried away in processing data.
Last edited by sans; 05-25-2012 at 03:58 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks