concatenating data

[Dummy Excel]

I have some data where row 1 is my header row, then row 2 is my store number and then i have the following rows are the purchase orders for that month. This number of rows can change each month. Then straight after that I have my next store number, and then again i have the following rows as the purchase
orders for that month, This number can change each month.

Im trying to concatenate the store number with the purchase order number to be like this xxx-xxxxx-D.

my formula is

"=IF(LEN(RC[-1])=4, R2C1&""-0""&RC[-1]&""-D"", R2C1&""-""&RC[-1]&""-D"")"

the problem I have is when I get to the next store number it still shows cell A2 as I have made it an absolute
reference.

My code so far is below, if there is an easier or quicker way please let me know

Sub Macro1()

    Columns("B:B").Insert Shift:=xlToRight
    Range("B3").FormulaR1C1 = _
        "=IF(LEN(RC[-1])=4, R2C1&""-0""&RC[-1]&""-D"", R2C1&""-""&RC[-1]&""-D"")"
    Range("B3").Copy
    Range("C3").End(xlDown).Offset(0, -1).Select
    Range(Selection, Selection.End(xlUp)).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
    Selection.End(xlDown).Offset(2, 0).FormulaR1C1 = _
        "=IF(LEN(RC[-1])=4, R2C1&""-0""&RC[-1]&""-D"", R2C1&""-""&RC[-1]&""-D"")"
end sub

here is an example of my spreadsheet

Excel Workbook
	A	B	C	D
1	PO #	ORIGINAL ORDER QUANTITY	EFFECTIVE ORDER QUANTITY	QUANTITY ON TIME
2	117	*	*	*
3	3917	1686	1686	0
4	6747	762	762	762
5	416	*	*	*
6	54335	1785	1785	1755
7	54406	876	876	0
8	54640	1068	1068	0
9	54641	924	924	0
10	54666	267	267	0
11	432	*	*	*
12	12932	2415	2415	2415
13	435	*	*	*
14	28552	4116	4116	4116
15	38513	2179	2179	1185
PO Summary

so 117, 416, 432, 435 are all store numbers and 3917 is a purchase order for example. So what I need is 117-03917-D - the purchase order needs to be 5 digits long. All the * are blank cells.

Any help will be greatly appreciated
thanks
Sam

 

[hiker95]

Dummy Excel,


Something like this?


Sample raw data:

Excel Workbook
	A	B	C	D
1	PO #	ORIGINAL ORDER QUANTITY	EFFECTIVE ORDER QUANTITY	QUANTITY ON TIME
2	117
3	3917	1686	1686	0
4	6747	762	762	762
5	416
6	54335	1785	1785	1755
7	54406	876	876	0
8	54640	1068	1068	0
9	54641	924	924	0
10	54666	267	267	0
11	432
12	12932	2415	2415	2415
13	435
14	28552	4116	4116	4116
15	38513	2179	2179	1185
16	432
17	1	2415	2415	2415
18	432
19	12	2415	2415	2415
20	432
21	129	2415	2415	2415
22	432
23	1293	2415	2415	2415
24	432
25	12932	2415	2415	2415
26
PO Summary

After the macro:

Excel Workbook
	A	B	C	D
