I have 2 columns, 200k rows:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">F1 1
F2 0
F3 0
F4 0
F5 0
F6 1
F7 1
F8 0
F9 10</code>For all values = 0, I would like to concatenate the associated field with the previous associated field. When there are more consecutive fields (4), I would like all of the (5) relating fields to be concatenated together.
I want:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">F1 1
F2 0 F1|F2|F3|F4|F5
F3 0
F4 0
F5 0
F6 1
F7 1
F8 0 F7|F8
F9 10 </code>Currently I have:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Sub mfewj()
N = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To N
If Cells(i, 2) = 0 Then Cells(i, 3).FormulaR1C1 = "=CONCATENATE(R[-1]C[-2],""|"",RC[-2])"
Next i
End Sub</code>This returns:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">F1 1
F2 0 F1|F2
F3 0 F2|F3
F4 0 F3|F4
F5 0 F4|F5
F6 1
F7 1
F8 0 F7|F8
F9 10 </code>Any suggestions are appreciated
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">F1 1
F2 0
F3 0
F4 0
F5 0
F6 1
F7 1
F8 0
F9 10</code>For all values = 0, I would like to concatenate the associated field with the previous associated field. When there are more consecutive fields (4), I would like all of the (5) relating fields to be concatenated together.
I want:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">F1 1
F2 0 F1|F2|F3|F4|F5
F3 0
F4 0
F5 0
F6 1
F7 1
F8 0 F7|F8
F9 10 </code>Currently I have:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Sub mfewj()
N = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To N
If Cells(i, 2) = 0 Then Cells(i, 3).FormulaR1C1 = "=CONCATENATE(R[-1]C[-2],""|"",RC[-2])"
Next i
End Sub</code>This returns:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">F1 1
F2 0 F1|F2
F3 0 F2|F3
F4 0 F3|F4
F5 0 F4|F5
F6 1
F7 1
F8 0 F7|F8
F9 10 </code>Any suggestions are appreciated