Auto repeat numbers

teacherr1

New Member
Joined
Nov 15, 2021
Messages
8
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
Hello, i tried search for an answer and guidance for long hours but I cant find, beacuse i dont even know what I'm looking for - formula, function etc. Ill try explain it best as on mini-sheet example. Thanks from advance!

example.xlsx
ABCDEFGHIJKLMN
1DATA 1RESULTSNSPACExSPACENSPACE +SPACEN
21 x 45 459x99 +36=9 x 99 + 36 and that gives 99.99.99.99.99.99.99.99.99.36
32 x 45 45.45N=NATURAL number with range from 1-99
43 x 45 45.45.45x = how many times+ = plus one of that number
54 x 45 45.45.45.45
65 x 45 45.45.45.45.45How to make it automaticly showing results like above?
76 x 45 45.45.45.45.45.45Every day at work I'm recieving new excel files, with same construction/style
87 x 45 45.45.45.45.45.45.45
98 x 45 45.45.45.45.45.45.45.45Need auto-results like on examples
109 x 45 45.45.45.45.45.45.45.45.456 x 45 + 18
11
12DATA 2RESULTS2 x 45 + 18
132 x 4545.45
143 x 6363.63.6336
159 x 3333.33.33.33.33.33.33.33.332 x 36 + 18
167 x 1818.18.18.18.18.18.18
173 x 33.3.3.5 x 45 + 27
18
19DATA 3RESULTS24 + 18
206 x 22 + 4822.22.22.22.22.22.4848
213 x 11 + 2011.11.11.202 x 36 + 9
223 x 9 + 369.9.9.366 x 28 + 24
233 x 63 + 9963.63.63.996 x 28
243 x 63 63.63.638 x 48 + 12
2530 + 12
26Ex. "9 x 45" mean write NINE times number 99 separated by DOT "." like 45.45.45.45.45.45.45.45.45
27Ex.2 "3 x 36 + 99" mean write THREE times number 36 and ONE time number 99 separated by dot like 36.36.36.99
Sheet1
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
I assume you're looking for a formula. This can probably be done with native worksheet functions, but for now here's a UDF you can try. Use it as you would use any worksheet function as in the example below.
Book1
AB
1DataResults
21 x 4545
32 x 4545.45
43 x 4545.45.45
56 x 90 + 9990.90.90.90.90.90.99
Sheet8
Cell Formulas
RangeFormula
B2:B5B2=AutoRepeat(A2)

VBA Code:
Function AutoRepeat(S As String) As String
Dim V As Variant, T As Variant
T = WorksheetFunction.Trim(Replace(S, "x", ""))
V = Split(T, " ")
AutoRepeat = WorksheetFunction.Rept(V(1) & ".", V(0))
AutoRepeat = Left(AutoRepeat, Len(AutoRepeat) - 1)
If InStr(S, "+") > 0 Then
    AutoRepeat = AutoRepeat & "." & Right(S, Len(S) - InStrRev(S, "+") - 1)
Else
    Exit Function
End If
End Function
 
Upvote 0
Formula base solution:
In case " n x 90 + n x 99"
Book1
AB
1DataResults
21 x 4545
32 x 4545.45
43 x 4545.45.45
56 x 90 + 9990.90.90.90.90.90.99
66 x 90 + 2 x 9990.90.90.90.90.90.99.99
76 x 90 + 5 x 9990.90.90.90.90.90.99.99.99.99.99
Sheet1
Cell Formulas
RangeFormula
B2:B7B2=SUBSTITUTE(REPT(TRIM(MID(A2,FIND("x",A2)+1,3))&".",LEFT(A2,FIND("x",A2)-1))&IFERROR(REPT(TRIM(IF(ISERROR(FIND("x",A2,FIND("+",A2)+1)),MID(A2,FIND("+",A2)+1,10),MID(A2,FIND("x",A2,FIND("+",A2)+1)+1,10)))&".",IFERROR(MID(A2,FIND("+",A2)+1,FIND("x",A2,FIND("+",A2)+1)-FIND("+",A2)-1)+0,1)),"")&"|",".|","")
 
