The following task seems simple... I just hope it is possible
I will break this down into pieces to see each step because it boggles my head when I try to build a formula all the steps at once.
NOTE: it is okay if the steps have to be done in separate ranges. However I want to keep them in rows and not columns.
First data is in over one thousand rows, but to keep it simple, I say data is in A1:Y1 range.
Second, the set of single Digits in A2:E2 are changed manually.
Lastly, I would like the sumproduct formula done all in one row (ie A3:Y3) if it can be. However, I am splitting the steps to include A4:Y4 and A5:Y5 for simplicity in seeing what I am after....
1. In the range A1:Y1 I have pairs such as 12 23 13 34 72 43 95 63 48 17 etc.
2. in a second range, say A2:E2, I have single digit(s), which I enter as needed, such as 1 5 7 3 6
I would like to have sumproduct() look at the right of all the pairs in A1:Y1 and return all that match any of the digits in Range A2:E2.
In this case sumproduct in range A3:Y3 returns 23 13 43 63 95 17.
3. in range A4:Y4 I need sumproduct to look at the resulting pairs left digits, and return all the pairs the have their left digit = to the left digit(s) of the pairs results in A3:Y3..
In the case above, we would see the left digit of our original return from A3:Y3 which is = 23 13 43 63 95 17:
so those left digits are 23 = 2, 13 = 1, 43 = 4, 63 = 6, 95 = 9, 17 = 1 ------ (2, 1, 4, 6, 9, 1)
so we are now looking for any pairs in A1:Y1
that have left digits = 2, 1, 4, 6, 9, 1
4. The results in A4:Y4 would be 23 13 43 63 95 17 minus(-) the first results (from A3:Y3) = 48 (and any other pairs which arise). These results stay and will remain for the remained of the calculation AND include the following.
5. In A5:Y5, sumproduct needs to look at the results in A4:Y4 and bring any pairs from A1:Y1 that have the left and/or right digits from A4:Y4. in this case = 48 = digits 4, 8
so sumproduct will be looking for left or right digits from A1:Y1 which are = 4, 8 and then return 43, 34 and include 48
The final results are 43, 34, 48
This seems easy but I don't understand sumproduct enough yet. I have been using if() with countif(s) which is not efficient and gets real confusingly cumbersome.
Hope this is said well enough
Thank you for your time and help
I will break this down into pieces to see each step because it boggles my head when I try to build a formula all the steps at once.
NOTE: it is okay if the steps have to be done in separate ranges. However I want to keep them in rows and not columns.
First data is in over one thousand rows, but to keep it simple, I say data is in A1:Y1 range.
Second, the set of single Digits in A2:E2 are changed manually.
Lastly, I would like the sumproduct formula done all in one row (ie A3:Y3) if it can be. However, I am splitting the steps to include A4:Y4 and A5:Y5 for simplicity in seeing what I am after....
1. In the range A1:Y1 I have pairs such as 12 23 13 34 72 43 95 63 48 17 etc.
2. in a second range, say A2:E2, I have single digit(s), which I enter as needed, such as 1 5 7 3 6
I would like to have sumproduct() look at the right of all the pairs in A1:Y1 and return all that match any of the digits in Range A2:E2.
In this case sumproduct in range A3:Y3 returns 23 13 43 63 95 17.
3. in range A4:Y4 I need sumproduct to look at the resulting pairs left digits, and return all the pairs the have their left digit = to the left digit(s) of the pairs results in A3:Y3..
In the case above, we would see the left digit of our original return from A3:Y3 which is = 23 13 43 63 95 17:
so those left digits are 23 = 2, 13 = 1, 43 = 4, 63 = 6, 95 = 9, 17 = 1 ------ (2, 1, 4, 6, 9, 1)
so we are now looking for any pairs in A1:Y1
that have left digits = 2, 1, 4, 6, 9, 1
4. The results in A4:Y4 would be 23 13 43 63 95 17 minus(-) the first results (from A3:Y3) = 48 (and any other pairs which arise). These results stay and will remain for the remained of the calculation AND include the following.
5. In A5:Y5, sumproduct needs to look at the results in A4:Y4 and bring any pairs from A1:Y1 that have the left and/or right digits from A4:Y4. in this case = 48 = digits 4, 8
so sumproduct will be looking for left or right digits from A1:Y1 which are = 4, 8 and then return 43, 34 and include 48
The final results are 43, 34, 48
This seems easy but I don't understand sumproduct enough yet. I have been using if() with countif(s) which is not efficient and gets real confusingly cumbersome.
Hope this is said well enough
Thank you for your time and help
