Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array search
Thanks all for helping. Here are the details and I'll ask the question in a
minute. I have a sheet with sets of 3 columns (ABC, DEF, GHI, JKL and so on) all numeric and no blank cells. To keep it simple let us say that columns PQR is the extent of the sets. Column P is the basis for a horizontal count. Columns Q & R are added together and their total will be the Vertical count. The horizontal count starts from P and counts from right to left as follows ABC=4 DEF=3 GHI=2 JKL=1 MNO=0 PQR. IF P25=3, Q25=3, R25=4 The count would be horizontal from P25 to D25:F25 (set #3). The Vertical count would be (Q25=3 + R25=4)=7. So D25:F25 less 7 = vertical count back to D18:F18. With that said, is it possible to have a formula find D18:F18 based on the earlier parameters, then match D17:F19 with P26:R26 (not D25:F25). I hope this is clear enough Thank you, Luke |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array search
Hi,
You might want to revisit this description. I for one have a hard time following it and can understand what you mean by "match D17:F19 with P26:R26 (not D25:F25)" What do you mean by match are 9 cell range (D17:F19) with a 3 cell range P26:R26 ? -- If this helps, please click the Yes button Cheers, Shane Devenshire "Luke" wrote: Thanks all for helping. Here are the details and I'll ask the question in a minute. I have a sheet with sets of 3 columns (ABC, DEF, GHI, JKL and so on) all numeric and no blank cells. To keep it simple let us say that columns PQR is the extent of the sets. Column P is the basis for a horizontal count. Columns Q & R are added together and their total will be the Vertical count. The horizontal count starts from P and counts from right to left as follows ABC=4 DEF=3 GHI=2 JKL=1 MNO=0 PQR. IF P25=3, Q25=3, R25=4 The count would be horizontal from P25 to D25:F25 (set #3). The Vertical count would be (Q25=3 + R25=4)=7. So D25:F25 less 7 = vertical count back to D18:F18. With that said, is it possible to have a formula find D18:F18 based on the earlier parameters, then match D17:F19 with P26:R26 (not D25:F25). I hope this is clear enough Thank you, Luke |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array search
Some other thoughts ..
... a formula find D18:F18 based on the earlier parameters, then match D17:F19 with P26:R26 (not D25:F25) Maybe you're looking to fashion something like this, in say S25: =SUMPRODUCT(--ISNUMBER(MATCH(OFFSET(A25,-(SUM(Q25:R25)+1),P25,3,3),P26:R26,0))) where this term: OFFSET(A25,-(SUM(Q25:R25)+1),P25,3,3) will return the required 3R x 3C matrix: D17:F19 based on the parameters in P25:R25 (the A25 is just an anchor point on the same row) The row param: -(SUM(Q25:R25)+1) will locate the top left cell of the 3x3 matrix vertically up from the anchor A25, with an arithmetic adjustment of +1 to SUM(Q25:R25), since SUM(Q25:R25) locates the row for "D18:F18" -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Luke" wrote: Thanks all for helping. Here are the details and I'll ask the question in a minute. I have a sheet with sets of 3 columns (ABC, DEF, GHI, JKL and so on) all numeric and no blank cells. To keep it simple let us say that columns PQR is the extent of the sets. Column P is the basis for a horizontal count. Columns Q & R are added together and their total will be the Vertical count. The horizontal count starts from P and counts from right to left as follows ABC=4 DEF=3 GHI=2 JKL=1 MNO=0 PQR. IF P25=3, Q25=3, R25=4 The count would be horizontal from P25 to D25:F25 (set #3). The Vertical count would be (Q25=3 + R25=4)=7. So D25:F25 less 7 = vertical count back to D18:F18. With that said, is it possible to have a formula find D18:F18 based on the earlier parameters, then match D17:F19 with P26:R26 (not D25:F25). |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array search
Shane,
Sorry I did leave that out. I am using a much larger matrix and forgot to mention there are data beyond P25:R25. Essentially for this example, P25:R25 are the cells to acquire the horizontal and vertical count, that in turn finds the range/array D17:F19. Then, Any of the cells in P26:R26 that match any of the cells in D17:F19 would be displayed at a defined cell range... Say for this example X26:Z26 (Ooops, another tidbit I left out). I Hope this helps. Luke "Shane Devenshire" wrote: Hi, You might want to revisit this description. I for one have a hard time following it and can understand what you mean by "match D17:F19 with P26:R26 (not D25:F25)" What do you mean by match are 9 cell range (D17:F19) with a 3 cell range P26:R26 ? -- If this helps, please click the Yes button Cheers, Shane Devenshire "Luke" wrote: Thanks all for helping. Here are the details and I'll ask the question in a minute. I have a sheet with sets of 3 columns (ABC, DEF, GHI, JKL and so on) all numeric and no blank cells. To keep it simple let us say that columns PQR is the extent of the sets. Column P is the basis for a horizontal count. Columns Q & R are added together and their total will be the Vertical count. The horizontal count starts from P and counts from right to left as follows ABC=4 DEF=3 GHI=2 JKL=1 MNO=0 PQR. IF P25=3, Q25=3, R25=4 The count would be horizontal from P25 to D25:F25 (set #3). The Vertical count would be (Q25=3 + R25=4)=7. So D25:F25 less 7 = vertical count back to D18:F18. With that said, is it possible to have a formula find D18:F18 based on the earlier parameters, then match D17:F19 with P26:R26 (not D25:F25). I hope this is clear enough Thank you, Luke |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array search
Max, Example below G15:O26 are left blank to save space in this reply. All
cells have digits in reality with no blank cells. In D17:F19 = 943, 178, 206 with respect to each cell. In P26:R26 = 164 with respect to each cell. Note that numbers 1, 6, & 4 exist in D17:D19. In S26:U26 = 146 (Result cells) P25:R25 =334: P25=3 means move right to left from P25, 12 columns, or, 4 sets of three columns. Left horizontal movement, as it were, to D25:F25. "ABC=4 DEF=3 GHI=2 JKL=1 MNO=0 PQR" (Q25=3 + R25=4) = 7 means move up vertically 7 cells to D18:F18 from D25:F25. Then compare P26, Q26 & R26 to the array D17:F19. OR, If that is not possible then I could live with just finding and comparing P26, Q26 & R26 with D18, E18, & F18 placing the results in S26, T26, & U26 respectfully. A B C D E F..G,H,I J,K,L M,N,O P Q R S T U 15 2 1 0 1 1 5 7 6 9 16 5 5 5 5 2 5 8 4 1 17 3 6 0 9 4 3 0 4 4 18 5 1 2 1 7 8 9 9 4 19 4 2 4 2 0 6 1 8 7 20 0 4 1 4 3 0 3 8 1 21 3 6 7 5 2 7 6 5 4 22 2 5 2 1 1 9 7 1 1 23 5 8 6 4 7 0 5 4 9 24 7 7 9 3 3 7 4 8 2 25 7 9 7 4 6 2 3 3 4 26 5 9 4 6 7 2 1 6 4 1 4 6 27 2 8 8 4 0 2 5 0 9 I hope this helps or at leaset makes better sense. Luke "Max" wrote: Some other thoughts .. ... a formula find D18:F18 based on the earlier parameters, then match D17:F19 with P26:R26 (not D25:F25) Maybe you're looking to fashion something like this, in say S25: =SUMPRODUCT(--ISNUMBER(MATCH(OFFSET(A25,-(SUM(Q25:R25)+1),P25,3,3),P26:R26,0))) where this term: OFFSET(A25,-(SUM(Q25:R25)+1),P25,3,3) will return the required 3R x 3C matrix: D17:F19 based on the parameters in P25:R25 (the A25 is just an anchor point on the same row) The row param: -(SUM(Q25:R25)+1) will locate the top left cell of the 3x3 matrix vertically up from the anchor A25, with an arithmetic adjustment of +1 to SUM(Q25:R25), since SUM(Q25:R25) locates the row for "D18:F18" -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Luke" wrote: Thanks all for helping. Here are the details and I'll ask the question in a minute. I have a sheet with sets of 3 columns (ABC, DEF, GHI, JKL and so on) all numeric and no blank cells. To keep it simple let us say that columns PQR is the extent of the sets. Column P is the basis for a horizontal count. Columns Q & R are added together and their total will be the Vertical count. The horizontal count starts from P and counts from right to left as follows ABC=4 DEF=3 GHI=2 JKL=1 MNO=0 PQR. IF P25=3, Q25=3, R25=4 The count would be horizontal from P25 to D25:F25 (set #3). The Vertical count would be (Q25=3 + R25=4)=7. So D25:F25 less 7 = vertical count back to D18:F18. With that said, is it possible to have a formula find D18:F18 based on the earlier parameters, then match D17:F19 with P26:R26 (not D25:F25). |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array search
Maybe this might satisfy your complex intents ..
In S26: =IF(COUNTIF(OFFSET($A25,-(SUM($Q25:$R25)+1),$P25,3,3),P26),P26,"") Copy S26 to U26. This checks each element in P26:R26 vs the array D17:F19 and returns match results in the same order, ie in your sample, it'll return as: 1, 6, 4. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Luke" wrote: Max, Example below G15:O26 are left blank to save space in this reply. All cells have digits in reality with no blank cells. In D17:F19 = 943, 178, 206 with respect to each cell. In P26:R26 = 164 with respect to each cell. Note that numbers 1, 6, & 4 exist in D17:D19. In S26:U26 = 146 (Result cells) P25:R25 =334: P25=3 means move right to left from P25, 12 columns, or, 4 sets of three columns. Left horizontal movement, as it were, to D25:F25. "ABC=4 DEF=3 GHI=2 JKL=1 MNO=0 PQR" (Q25=3 + R25=4) = 7 means move up vertically 7 cells to D18:F18 from D25:F25. Then compare P26, Q26 & R26 to the array D17:F19. OR, If that is not possible then I could live with just finding and comparing P26, Q26 & R26 with D18, E18, & F18 placing the results in S26, T26, & U26 respectfully. A B C D E F..G,H,I J,K,L M,N,O P Q R S T U 15 2 1 0 1 1 5 7 6 9 16 5 5 5 5 2 5 8 4 1 17 3 6 0 9 4 3 0 4 4 18 5 1 2 1 7 8 9 9 4 19 4 2 4 2 0 6 1 8 7 20 0 4 1 4 3 0 3 8 1 21 3 6 7 5 2 7 6 5 4 22 2 5 2 1 1 9 7 1 1 23 5 8 6 4 7 0 5 4 9 24 7 7 9 3 3 7 4 8 2 25 7 9 7 4 6 2 3 3 4 26 5 9 4 6 7 2 1 6 4 1 4 6 27 2 8 8 4 0 2 5 0 9 I hope this helps or at leaset makes better sense. Luke |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array search
Tinkered with 2 other result variations for you to try out over there
This will return the matched results in ascending order from right to left Array-entered in S26, copied to U26: =SMALL(IF(COUNTIF($P26:$R26,OFFSET($A25,-(SUM($Q25:$R25)+1),$P25,3,3))*OFFSET($A25,-(SUM($Q25:$R25)+1),$P25,3,3)0,COUNTIF($P26:$R26,O FFSET($A25,-(SUM($Q25:$R25)+1),$P25,3,3))*OFFSET($A25,-(SUM($Q25:$R25)+1),$P25,3,3)),COLUMNS($A:A)) Above returns #NUM! if element in P26:R26 is unmatched This will return the matched results in descending order from right to left Array-entered in S26, copied to U26: =LARGE(COUNTIF($P26:$R26,OFFSET($A25,-(SUM($Q25:$R25)+1),$P25,3,3))*OFFSET($A25,-(SUM($Q25:$R25)+1),$P25,3,3),COLUMNS($A:A)) ABove returns zero if element in P26:R26 is unmatched NB: Array-entered means press CTRL+SHIFT+ENTER to confirm the formula -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array search
Very nice! I'm not sure exactly how that works but I did get the results.
However if I fill S26:U26 up or down the results are some what off base. Any Ideas? I will work with this to see if I can understand it better. Nice Job Max! I will look back at this in the morning to catch any replies. Luke "Max" wrote: Maybe this might satisfy your complex intents .. In S26: =IF(COUNTIF(OFFSET($A25,-(SUM($Q25:$R25)+1),$P25,3,3),P26),P26,"") Copy S26 to U26. This checks each element in P26:R26 vs the array D17:F19 and returns match results in the same order, ie in your sample, it'll return as: 1, 6, 4. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Luke" wrote: Max, Example below G15:O26 are left blank to save space in this reply. All cells have digits in reality with no blank cells. In D17:F19 = 943, 178, 206 with respect to each cell. In P26:R26 = 164 with respect to each cell. Note that numbers 1, 6, & 4 exist in D17:D19. In S26:U26 = 146 (Result cells) P25:R25 =334: P25=3 means move right to left from P25, 12 columns, or, 4 sets of three columns. Left horizontal movement, as it were, to D25:F25. "ABC=4 DEF=3 GHI=2 JKL=1 MNO=0 PQR" (Q25=3 + R25=4) = 7 means move up vertically 7 cells to D18:F18 from D25:F25. Then compare P26, Q26 & R26 to the array D17:F19. OR, If that is not possible then I could live with just finding and comparing P26, Q26 & R26 with D18, E18, & F18 placing the results in S26, T26, & U26 respectfully. A B C D E F..G,H,I J,K,L M,N,O P Q R S T U 15 2 1 0 1 1 5 7 6 9 16 5 5 5 5 2 5 8 4 1 17 3 6 0 9 4 3 0 4 4 18 5 1 2 1 7 8 9 9 4 19 4 2 4 2 0 6 1 8 7 20 0 4 1 4 3 0 3 8 1 21 3 6 7 5 2 7 6 5 4 22 2 5 2 1 1 9 7 1 1 23 5 8 6 4 7 0 5 4 9 24 7 7 9 3 3 7 4 8 2 25 7 9 7 4 6 2 3 3 4 26 5 9 4 6 7 2 1 6 4 1 4 6 27 2 8 8 4 0 2 5 0 9 I hope this helps or at leaset makes better sense. Luke |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array search
Luke,
I've posted another 2 variations using array SMALL/LARGE to return results in ascending/descending order to play around with However if I fill S26:U26 up or down the results are some what off base The crux flexi-capture of the array is via the OFFSET, which construct is explained in my 1st response. Maybe it still needs some further refinement, I don't know, as I've made some interpretations/assumptions based on your descripts on the params. Tinker with the OFFSET's params, that's the key here. In the formula bar, select just the OFFSET term, then press F9 to see the result array generated: {...} - reconcile with the matrix on the sheet that it is supposed to capture. Press Esc to revert. If the OFFSET's capture is not correct, then of course the downstream matching with cols P to R will yield meaningless results. Btw, pl mark all responses which contribute to resolving your complex issue by clicking the YES button below. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Luke" wrote: Very nice! I'm not sure exactly how that works but I did get the results. However if I fill S26:U26 up or down the results are some what off base. Any Ideas? I will work with this to see if I can understand it better. Nice Job Max! I will look back at this in the morning to catch any replies. Luke |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
search macro with array | New Users to Excel | |||
Please help on array search & result | Excel Worksheet Functions | |||
search array | Excel Worksheet Functions | |||
Text Search in an Array | Excel Worksheet Functions | |||
Text Search in an Array | Excel Discussion (Misc queries) |