Combine cells value with the header and ignore the blanks

[sahil86]

Hi All

Please if someone can help to achieve the desired result from complex Excel data. I need to combine the cell value with the header and ignore the blank cells.

Please advise if it is possible. Thank you for your time.

Number	RESULT REQUIRED	First Name	Second Name	Tel	Email
1​	First Name: ABC - Tel: 123456789	ABC		123456789​
2​	Tel: 123456789			123456789​
3​	Second Name: ABD - Email: @@@		ABC	123456789​	@@@

 

[Ezguy4u]

Well let's get the ball rolling. This program delivers 99% of what you want. The only thing missing is that - between Results. Anyway what usually happens now is the A Students will weigh in and do the same with with 3 lines of code. This should at least get the discussion goiong.

Sub Join1()

Dim Row1 As Long
Dim LastRow As Long
Dim LStr As String
Dim Str1 As String
Dim Str2 As String
Dim Str3 As String
Dim Str4 As String

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

For Row1 = 2 To LastRow

If Cells(Row1, 3) <> "" Then
Str1 = "First Name: " & Cells(Row1, 3).Value & "  "
Else
Str1 = ""
End If

If Cells(Row1, 4) <> "" Then
Str2 = "Second Name: " & Cells(Row1, 4).Value & "  "
Else
Str2 = ""
End If

If Cells(Row1, 5) <> "" Then
Str3 = "Tel: " & Cells(Row1, 5).Value & "  "
Else
Str3 = ""
End If
If Cells(Row1, 6) <> "" Then
Str4 = "Email: " & Cells(Row1, 6).Value & "  "
Else
Str4 = ""
End If

Range("B" & Row1).Value = Str1 & Str2 & Str3 & Str4
Next Row1

End Sub

20-11-20.xlsm
	A	B	C	D	E	F
1	Number	RESULT REQUIRED	First Name	Second Name	Tel	Email
2	1	First Name: ABC Tel: 123456789	ABC		123456789
3	2	Tel: 123456789			123456789
4	3	Second Name: ABD Tel: 123456789 Email: @@@		ABD	123456789	@@@
Join

 

[CA_Punit]

You can also try this, if formula suits you

Book1
	C	D	E	F	G	H
4	Number	RESULT REQUIRED	First Name	Second Name	Tel	Email
5	1	First Name: ABC Tel: 123456789	ABC		123456789
6	2	Tel: 123456789			123456789
7	3	Second Name: ABD Tel: 123456789 Email: @@@		ABD	123456789	@@@
Sheet1

Cell Formulas
Range	Formula
E5:H7	E5	=IFERROR(MID(MID($D5,SEARCH(E$4,$D5)+LEN(E$4)+2,100),1,IFERROR(SEARCH(" ",MID($D5,SEARCH(E$4,$D5)+LEN(E$4)+2,100)),100)),"")

 

[Fluff]

Another option as a UDF

Function sahil(Rng As Range, HdrRw As Long) As String
   Dim Cl As Range
  
   For Each Cl In Rng
      If Cl <> "" Then sahil = sahil & " - " & Cells(HdrRw, Cl.Column) & ": " & Cl.Value
   Next Cl
   sahil = Mid(sahil, 4)
End Function

Used like

+Fluff v2.xlsm
	A	B	C	D	E	F
1	Number	RESULT REQUIRED	First Name	Second Name	Tel	Email
2	1	First Name: ABC - Tel: 123456789	ABC		123456789
3	2	Tel: 123456789			123456789
4	3	Second Name: ABC - Tel: 123456789 - Email: @@@		ABC	123456789	@@@
5
Master

Cell Formulas
Range	Formula
B2:B4	B2	=sahil(C2:F2,1)

Where the 2nd argument is the row number for the headers.

@CA_Punit
I think the OP wants it the other way round.

 

[Ezguy4u]

Fluff Your UserFunction works great. I am going to add it to my library.

 

[Peter_SSs]

Welcome to the MrExcel board!

A couple more options.
- Rows 2-6 using standard worksheet functions available in your Excel 2013
- Rows 9-13 using the following user-defined function. Post back if you are interested in using a udf and need help with how to implement those.

Function Combine(rData As Range, rHdrs As Range) As String
  Combine = RTrim(Join(Filter(Split(Join(Evaluate(rHdrs.Address & "&"": ""&" & rData.Address), " |") & " ", "|"), ":  ", False), "- "))
End Function

sahil86.xlsm
	A	B	C	D	E	F
1	Number	RESULT REQUIRED	First Name	Second Name	Tel	Email
2	1	First Name: ABC - Tel: 123456789	ABC		123456789
3	2	Tel: 123456789			123456789
4	3	Second Name: ABC - Tel: 123456789 - Email: @@@		ABC	123456789	@@@
5	4	First Name: ABC - Email: email1	ABC			email1
6	5	First Name: Tom - Second Name: Hill - Tel: 56-789 - Email: email2	Tom	Hill	56-789	email2
7
8	Number	RESULT REQUIRED	First Name	Second Name	Tel	Email
9	1	First Name: ABC - Tel: 123456789	ABC		123456789
10	2	Tel: 123456789			123456789
11	3	Second Name: ABC - Tel: 123456789 - Email: @@@		ABC	123456789	@@@
12	4	First Name: ABC - Email: email1	ABC			email1
13	5	First Name: Tom - Second Name: Hill - Tel: 56-789 - Email: email2	Tom	Hill	56-789	email2
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=MID(IF(C2="",""," - "&C$1&": "&C2)&IF(D2="",""," - "&D$1&": "&D2)&IF(E2="",""," - "&E$1&": "&E2)&IF(F2="",""," - "&F$1&": "&F2),4,999)
B9:B13	B9	=Combine(C9:F9,C$8:F$8)

 

[Rick Rothstein]

For those who might be interested, here is Fluff's UDF as a one-liner...

Function sahil(Rng As Range, HR As Long) As String
  sahil = Mid(Join(Evaluate(Replace(Replace("IF(@="""","""","" - ""&#&"": ""&@)", "@", Rng.Address), "#", Intersect(Rows(HR), Rng.EntireColumn).Address)), ""), 4)
End Function

 

[Peter_SSs]

For those who might be interested, here is Fluff's UDF as a one-liner..

That one-liner could be reduced (ie characters) a tiny bit further ..

Function sahil(Rng As Range, HR As Long) As String
  sahil = Mid(Join(Evaluate(Replace(Replace("IF(@="""","""","" - ""&#&"": ""&@)", "@", Rng.Address), "#", Rng.Offset(HR - Rng.Row).Address)), ""), 4)
End Function

Just a further comment that if any form of this udf is used, I would recommend using like below so that if any rows are inserted/deleted above the header row the udf would automatically adjust that header row argument on recalculation.

=sahil(C2:F2,ROW(C$1))

 

[sahil86]

THANK YOU EVERYONE YOU GUYZ ARE AWESOME. @ Fluffs UDF WORKS GREAT AND ALREADY APPRECIATED BY EASYGUY4U & OTHERS.


God Bless you everyone ?????

 

[Fluff]

Glad we could help & thanks for the feedback.