detect in each draw line the mirror number corresponds based on the mirror table.

For the specific example you've provided, try:

ABCDEFGH
101234mirror table
256789
3
4INOUT
50651
61166
71469
81661
92176
102277
112570
123287
133388
143489
154691
165500
176116
187025
197328
207823
218833
228934
239045
249540
250055
Planilha1
Cell Formulas
RangeFormula
C5:C25C5=10*MOD(LEFT(TEXT(A5,"00"),1)+5,10)+MOD(RIGHT(A5,1)+5,10)

If your mirror can be "cracked", try:

ABCDEFGHIJKL
1IN0123456789
2OUT5687901324
3
4INOUT
50651
61166
71469
81661
92186
102288
112580
123278
133377
143479
154691
165500
176116
187035
197337
207832
218822
228924
239045
249540
250055
26
Planilha1
Cell Formulas
RangeFormula
C5:C25C5=10*HLOOKUP(--LEFT(TEXT(A5,"00"),1),mirror,2,)+HLOOKUP(--RIGHT(A5,1),mirror,2,)
Named Ranges
NameRefers ToCells
mirror=Planilha1!$B$1:$K$2C5:C25
 
Upvote 0
hello, Crump! the formula is giving an error!, can you change it to a vba macro?
 
Upvote 0
If you want a formula to copy down, give this a try...

=LET(n,MID(A5,{1,2},1),CONCAT(n+5-10*(0+n>4)))

However, this single formula will output the entire list for the specified range...

=MAP(A5:A25,LAMBDA(x,LET(n,MID(x,{1,2},1),CONCAT(n+5-10*(0+n>4)))))

Note: This formula does not require the mirror table as the mirroring is built into it.
 
Upvote 0
the formula is giving an error!
I applied my formula to your workbook, in which the input values 06, 11, 14, 16, 21 etc are numbers, formatted as "00".

ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAO
1
2draws 100-20
3
410ª11ª12ª13ª14ª15ª16ª17ª18ª19ª20ª10ª11ª12ª13ª14ª15ª16ª17ª18ª19ª20ª
506111416212225323334466170737888899095005166696176777087888991162528233334454055
608111417182429374449515562636476828493975366696263797482999406001718192137394842
708101117202228344547485455576774798591995365666275777389909293090002122924304644
805121425303539414449515560636467788393985067697085808496999406001518191223384843
Planilha1
Cell Formulas
RangeFormula
V5:AO8V5=10*MOD(LEFT(TEXT(A5,"00"),1)+5,10)+MOD(RIGHT(A5,1)+5,10)

Rick's formulae (if you have Excel 365?) assume text values "06", "11", "14", etc

How are you applying my formula, to get an error?
 
Upvote 0
Hello crump and rich, my experience in excel is weak, can you put it in the file to download the excel? Please
 
Upvote 0
Click here, and you can paste the layout and formulae directly into cell A1 of a blank worksheet:

1739743641310.png


But here are the three versions: Mirror.xlsx

ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAP
1
2draws 100-20
3Number valuesStephen
4
510ª11ª12ª13ª14ª15ª16ª17ª18ª19ª20ª10ª11ª12ª13ª14ª15ª16ª17ª18ª19ª20ª
606111416212225323334466170737888899095005166696176777087888991162528233334454055
708111417182429374449515562636476828493975366696263797482999406001718192137394842
808101117202228344547485455576774798591995365666275777389909293090002122924304644
905121425303539414449515560636467788393985067697085808496999406001518191223384843
10
11Text valuesRick - 1
12
1310ª11ª12ª13ª14ª15ª16ª17ª18ª19ª20ª10ª11ª12ª13ª14ª15ª16ª17ª18ª19ª20ª
1406111416212225323334466170737888899095005166696176777087888991162528233334454055
1508111417182429374449515562636476828493975366696263797482999406001718192137394842
1608101117202228344547485455576774798591995365666275777389909293090002122924304644
1705121425303539414449515560636467788393985067697085808496999406001518191223384843
18
19Rick - 2
20
2110ª11ª12ª13ª14ª15ª16ª17ª18ª19ª20ª
225166696176777087888991162528233334454055
235366696263797482999406001718192137394842
245365666275777389909293090002122924304644
255067697085808496999406001518191223384843
26
Mirror
Cell Formulas
RangeFormula
V6:AO9V6=10*MOD(LEFT(TEXT(A6,"00"),1)+5,10)+MOD(RIGHT(A6,1)+5,10)
A14:T17A14=TEXT(A6,"00")
V14:AO17V14=LET(n,MID(A14,{1,2},1),CONCAT(n+5-10*(0+n>4)))
V22:AO25V22=MAP(A14:T17,LAMBDA(x,LET(n,MID(x,{1,2},1),CONCAT(n+5-10*(0+n>4)))))
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,226,797
Messages
6,193,051
Members
453,772
Latest member
aastupin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top