Join rows in to one cell

[Clamarc]

My excel 2016 doesn't have the "textjoin" function, so I use a custom function that I called "joinlines", where the values of several lines of a column are joined in a single cell and separated by comma+space. The problem is that if there are hidden rows they are also returned in the join result and I also need to remove the VCI- and PO- from the beginning of the records.

The values in the rows i want to join are like this:
VCI-2130/17
VCI-2134.1/17
PO-2134.2/17

In my function, I managed to join the values, but I have two problems:
1 - I don't want to join the hidden rows
2 - I don't want the values from the beginning, ex. VCI- and PO-

And I wanted to merge into a single cell like this, without the VCI- and PO- at the beginning and only on the visible rows:
2130/17, 2134.1/17, 2134.2/17

See the function below:

Public Function JOINLINES(vArray As Variant, _
Optional lSize As Long, _
Optional sSeparator As String = ", ") As Variant
Dim v As Variant
Dim lCount As Long
Dim asOut() As String
 
If lSize < 0 Then
JOINLINES = CVErr(xlErrNum)
Exit Function
End If
 
For Each v In vArray                                 '--.
If v <> "" Then                                            '| 
lCount = lCount + 1                                   '| here loads my array,
ReDim Preserve asOut(1 To lCount)           '| but i want to load [B]only[/B] 
asOut(lCount) = v                                       '| the [B]visible rows[/B] 
If lCount = lSize Then Exit For                    '| without the [B]VCI-[/B] and [B]PO-[/B]
End If                                                          '|
Next v                                                      '--.
 
JOINLINES = Join(asOut, sSeparator)
End Function

What should i change in the code to join only visible rows and the values without VCI- and PO- ?

 

[DanteAmor]

Hi @Clamarc. Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.

Try if the following solves what you need:

Public Function JOINLINES(vArray As Variant, _
      Optional lSize As Long, _
      Optional sSeparator As String = ", ") As Variant
  Dim v As Variant
  Dim lCount As Long
  Dim asOut() As String
   
  If lSize < 0 Then
    JOINLINES = CVErr(xlErrNum)
    Exit Function
  End If
   
  For Each v In vArray
    If v.EntireRow.Hidden = False Then
      If v <> "" Then
        lCount = lCount + 1
        ReDim Preserve asOut(1 To lCount)
        asOut(lCount) = Replace(Replace(v, "VCI-", "", , , vbTextCompare), "PO-", "", , , vbTextCompare)
        If lCount = lSize Then Exit For
      End If
    End If
  Next v
   
  JOINLINES = Join(asOut, sSeparator)
End Function

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[Clamarc]

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

Simply perfect!... thank you very much for your help!
Clamarc.

 

[Clamarc]

Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

Hi DanteLove
The code is working perfectly, but it only accepts values in a range A1:A12. How to merge, too, by selecting cells individually, eg. A1;A3;A4;A12...?

thanks

 

[DanteAmor]

How to merge, too, by selecting cells individually, eg. A1;A3;A4;A12...?

Put the cells or range of cells between quotes and within the quotes separated by commas, example:

Dante Amor
	A	B	C	D
1
2		VCI-2130/17		2130/17, 2134.2/17, 2134.1/18, 2130/17, 2134.1/17
3		VCI-2134.1/17
4		PO-2134.2/17
5		VCI-2130/18
6		VCI-2134.1/18
7		PO-2134.2/18
8		VCI-2130/17
9		VCI-2134.1/17
10		PO-2134.2/17
11		VCI-2130/18
12		VCI-2134.1/18
13		PO-2134.2/18
14		PO-2134.2/18
15		VCI-2130/17
16		VCI-2134.1/17
17		PO-2134.2/17
18		VCI-2130/18
Hoja10

Cell Formulas
Range	Formula
D2	D2	=JOINLINES("B8,B10,B12,B15:C18",5,", ")

Try this:

Public Function JOINLINES(vArray As Variant, _
      Optional lSize As Long, _
      Optional sSeparator As String = ", ") As Variant
  Dim v As Variant
  Dim lCount As Long
  Dim asOut() As String
  Dim rng As Range

  If lSize < 0 Then
    JOINLINES = CVErr(xlErrNum)
    Exit Function
  End If
  Set rng = Range(vArray)
  For Each v In rng
    If v.EntireRow.Hidden = False Then
      If v <> "" Then
        lCount = lCount + 1
        ReDim Preserve asOut(1 To lCount)
        asOut(lCount) = Replace(Replace(v, "VCI-", "", , , vbTextCompare), "PO-", "", , , vbTextCompare)
        If lCount = lSize Then Exit For
      End If
    End If
  Next v

  JOINLINES = Join(asOut, sSeparator)