1	PO #	ORIGINAL ORDER QUANTITY	EFFECTIVE ORDER QUANTITY	QUANTITY ON TIME
2	117
3	117-03917-D	1686	1686	0
4	117-06747-D	762	762	762
5	416
6	416-54335-D	1785	1785	1755
7	416-54406-D	876	876	0
8	416-54640-D	1068	1068	0
9	416-54641-D	924	924	0
10	416-54666-D	267	267	0
11	432
12	432-12932-D	2415	2415	2415
13	435
14	435-28552-D	4116	4116	4116
15	435-38513-D	2179	2179	1185
16	432
17	432-00001-D	2415	2415	2415
18	432
19	432-00012-D	2415	2415	2415
20	432
21	432-00129-D	2415	2415	2415
22	432
23	432-01293-D	2415	2415	2415
24	432
25	432-12932-D	2415	2415	2415
26
PO Summary

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ConcatStoreNbrPONbr()
' hiker95, 08/15/2011
' http://www.mrexcel.com/forum/showthread.php?t=571984
Dim Area As Range, SR As Long, ER As Long, a As Long, H As String
Application.ScreenUpdating = False
Worksheets("PO Summary").Activate
For Each Area In Range("B2", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  With Area
    SR = .Row
    ER = SR + .Rows.Count - 1
    If SR = ER Then
      H = ""
      H = Range("A" & SR - 1) & "-"
      Select Case Len(Range("A" & SR))
        Case 1
          H = H & "0000" & Range("A" & SR) & "-D"
        Case 2
          H = H & "000" & Range("A" & SR) & "-D"
        Case 3
          H = H & "00" & Range("A" & SR) & "-D"
        Case 4
          H = H & "0" & Range("A" & SR) & "-D"
        Case 5
          H = H & Range("A" & SR) & "-D"
      End Select
      Range("A" & SR) = H
    Else
      For a = SR To ER Step 1
        H = ""
        H = Range("A" & SR - 1) & "-"
        Select Case Len(Range("A" & a))
          Case 1
            H = H & "0000" & Range("A" & a) & "-D"
          Case 2
            H = H & "000" & Range("A" & a) & "-D"
          Case 3
            H = H & "00" & Range("A" & a) & "-D"
          Case 4
            H = H & "0" & Range("A" & a) & "-D"
          Case 5
            H = H & Range("A" & a) & "-D"
        End Select
        Range("A" & a) = H
      Next a
    End If
  End With
Next Area
Columns(1).AutoFit
Application.ScreenUpdating = True
End Sub

Then run the ConcatStoreNbrPONbr macro.

 

[Rick Rothstein]

I think this code does what you want...

Sub CreateStorePOs()
  Dim X As Long, LastRow As Long, Store As String
  LastRow = Cells(Rows.Count, "A").End(xlUp).Row
  Columns("B").Insert
  Columns("C").SpecialCells(xlCellTypeBlanks).Offset(, -1).FormulaR1C1 = "=RC[-1]"
  For X = 2 To LastRow
    If Len(Cells(X, "B").Value) Then
      Store = Cells(X, "B").Value
    Else
      Cells(X, "B").Value = Store & "-" & Format(Cells(X, "B").Offset(, -1).Value, "00000") & "-D"
    End If
  Next
  Columns("B").SpecialCells(xlCellTypeFormulas).Clear
End Sub

This is what it outputs for the data you posted...

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 52px"><COL style="WIDTH: 102px"><COL style="WIDTH: 79px"><COL style="WIDTH: 79px"><COL style="WIDTH: 79px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 54px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>PO #</TD><TD> </TD><TD>ORIGINAL ORDER QUANTITY</TD><TD>EFFECTIVE ORDER QUANTITY</TD><TD>QUANTITY ON TIME</TD></TR><TR style="HEIGHT: 25px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">117</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 25px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">3917</TD><TD style="TEXT-ALIGN: center">117-03917-D</TD><TD style="TEXT-ALIGN: right">1686</TD><TD style="TEXT-ALIGN: right">1686</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 25px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">6747</TD><TD style="TEXT-ALIGN: center">117-06747-D</TD><TD style="TEXT-ALIGN: right">762</TD><TD style="TEXT-ALIGN: right">762</TD><TD style="TEXT-ALIGN: right">762</TD></TR><TR style="HEIGHT: 25px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">416</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 25px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">54335</TD><TD style="TEXT-ALIGN: center">416-54335-D</TD><TD style="TEXT-ALIGN: right">1785</TD><TD style="TEXT-ALIGN: right">1785</TD><TD style="TEXT-ALIGN: right">1755</TD></TR><TR style="HEIGHT: 25px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">54406</TD><TD style="TEXT-ALIGN: center">416-54406-D</TD><TD style="TEXT-ALIGN: right">876</TD><TD style="TEXT-ALIGN: right">876</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 25px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">54640</TD><TD style="TEXT-ALIGN: center">416-54640-D</TD><TD style="TEXT-ALIGN: right">1068</TD><TD style="TEXT-ALIGN: right">1068</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 25px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">54641</TD><TD style="TEXT-ALIGN: center">416-54641-D</TD><TD style="TEXT-ALIGN: right">924</TD><TD style="TEXT-ALIGN: right">924</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 25px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right">54666</TD><TD style="TEXT-ALIGN: center">416-54666-D</TD><TD style="TEXT-ALIGN: right">267</TD><TD style="TEXT-ALIGN: right">267</TD><TD style="TEXT-ALIGN: right">0</TD></TR><TR style="HEIGHT: 25px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right">432</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 25px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: right">12932</TD><TD style="TEXT-ALIGN: center">432-12932-D</TD><TD style="TEXT-ALIGN: right">2415</TD><TD style="TEXT-ALIGN: right">2415</TD><TD style="TEXT-ALIGN: right">2415</TD></TR><TR style="HEIGHT: 25px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: right">435</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 25px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: right">28552</TD><TD style="TEXT-ALIGN: center">435-28552-D</TD><TD style="TEXT-ALIGN: right">4116</TD><TD style="TEXT-ALIGN: right">4116</TD><TD style="TEXT-ALIGN: right">4116</TD></TR><TR style="HEIGHT: 25px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: right">38513</TD><TD style="TEXT-ALIGN: center">435-38513-D</TD><TD style="TEXT-ALIGN: right">2179</TD><TD style="TEXT-ALIGN: right">2179</TD><TD style="TEXT-ALIGN: right">1185</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

 

[hiker95]

Dummy Excel,


Same screenshots as above, but the new code should run faster.


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub ConcatStoreNbrPONbrV2()
' hiker95, 08/15/2011
' http://www.mrexcel.com/forum/showthread.php?t=571984
Dim Area As Range, SR As Long, ER As Long, a As Long, H As String, Z As String
Application.ScreenUpdating = False
Worksheets("PO Summary").Activate
For Each Area In Range("B2", Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  With Area
    SR = .Row
    ER = SR + .Rows.Count - 1
    If SR = ER Then
      H = ""
      H = Range("A" & SR - 1) & "-"
      Z = Application.Rept("0", 5 - Len(Range("A" & SR)))
      H = H & Z & Range("A" & SR) & "-D"
      Range("A" & SR) = H
    Else
      For a = SR To ER Step 1
        H = ""
        H = Range("A" & SR - 1) & "-"
        Z = Application.Rept("0", 5 - Len(Range("A" & a)))
        H = H & Z & Range("A" & a) & "-D"
        Range("A" & a) = H
      Next a
    End If
  End With
Next Area
Columns(1).AutoFit
Application.ScreenUpdating = True
End Sub

Then run the ConcatStoreNbrPONbrV2 macro.

 

[hiker95]

Dummy Excel,


Oops - I missed something.


After the latest macro:

Excel Workbook
	A	B	C	D	E
1	PO #		ORIGINAL ORDER QUANTITY	EFFECTIVE ORDER QUANTITY	QUANTITY ON TIME
2	117
3	3917	117-03917-D	1686	1686	0
4	6747	117-06747-D	762	762	762
5	416
6	54335	416-54335-D	1785	1785	1755
7	54406	416-54406-D	876	876	0
8	54640	416-54640-D	1068	1068	0
9	54641	416-54641-D	924	924	0
10	54666	416-54666-D	267	267	0
11	432
12	12932	432-12932-D	2415	2415	2415
13	435
14	28552	435-28552-D	4116	4116	4116
15	38513	435-38513-D	2179	2179	1185
16
PO Summary

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub ConcatStoreNbrPONbrV3()
' hiker95, 08/15/2011
' http://www.mrexcel.com/forum/showthread.php?t=571984
Dim Area As Range, SR As Long, ER As Long, a As Long, H As String, Z As String
Application.ScreenUpdating = False
Worksheets("PO Summary").Activate
Columns(2).Insert
For Each Area In Range("C2", Range("C" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  With Area
    SR = .Row
    ER = SR + .Rows.Count - 1
    If SR = ER Then
      H = ""
      H = Range("A" & SR - 1) & "-"
      Z = Application.Rept("0", 5 - Len(Range("A" & SR)))
      H = H & Z & Range("A" & SR) & "-D"
      Range("B" & SR) = H
    Else
      For a = SR To ER Step 1
        H = ""
        H = Range("A" & SR - 1) & "-"
        Z = Application.Rept("0", 5 - Len(Range("A" & a)))
        H = H & Z & Range("A" & a) & "-D"
        Range("B" & a) = H
      Next a
    End If
  End With
Next Area
Columns(2).AutoFit
Application.ScreenUpdating = True
End Sub

Then run the ConcatStoreNbrPONbrV3 macro.

 

[Peter_SSs]

Another variation ..

<font face=Courier New><br><br><SPAN style="color:#00007F">Sub</SPAN> StoreOrders()<br>    <SPAN style="color:#00007F">Dim</SPAN> Store <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> a <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    <SPAN style="color:#00007F">Const</SPAN> f <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "=""#""&TEXT(RC[-1],""-00000-"")&""D"""<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    Columns("B").Insert<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> a <SPAN style="color:#00007F">In</SPAN> Columns("C").SpecialCells(xlCellTypeConstants, xlNumbers).Areas<br>        <SPAN style="color:#00007F">With</SPAN> a.Offset(, -1)<br>            Store = .Cells(1, 1).Offset(-1, -1).Value<br>            .FormulaR1C1 = Replace(f, "#", Store)<br>            .Value = .Value<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> a<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Dummy Excel]

both ways works great and fast.
hiker, i can see the difference in code although in terms of faster, they are just as quick as each other

really appreciate your help with this

 

[hiker95]

Dummy Excel,

You are very welcome.

Glad I could help.

Thanks for the feedback.

Come back anytime.

hiker, i can see the difference in code although in terms of faster, they are just as quick as each other

I am sorry, I was comparing my first marco using Select Case, and my third macro.


I am learning how to code using arrays in memory, instead of working directly in worksheets.

I tested the ConcatStoreNbrPONbrV3 macro against my latest macro ConcatStoreNbrPONbrV4 (below) against 30,000+ rows of data.

ConcatStoreNbrPONbrV3 took 7 seconds, while the below macro code ConcatStoreNbrPONbrV4 took zero seconds.




Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub ConcatStoreNbrPONbrV4()
' hiker95, 08/15/2011
' http://www.mrexcel.com/forum/showthread.php?t=571984
Dim A(), i As Long, H As String, Z As String, S As String
Worksheets("PO Summary").Activate
Columns(2).Insert
A = Range("A1:C" & Cells(Rows.Count, 1).End(xlUp).Row)
For i = 2 To UBound(A)
  If A(i, 3) = "" Then
    S = A(i, 1) & "-"
  Else
    H = ""
    Z = Application.Rept("0", 5 - Len(A(i, 1)))
    H = H & Z & A(i, 1) & "-D"
    A(i, 2) = S & H
  End If
Next i
Range("A1:C" & UBound(A)) = A
Columns(2).AutoFit
End Sub

Then run the ConcatStoreNbrPONbrV4 macro.

 

[Peter_SSs]

... ConcatStoreNbrPONbrV4 took zero seconds.

Measuring down to tenths of seconds, that code took about 0.4 seconds for me with about 30,0000 rows. This slight change halved the run time to abou 0.2 seconds.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ConcatStoreNbrPONbrV5()<br>    <SPAN style="color:#00007F">Dim</SPAN> A(), i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, H <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Z <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, S <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br> <br>    Worksheets("PO Summary").Activate<br>    Columns(2).Insert<br>    A = Range("A1:C" & Cells(Rows.Count, 1).End(xlUp).Row)<br>    <SPAN style="color:#00007F">For</SPAN> i = 2 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(A)<br>      <SPAN style="color:#00007F">If</SPAN> A(i, 3) = "" <SPAN style="color:#00007F">Then</SPAN><br>        S = A(i, 1) & "-"<br>      <SPAN style="color:#00007F">Else</SPAN><br>        A(i, 2) = S & Right("00000" & A(i, 1), 5) & "-D"<br>      <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    Range("A1:C" & <SPAN style="color:#00007F">UBound</SPAN>(A)) = A<br>    Columns(2).AutoFit<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[hiker95]

Peter,

Thank you very much.