Upvote 0
I assume you're looking for a formula. This can probably be done with native worksheet functions, but for now here's a UDF you can try. Use it as you would use any worksheet function as in the example below.
Book1
AB
1DataResults
21 x 4545
32 x 4545.45
43 x 4545.45.45
56 x 90 + 9990.90.90.90.90.90.99
Sheet8
Cell Formulas
RangeFormula
B2:B5B2=AutoRepeat(A2)

VBA Code:
Function AutoRepeat(S As String) As String
Dim V As Variant, T As Variant
T = WorksheetFunction.Trim(Replace(S, "x", ""))
V = Split(T, " ")
AutoRepeat = WorksheetFunction.Rept(V(1) & ".", V(0))
AutoRepeat = Left(AutoRepeat, Len(AutoRepeat) - 1)
If InStr(S, "+") > 0 Then
    AutoRepeat = AutoRepeat & "." & Right(S, Len(S) - InStrRev(S, "+") - 1)
Else
    Exit Function
End If
End Function
This is working perfectly, this way i need to develop to deal with my daily lists. I tried to figure it myself but im to newbie to construct my own formulas yet.
So please tell me how to add to this option to write single number cells? range again is 1-99 so like 2x50 3x45 4x40 etc.. it could be just 10,25,60,50 or whatever to 99, with this UDF it will resume error value on single cell without "x, or +" , how to change that? also there is any expresion in this formula so i could change the DOT between each returned value? now its 45.45.45.45 and in the future i would like to use different marks like 45/45/45/45 or 45'45'45'45 or 45:45:45:45 etc.

also different question, how to search for empty cells in specific column and on find delete whole ROW, untill no blank cells within data range.

