Concatenate multiple column and its heading

[gleamng]

hi everyone, below is my excel worksheet

i need help with a vba to concatenate from Column D to AY into Col AZ with some conditions
a. if a cell is 0 or blank, it should be omitted
b. Col C and D be merged with space in between
c. Col E to AY should be merged with their heanding in cell 1
d. Senicolon ";" should be seperator in between each cell value
e. Result in col AZ be formatted as text format #,##0.00
i have tried my a formula which i have in col AZ, it works but its too long and cumbersome; the need for a vba code to help. below is my excel sheet for further details

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ
1	NAME	BASIC	SOCIETY	COOP	ZEDVANCELOAN	TRACTOR	STOCKCORP	OURSMFB	KWHACOOP	KAMHOUSE	IYERUOKIN	ILUDUN-ORO	ILORINMFB	IBOLO	FMBLOAN-3	FMBLOAN-2B	FMBLOAN-2A	FMBLOAN-1C	FMBLOAN-1B	FIRSTHERITAGE	EMIRATE-CONTRIBU	DIRECT CREDIT	CONFIDENCE	B.GAMBARI	B.FULANI	APEKS	NHF	PAYE	WATER	D-LEVY	VET-LEVY	QRTRENT	PHARM	PERMSEC	PERMCOP	NUJ	NACHPN	MWAN	MUSWOW	MOSQUE	MOJ	MDCAN	KWEHO	IMAN	IGBOMINA	HLT	UNION	AMLSNN	AMELTAN	AHAPN	AAEUN	merged ded
2	SAMU ALAJO	54,613.40	CHW	25,000.00	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	709.97	669.45	150.00	8.33	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	1,092.27	-	-	-	-	CHW-25,000.00;
3	BODUNRIN ELEHA	261,859.35	CHW	82,000.00	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	2,958.58	21,788.12	200.00	8.33	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	6,827.49	-	-	-	-	CHW-82,000.00;
4	UMOBANO SARAH	261,859.35	WEH	98,000.00	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	2,958.58	21,788.12	200.00	8.33	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	6,827.49	-	-	-	-	WEH-98,000.00;
5	DARAMOYE ADENIKE	193,699.60	WEH	69,000.00	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	2,154.89	12,929.08	200.00	8.33	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	4,972.83	-	-	-	-	WEH-69,000.00;
6	SHOLA DURO	108,087.75	FIN	16,000.00	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	1,405.14	3,964.39	200.00	8.33	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	2,161.76	-	-	-	-	FIN-16,000.00;
7	AVAGADROS BUKOYE	83,223.40	EDU	17,000.00	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	1,081.90	2,621.37	200.00	8.33	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	1,664.47	-	-	-	-	EDU-17,000.00;
8	TANKPAFU EMIWOROGI	244,118.50	FIN	80,000.00	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	3,173.54	16,485.62	200.00	8.33	-	-	-	-	-	-	-	-	2,000.00	-	-	-	-	-	-	-	4,882.37	-	-	-	-	FIN-80,000.00;
9	KPADA JEBBA	847,299.85	FIN	40,000.00	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	4,236.50	95,201.92	200.00	8.33	-	-	-	-	-	-	-	2,000.00	-	-	-	11,000.00	-	-	-	-	-	-	-	-	-	FIN-40,000.00;
10	MOJOYIN KALE	105,095.50	MCI	19,000.00	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	1,366.24	3,792.41	200.00	8.33	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	2,101.91	-	-	-	-	MCI-19,000.00;
11	KOKO IGBONA	260,686.60	HPR	3,000.00	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	3,150.55	23,584.30	200.00	8.33	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	7,270.50	-	-	-	-	HPR-3,000.00;
Sheet1

Cell Formulas
Range	Formula
AZ2:AZ11	AZ2	=IF(D2<1,"",C2&"-"&TEXT(D2, "#,##0.00")&"; ")&IF(E2<1,"",$E$1&"-"&TEXT(E2, "#,##0.00")&"; ")&IF(F2<1,"",$F$1&"-"&TEXT(F2, "#,##0.00")&"; ")&IF(G2<1,"",$G$1&"-"&TEXT(G2, "#,##0.00")&"; ")&IF(H2<1,"",$H$1&"-"&TEXT(H2, "#,##0.00")&"; ")&IF(I2<1,"",$I$1&"-"&TEXT(I2, "#,##0.00")&"; ")&IF(J2<1,"",$J$1&"-"&TEXT(J2, "#,##0.00")&"; ")&IF(K2<1,"",$K$1&"-"&TEXT(K2, "#,##0.00")&"; ")&IF(L2<1,"",$L$1&"-"&TEXT(L2, "#,##0.00")&"; ")&IF(M2<1,"",$M$1&"-"&TEXT(M2, "#,##0.00")&"; ")&IF(N2<1,"",$N$1&"-"&TEXT(N2, "#,##0.00")&"; ")&IF(O2<1,"",$O$1&"-"&TEXT(O2, "#,##0.00")&"; ")&IF(P2<1,"",$P$1&"-"&TEXT(P2, "#,##0.00")&"; ")

thanking you for your help and support

 

[iggydarsa]

Sub ConcatCells()
    lr = Range("A" & Rows.Count).End(xlUp).Row
    result = ""
    For r = 2 To lr
        result = Cells(r, 3).Value & " " & Format(Cells(r, 4).Value, "#,##0.00") & ";"
        For c = 5 To 51
            If Cells(r, c).Value <> "" And Cells(r, c) >= 1 Then
                result = result & Cells(1, c).Value & "-" & Format(Cells(r, c).Value, "#,##0.00") & ";"
            End If
        Next c
        Cells(r, 52).Value = result
    Next r
End Sub

 

[gleamng]

Thanks a million, it worked exactly as I wanted.

 

[gleamng]

Thanks once again for your help, @ iggydarsa.
I run into an error (compile error: variable not defined) and lr = is highlighted once I added option explicit above the macro.
Please kindly help me on this also. Best regards.

 

[gleamng]

Thanks once again for your help, @ iggydarsa.
I run into an error (compile error: variable not defined) and lr = is highlighted once I added option explicit above the macro.
Please kindly help me on this also. Best regards.

In addition also, if any cell in column c is blank, it should not contatenate col c and d.
Thanks a lot

 

[iggydarsa]

Option Explicit

Sub ConcatCells()
    Dim lr As Long
    Dim result As String
    Dim r As Long
    Dim c As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    result = ""
    For r = 2 To lr
        If Cells(r, 3).Value <> "" Then result = Cells(r, 3).Value & " " & Format(Cells(r, 4).Value, "#,##0.00") & ";"
        For c = 5 To 51
            If Cells(r, c).Value <> "" And Cells(r, c) >= 1 Then
                result = result & Cells(1, c).Value & "-" & Format(Cells(r, c).Value, "#,##0.00") & ";"
            End If
        Next c
        Cells(r, 52).Value = result
    Next r
End Sub