Join all row cells in a single cell by vertical bar

[motilulla]

Hello,</SPAN></SPAN>

I need to Join P:Y column each row cells in single cell by using a vertical bar, result require in AA column as shown below </SPAN></SPAN>

Example</SPAN></SPAN>

Book1
	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1
2
3	n1	n2	n3	n4	n5	n6	n7	n8	n9	n10		Join
4	11	13										11 | 13
5	9	10	12	13								9 | 10 | 12
6	9	10	12									9 | 10 | 12
7	10	13										10 | 13
8	9	11	12									9 | 11 | 12
9	9	11	13									9 | 11 | 13
10	3	4	9	10								3 | 4 | 9 | 10
11	1	2	3	4	5	9						1 | 2 | 3 | 4 | 5 | 9
Sheet1

Thank you all </SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[motilulla]

@ alansidman, Adjust code ranges accordingly my data, code does not work when there are data in next to AA columns thank you for your help.</SPAN></SPAN>

@ Rick, thank you your formula results fine.</SPAN></SPAN>

@ YasserKhalil, thank you your code results as treat.</SPAN></SPAN>

@ mohadin, tried your last post#10 code, but it does not work when there are data in next to AA columns thank you for your help.</SPAN></SPAN>

Everyone have a good day</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[motilulla]

Hello,</SPAN></SPAN>

I am sorry to replant my query again need all results with 2 digits as shown in the example below...Please help</SPAN></SPAN>

Book1
	P	Q	R	S	T	U	V	W	X	Y	Z	AA
1
2
3	n1	n2	n3	n4	n5	n6	n7	n8	n9	n10		Join
4	11	13										11|13
5	9	10	12	13								09|10|12|13
6	9	10	12									09|10|12
7	10	13										10|13
8	9	11	12									09|11|12
9	9	11	13									09|11|13
10	3	4	9	10								03|04|09|10
11	1	2	3	4	5	9						01|02|030|04|05|09
Sheet1

Thank you all </SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[Rick Rothstein]

I am sorry to replant my query again need all results with 2 digits as shown in the example below...Please help[/COLOR]</SPAN></SPAN>

Here is a formula solution...

[table="width: 500"]
[tr]
	[td]=SUBSTITUTE(TRIM(IF(A2="","",TEXT(A2,"00 "))&IF(B2="","",TEXT(B2,"00 "))&IF(C2="","",TEXT(C2,"00 "))&IF(D2="","",TEXT(D2,"00 "))&IF(E2="","",TEXT(E2,"00 "))&IF(F2="","",TEXT(F2,"00 "))&IF(G2="","",TEXT(G2,"00 "))&IF(H2="","",TEXT(H2,"00 "))&IF(I2="","",TEXT(I2,"00 "))&IF(J2="","",TEXT(J2,"00 ")))," ","|")[/td]
[/tr]
[/table]

 

[motilulla]

Here is a formula solution...

[TABLE="width: 500"]
<TBODY>[TR]
[TD]=SUBSTITUTE(TRIM(IF(A2="","",TEXT(A2,"00 "))&IF(B2="","",TEXT(B2,"00 "))&IF(C2="","",TEXT(C2,"00 "))&IF(D2="","",TEXT(D2,"00 "))&IF(E2="","",TEXT(E2,"00 "))&IF(F2="","",TEXT(F2,"00 "))&IF(G2="","",TEXT(G2,"00 "))&IF(H2="","",TEXT(H2,"00 "))&IF(I2="","",TEXT(I2,"00 "))&IF(J2="","",TEXT(J2,"00 ")))," ","|")
[/TD]
[/TR]
</TBODY>[/TABLE]

Rick, it is absolutely perfect! I appreciate your help</SPAN></SPAN>

Have a nice day</SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>

Note: I am observing in the example got result error in the AA11, which formula did correct

 

[mohadin]

Hi again
what about

Sub test2()
    Dim b, c As Variant, x
    Dim lr, lc, i
    Range("aa4:aa11").ClearContents
    Range("aa4:aa11").Formula = "=COUNTA(p4:y4)"
    c = Application.Transpose(Range("aa4:aa11").Value)
    For i = 4 To Cells(Rows.Count, 16).End(xlUp).Row
        b = Application.Transpose(WorksheetFunction.Transpose(Cells(i, 16).Resize(, c(i - 3))))
        b = "0" & Join(b, "|0")
        Cells(i, 27) = b
    Next
End Sub

 

[motilulla]

Hi again
what about

Sub test2()
    Dim b, c As Variant, x
    Dim lr, lc, i
    Range("aa4:aa11").ClearContents
    Range("aa4:aa11").Formula = "=COUNTA(p4:y4)"
    c = Application.Transpose(Range("aa4:aa11").Value)
    For i = 4 To Cells(Rows.Count, 16).End(xlUp).Row
        b = Application.Transpose(WorksheetFunction.Transpose(Cells(i, 16).Resize(, c(i - 3))))
        b = "0" & Join(b, "|0")
        Cells(i, 27) = b
    Next
End Sub

Hello mohadin, Please can you test your code I think you have to check it is adding extra "0" in the each value it has to add only where is one digit number to make them 2 digit it is converting 2 digit in to 3 digit </SPAN></SPAN>

Kind Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>