ex.
whole column as data range
3 x 84 + 48start here
1st detect ` delete row
4 x 23so there is no blank between
again delete
and so on till no blank cells
36
2 x 36
45 + 36
24
3 x 28 + 12
2 x 28 + 16
2 x 48
30
20 pl



With regards, Lukas
 
Upvote 0
Glad it's working for you. Here's a subroutine you can use to eliminate entire rows corresponding to empty cells in a specific column of your starting data. Once you've done that, you can select a column to enter a modification of the UDF I posted earlier. The modification returns single numbers (no "x" or "+") in a input data cell, and also allows you to choose the delimiter for application to the cells containing "x" or "+". I have changed the delimiter from "." to "/" in the example below.
Example:
Starting data is in col A
Book1
AB
1DataResult
21 x 45
32 x 45
4
5
63 x 45
76 x 90 + 99
8
9
1045 x 6
1199
12
13gbf
Sheet2

Data after running the "Delete EmptyRows Routine"
Book1
AB
1DataResult
21 x 45
32 x 45
43 x 45
56 x 90 + 99
645 x 6
799
8gbf
Sheet2

Data after entering the UDF in col B
Book1
AB
1DataResult
21 x 4545
32 x 4545/45
43 x 4545/45/45
56 x 90 + 9990/90/90/90/90/90/99
620 x 66/6/6/6/6/6/6/6/6/6/6/6/6/6/6/6/6/6/6/6
79999
8gbf#VALUE!
Sheet2
Cell Formulas
RangeFormula
B2:B8B2=AutoRepeat(A2,"/")

VBA Code:
Sub DeleteEmptyRows()
Dim R As Range, Ar As Range
On Error Resume Next
Set R = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not R Is Nothing Then
    Application.ScreenUpdating = False
    For i = R.Areas.Count To 1 Step -1
        R.Areas(i).EntireRow.Delete
    Next i
    Application.ScreenUpdating = True
End If
End Sub
Function AutoRepeat(S As String, Delim As String) As Variant
Dim V As Variant, T As Variant
If InStr(S, " x ") = 0 And InStr(S, "+") = 0 Then
    If IsNumeric(S) Then
        AutoRepeat = S
        Exit Function
    Else
        AutoRepeat = CVErr(xlErrValue)
        Exit Function
    End If
End If
T = WorksheetFunction.Trim(Replace(S, "x", ""))
V = Split(T, " ")
AutoRepeat = WorksheetFunction.Rept(V(1) & Delim, V(0))
AutoRepeat = Left(AutoRepeat, Len(AutoRepeat) - 1)
If InStr(S, "+") > 0 Then
    AutoRepeat = AutoRepeat & Delim & Right(S, Len(S) - InStrRev(S, "+") - 1)
Else
    Exit Function
End If
End Function
 
Upvote 0
Solution
hai, sorry for late answer but i had a lot of work recently plus im trying to fight with finding easier way to deal with my data.
it was working fine untill i edited with new things. I think exact example with instructions how im dealing with informations might be usefull to help with my methods and what i need to achieve. that is not really much data to manage but explaining each cell/column could be time consuming to write i down.
also few data could be sensetive to post on public, but ill try my best.
with regards,
 
Upvote 0
Hello again,


I've managed to deal with some things but now I'm struggling with something like this... Ill try to explain best I can, if something is not clear, just please point it to me and I’ll try explain it again.

This is example of my daily order list, locations are next to each other in one sheet and that's what I'm trying to automate and filter/sort to make it easier/faster.

EXAMPLE.xlsx
ABCDEFGHIJKLMNOPQRST
1
2
3Location1Location2Location3Location4Location5Location6
4Product1 181899991818
5Product22883 x 84 + 362643 x 84 + 122042 x 84 + 3691210 x 84 + 724325 x 84 + 127448 x 84 + 72
6Product3 1623 x 45 + 272886 x 45 + 182615 x 45 + 36
7Product41894 x 45 + 9
8Product5 7245 + 271082 x 45 + 181172 x 45 + 27
9Product61353 x 45
10Product73636484848483636962 x 486048 + 12
11Product866 4466
12Product9722 x 36812 x 36 + 96336 + 27902 x 36 + 18812 x 36 + 9
13Product101263 x 36 + 18
14Product118145 + 361353 x 451353 x 452525 x 45 + 272345 x 45 + 9
15Product122074 x 45 + 27
16Product1324243624 + 12723 x 243624 + 121205 x 243024 + 6
17Product14121212121212484824242424
18Product15363699 1353 x 36 + 27991173 x 36 + 9
19Product166336 + 2799 1534 x 36 + 918181173 x 36 + 9
20Product17722 x 28 + 161164 x 28 + 4923 x 28 + 81284 x 28 + 161405 x 28
21Product181445 x 28 + 4
22Product19602 x 28 + 4722 x 28 + 16843 x 281645 x 28 + 241124 x 28
23Product201244 x 28 + 12
24Product2130304230 + 12842 x 30 + 244830 + 181264 x 30 + 65430 + 24
25Product22962 x 481563 x 48 + 121202 x 48 + 243968 x 48 + 122044 x 48 + 123487 x 48 + 12
13887621 plts99826 plts116131 plts280962 plts197249 plts243954 plts
1393 mix4 mix3 mix5 mix6 mix4 mix
Verlading
Cell Formulas
RangeFormula
I138,S138,Q138,O138,M138,K138I138=SUM(I4:I137)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
F1:G1Expression=#ADR!="Blau"textNO
F1:G1Expression=#ADR!="Grün"textNO
F1:G1Expression=#ADR!="Rot"textNO
F1:G1Expression=#ADR!="Gelb"textNO
I3:I138,S3:S138,Q3:Q138,O3:O138,M3:M138,K3:K138Expression=#ADR!="grün"textNO
I3:I138,S3:S138,Q3:Q138,O3:O138,M3:M138,K3:K138Expression=#ADR!="Blau"textNO
I3:I138,S3:S138,Q3:Q138,O3:O138,M3:M138,K3:K138Expression=#ADR!="Rot"textNO
I3:I138,S3:S138,Q3:Q138,O3:O138,M3:M138,K3:K138Expression=#ADR!="Gelb"textNO
G138Cell Value<>#ADR!textNO


The list looks the same every day expect rows (that's why sum of total pallets/mixes is located at row number 138/139 (to leave space for extending the potential product changes within order (now we have total 22 products {can be more or less each day} and other rows between last product (in this example row:25) and row:138 are hidden by default.

What I'm doing every day is to separate’s each location to different sheet and make space for handwrits so I can write down products that are ready and checked by me.


It would look like this (example of locations 1;2;3)



Loc.1



EXAMPLE.xlsx
ABCDEFGHIJK
1
2
3Location1
4Product1
5Product22883 x 84 + 36
6Product3
7Product4
8Product5
9Product6
10Product73636
11Product866
12Product9722 x 36
13Product10
14Product118145 + 36
15Product12
16Product132424
17Product141212
18Product153636
19Product166336 + 27
20Product17722 x 28 + 16
21Product18
22Product19602 x 28 + 4
23Product20
24Product213030
25Product22962 x 48
1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I3:I25Expression=#ADR!="grün"textNO
I3:I25Expression=#ADR!="Blau"textNO
I3:I25Expression=#ADR!="Rot"textNO
I3:I25Expression=#ADR!="Gelb"textNO
F1:G1Expression=#ADR!="Blau"textNO
F1:G1Expression=#ADR!="Grün"textNO
F1:G1Expression=#ADR!="Rot"textNO
F1:G1Expression=#ADR!="Gelb"textNO






Loc.2



EXAMPLE.xlsx
ABCDEFGHIJK
1
2
3Location2
4Product11818
5Product22643 x 84 + 12
6Product3
7Product4
8Product5
9Product6
10Product74848
11Product8
12Product9812 x 36 + 9
13Product10
14Product111353 x 45
15Product12
16Product133624 + 12
17Product141212
18Product1599
19Product1699
20Product171164 x 28 + 4
21Product18
22Product19722 x 28 + 16
23Product20
24Product214230 + 12
25Product221563 x 48 + 12
2
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I3:I25Expression=#ADR!="grün"textNO
I3:I25Expression=#ADR!="Blau"textNO
I3:I25Expression=#ADR!="Rot"textNO
I3:I25Expression=#ADR!="Gelb"textNO
F1:G1Expression=#ADR!="Blau"textNO
F1:G1Expression=#ADR!="Grün"textNO
F1:G1Expression=#ADR!="Rot"textNO
F1:G1Expression=#ADR!="Gelb"textNO




Loc.3



EXAMPLE.xlsx
ABCDEFGHIJK
1
2
3Location3
4Product199
5Product22042 x 84 + 36
6Product31623 x 45 + 27
7Product4
8Product57245 + 27
9Product6
10Product74848
11Product844
12Product96336 + 27
13Product10
14Product111353 x 45
15Product12
16Product13723 x 24
17Product141212
18Product15
19Product16
20Product17923 x 28 + 8
21Product18
22Product19843 x 28
23Product20
24Product21842 x 30 + 24
25Product221202 x 48 + 24
3
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I3:I25Expression=#ADR!="grün"textNO
I3:I25Expression=#ADR!="Blau"textNO
I3:I25Expression=#ADR!="Rot"textNO
I3:I25Expression=#ADR!="Gelb"textNO
F1:G1Expression=#ADR!="Blau"textNO
F1:G1Expression=#ADR!="Grün"textNO
F1:G1Expression=#ADR!="Rot"textNO
F1:G1Expression=#ADR!="Gelb"textNO






As You can see each location vary with products so there are empty rows – Order from

Location 1 no products with 1;3;4;5;6;10;12;18;20

Location 2 no products with 3;4;5;6;8;10;12;18;20

Location 3 no products with 4;6;10;12;15;16;18;20

That’s why I need to make each location on its own sheet so I could use CTRL-(Minus) to delete rows without affecting other locations, which could have that product.



My whole process is something like ->>>

Split locations to different sheets -> check for empty rows and delete them-> print the list -> check products -> hand write checked product -> add into system via PC by reading from the list I just wrote -> crossdown products I just put into system (WTF???)-> check rest products with other lists, depend how order is splitted.



Ex.

Location 6 - total order pallets = 54

And that will be splitted into 33p (max per truck) and 21p

that’s gives me 2 lists:

one for 33p

second for 21p



But… customers can split orders like they want, not only as the way on example above.

Like

15p

14p

13p

12p

=54p


And that would mean I need to print 4x identical lists to check every transport separately.

So…After completing 1st list with 15p

By handwriting I have to crossdown products on 2nd list with 14p - from 1st list with (15p) I just checked then I can check the rest products…repeat same with 3rd and 4th and so on..

As You can see this whole processs is idiotic and making me re-write same data over and over again with each list.

This is happening because products from orders are checked differently each day. I have only information about how its split by amounts of pallets within the location –

In first transport (15p) I can put any product from the order, so each day will be different, because there could be a day, in which there are already made 42pallets of different products so I would be able to fill list 1,2,3 (15p,14p,13p)

and only last 12pallets of products need to be waited for.

Other day I’ll have only 9p ready so I wont be able to complete even 1st list…

etc etc



I tried to explain whole data-relation process, the best way I can and I hope it at least on ‘decent’ status of being readable…





My whole idea is to end with this stupid re-writing process and implement something different like working only on excel without printing papers at all.

My best idea so far is that, I could use Tablet instead of pen and paper. So in real-time I could change the checked product values -> then with proper formula/macro/whatever the whole data would modify.

That’s why earlier I wanted to change” 7 x 48 + 12” to 48,48,48,48,48,48,48,12 because with tablet I can just click 48,48.48.12 adding it to 1st list, then excel could automaticly generate other sheet for 2nd list but with changes making it “4 x 48

(I added already 3x pallets with 48 boxes + 12 boxes to the 1st list, so there are only 4x48 left of that specific product.

Like I mentioned, right now I would have 4x identical printed lists of location 6 with “7 x 48 + 12” of X product, and every changes is related with crossing down with pen making it less and less visible and readable.



I have my fingers crossed that You’ll be able to understand the way of my thinking and the methods of dealing with those sheets. Of course that tablet idea is just idea, if there are any better solutions to make it easier/faster to deal with data, please let me know.



With regards,
 
Upvote 0
Function AutoRepeat(S As String, Delim As String) As Variant Dim V As Variant, T As Variant If InStr(S, " x ") = 0 And InStr(S, "+") = 0 Then If IsNumeric(S) Then AutoRepeat = S Exit Function Else AutoRepeat = CVErr(xlErrValue) Exit Function End If End If T = WorksheetFunction.Trim(Replace(S, "x", "")) V = Split(T, " ") AutoRepeat = WorksheetFunction.Rept(V(1) & Delim, V(0)) AutoRepeat = Left(AutoRepeat, Len(AutoRepeat) - 1) If InStr(S, "+") > 0 Then AutoRepeat = AutoRepeat & Delim & Right(S, Len(S) - InStrRev(S, "+") - 1) Else Exit Function End If End Function

Hello again after few months, ^this works perfectly but how to add case when cells contains numbers with + only? right now its Repeating "+" as many as number before mark.
so options are:
JUST NUMBER
NUMBER + NUMBER
NUMBER X NUMBER + NUMBER

99
7 x 8484|84|84|84|84|84|84
36 + 9+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|9
3232
5 x 45 + 945|45|45|45|45|9
2 x 45 + 1845|45|18
48 + 12+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|12
5 x 36 + 2736|36|36|36|36|27
2 x 36 + 936|36|9
6 x 45 + 2745|45|45|45|45|45|27
48 + 42+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|42
1212
7 x 2828|28|28|28|28|28|28
4 x 28 + 1228|28|28|28|12
48 + 36+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|+|36
2 x 3030|30


Also how to check in the end of AutoRepeat if cell was containing"filled color" if yes, then match same color for last number (if its just one number too) like on example there are just one numbers 9 and 12.

WIth regards,
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

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