Using sumproduct with left and right

[LuGyver]

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

 

[LuGyver]

Eric W the work you've done is beautiful to say the least!
The last change didn't work as planned, but I found your first two, A3 and A4 have reduced pairs to an amazing perfection. Then I took the two and simply used each formula in a second set such as A6 (where a new set of single digits are entered) and then A7 holds A3 formula, with cell adjustments of course and etc..., and goal complete!

My hats off to you my friend! Perfect!
Thank you so much for you patience with me

All the best to you and family

 

[Eric W]

Glad to help!

 

[LuGyver]

Eric W one more question if you don't mind

I used a new sheet for this test run. During the test, I copied and pasted all data over so as to have no interference. I then would change the references accordingly and all was good.

I just found that when I moved the formula to a sheet with a lot of data in it comes up blank results for the same information.
The location is different but that is it.

would other data created by formulas interfere?

thanks

 

[Eric W]

It shouldn't. I'd check to see if the cells are empty or not. The formulas look for a 2-digit number, or an empty cell, but if the cell has spaces in it, that could throw things off.

 

[LuGyver]

It shouldn't. I'd check to see if the cells are empty or not. The formulas look for a 2-digit number, or an empty cell, but if the cell has spaces in it, that could throw things off.

Okay that rules out your side. I suspect a text/number format discrepancy somewhere because there are no empty cells. I may try to rebuild one set at a time to see where the issue is found.

I'll let you know, thank you

 

[LuGyver]

Eric W,
I found a few issues and fixed them so there are no blanks anywhere.
To be clear (should have mentioned it earlier), the formula you provided for the test sheets are place in lower cells ie A1 A2 A3 A4..., whereas my REAL data is all in one row and thinking I may be getting an issue with that single row concept.

Right now I do get results, but they are different results in a one row scenario than that which we seen during testing in A1 through A4.

If it helps my data is set up
Specific data to achieve pairs
A1:J1000

Pairs Location
L1:CO1000

Single Digits
CP1:CY1000

Calculations You provided for A3
CZ1:CG1000

I have not gone further yet with the other two formulas you gave for A4 and A5
Let me know if you see or need anything from me.
Thank you

 

[Eric W]

I'm not sure exactly what you're asking. The formulas should work find no matter where the data is, if you change the range references in them correctly. Since that can be tricky sometimes, it's usually best to show your sample data in the same place on the sheet where it actually is.

Another thought is that these formulas do work, but they are pretty complicated, and probably prone to breaking if something is a little out of whack. You might be better off with a VBA solution. Either a user-defined function that does the same thing as the current formulas, or a macro that reads your entire sheet and calculates the results for all 1000 rows at once. If either of those is of interest, let me know, and show me EXACTLY where your data is on the sheets(s), and EXACTLY what data you want returned, and WHERE. You may not want/need any of the intermediate results.

 

[LuGyver]

I'm not sure exactly what you're asking. The formulas should work find no matter where the data is, if you change the range references in them correctly. Since that can be tricky sometimes, it's usually best to show your sample data in the same place on the sheet where it actually is.

Another thought is that these formulas do work, but they are pretty complicated, and probably prone to breaking if something is a little out of whack. You might be better off with a VBA solution. Either a user-defined function that does the same thing as the current formulas, or a macro that reads your entire sheet and calculates the results for all 1000 rows at once. If either of those is of interest, let me know, and show me EXACTLY where your data is on the sheets(s), and EXACTLY what data you want returned, and WHERE. You may not want/need any of the intermediate results.

Okay thank you. You're right you've been good to me. I have to stop the panic attacks... It is in my the data somewhere. I started over with a clean sheet in an entirely new book and will just do one line set at a time until I get past the issue. If I still can't get it I will give you the file along with what needs to happen in the return.

Thank you

 

[LuGyver]

I'm not sure exactly what you're asking. The formulas should work find no matter where the data is, if you change the range references in them correctly. Since that can be tricky sometimes, it's usually best to show your sample data in the same place on the sheet where it actually is.

Another thought is that these formulas do work, but they are pretty complicated, and probably prone to breaking if something is a little out of whack. You might be better off with a VBA solution. Either a user-defined function that does the same thing as the current formulas, or a macro that reads your entire sheet and calculates the results for all 1000 rows at once. If either of those is of interest, let me know, and show me EXACTLY where your data is on the sheets(s), and EXACTLY what data you want returned, and WHERE. You may not want/need any of the intermediate results.

Eric Sorry to bother you so much. Heres a link to my file. What I want is not much different except that it is all in one row. I just can get what I need out of it. I hope this link works.
No VB or macros please and I need to be able to fill down (as you will see)
Thank you
https://drive.google.com/file/d/1i6I4vWGJ7XFvQ3Xcr76B1ydorWhNYfQZ/view?usp=sharing

 

[Eric W]

I haven't exhaustively tested the formulas, but I found an issue with the first one, and it appears the same issue occurs in all the formulas. The CZ3 formula should be:

=IFERROR(INDEX($O3:$CR3,SMALL(IF(MMULT(COLUMN($CT3:$CX3)^0,IF(RIGHT($O3:$CR3,1)=TRANSPOSE($CT3:$CX3&""),1,0))*($O3:$CR3<>""),COLUMN($O3:$CR3)-COLUMN($O3)+1),COLUMNS($CZ3:CZ3))),"")

Add the part in red. That section generates an offset to give to the first INDEX in the formula. The part in red is not needed if the starting column is A, but when you moved the ranges, it became necessary. The part in blue you can change if you want. I generally like the range there to be the first cell in the range, but it'll work as is.

I haven't tested the rest of the formulas for accuracy, but when I add the red part to the formulas in GK3, JV3, NG3, QR3, UC3, and XN3 they all start generating results.