End Function

 

[Clamarc]

Try this:

Public Function JOINLINES(vArray As Variant, _
      Optional lSize As Long, _
      Optional sSeparator As String = ", ") As Variant
  Dim v As Variant
  Dim lCount As Long
  Dim asOut() As String
  Dim rng As Range 
[/QUOTE]
It works, but do I always have to manually change the selected cells in the function bar?... if they were few lines, ok, but my file has more than 10,000 lines!

Eg. When I select the cells, the function bar shows JOINLINES(A1;A3;A12;A14:A18), so I have to edit the command and manually change it to JOINLINES("A1,A3,A12,A14:A18") so that work.

If I select 1,000 different lines I will have to manually put double quotes at the beginning and end, and then change 1,000 "[B];[/B]" to "[B],[/B]" is that it?

 

[Clamarc]

Hi, DanteAmor,

It works, but do I always have to manually change the selected cells in the function bar?... if they were few lines, ok, but my file has more than 10,000 lines!

Eg. When I select the cells, the function bar shows JOINLINES(A1;A3;A12;A14:A18), so I have to edit the command and manually change it to JOINLINES("A1,A3,A12,A14:A18") so that work.

If I select 1,000 different lines I will have to manually put double quotes at the beginning and end, and then change 1,000 ";" to "," is that it?

thanks

 

[DanteAmor]

If I select 1,000 different lines

If you have to select 1000 cells one by one , something is wrong with your process. Imagine, you go to the selection of cell 728 and you make a mistake or something happens, you must start from cell1, cell2... up to cell1000.


Forget the quotes double . What you should do is find a pattern that identifies out of the 10,000 lines which are the 1,000 lines you need, then you don't need to select one by one, the macro will identify those 1,000 lines and concatenate them.

So when you identify that pattern, you come back here, create a new post, for us to help you with a new function, of course, based on that pattern.
If you don't have a pattern, I'm afraid no function will help you avoid selecting those thousand cells one by one.

 

[Clamarc]

If you have to select 1000 cells one by one , something is wrong with your process. Imagine, you go to the selection of cell 728 and you make a mistake or something happens, you must start from cell1, cell2... up to cell1000. ...

There is no standard.

I think I didn't express myself correctly in my question.

In your example you said: "Put the cells or range of cells between quotes and within the quotes separated by commas" . When I do that, the function works correctly!...

...but if I select only a range (A14:A18), the command gives an error because I have to put the range between double quotes manually, you know?

I want two options in this function:
1 - Select individually, as you informed (Put the cells or range of cells between quotes and within the quotes separated by commas); or
2 - When I select only a range, I don't need to put double quotes (as it was in the original function).

Note: item 1 works ok, but in item 2 I have to put the double quotes manually for it to work.

 

[DanteAmor]

Try this:

Public Function JOINLINES(vArray As Variant, _
                Optional lSize As Long, _
                Optional sSeparator As String = ", ") As Variant
  Dim v As Variant
  Dim lCount As Long
  Dim asOut() As String
  Dim rng As Range

  If lSize < 0 Then
    JOINLINES = CVErr(xlErrNum)
    Exit Function
  End If
  
  On Error Resume Next
    Set rng = vArray
    If rng Is Nothing Then
      Set rng = Range(vArray)
    End If
  On Error GoTo 0
  
  For Each v In rng
    If v.EntireRow.Hidden = False Then
      If v <> "" Then
        lCount = lCount + 1
        ReDim Preserve asOut(1 To lCount)
        asOut(lCount) = Replace(Replace(v, "VCI-", "", , , vbTextCompare), "PO-", "", , , vbTextCompare)
        If lCount = lSize Then Exit For
      End If
    End If
  Next v

  JOINLINES = Join(asOut, sSeparator)
End Function

Examples:

Dante Amor
	A	B	C	D
1
2		VCI-2130/17		2130/17, 2130/18, 2130/17, 2130/17, 2134.1/17
3		VCI-2134.1/17		2134.2/17, 2130/18, 2134.1/18, 2134.2/18, 2134.2/18
4		PO-2134.2/17
5		VCI-2130/18
6		VCI-2134.1/18
7		PO-2134.2/18
8		VCI-2130/17
9		VCI-2134.1/17
10		PO-2134.2/17
11		VCI-2130/18
12		VCI-2134.1/18
13		PO-2134.2/18
14		PO-2134.2/18
15		VCI-2130/17
16		VCI-2134.1/17
17		PO-2134.2/17
18		VCI-2130/18
Hoja10

Cell Formulas
Range	Formula
D2	D2	=JOINLINES("B2,B5,B8,B15:B18",5,", ")
D3	D3	=JOINLINES(B10:B18,5,", ")