Excel Formula required (text)

V

vishan_rana

New Member
Joined
Feb 22, 2012
Messages
20
Hi All,

I want help to get a formula in excel. I have a list of mobile no.s and next column of this it's Gift.

I want formula that when a mobile repeat or select more than 1 gift then formula merge it separate via "," in next column

Data is like below.

<table border="0" cellpadding="0" cellspacing="0" width="592"><colgroup><col style="mso-width-source:userset;mso-width-alt:3181;width:65pt" width="87"> <col style="mso-width-source:userset;mso-width-alt:2816; width:58pt" span="2" width="77"> <col style="mso-width-source:userset;mso-width-alt:9362;width:192pt" width="256"> <col style="mso-width-source:userset;mso-width-alt:3474;width:71pt" width="95"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;width:65pt" height="17" width="87"> <table border="0" cellpadding="0" cellspacing="0" width="482"><colgroup><col style="mso-width-source:userset;mso-width-alt:2816;width:58pt" width="77"> <col style="mso-width-source:userset;mso-width-alt:12470;width:256pt" width="341"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:58pt" height="17" width="77">MSISDN</td> <td style="width:256pt" width="341">GIFT Name</td> <td style="width:48pt" width="64">Formula</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">8527460456</td> <td>Yamaha crux Whirlpool washing machine gents wallet</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">8527460456</td> <td>Yamaha Crux</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">8527460456</td> <td>Whirlpool washing machine</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">8527460456</td> <td>Whirlpool washing machine</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">8527460456</td> <td>DVD PLAYER</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">8527460456</td> <td>Gents Wallet</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">8527461399</td> <td>WHIRLPOOL 6.5 KG SEMI AUTO WASHING MACHINE</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">8527461399</td> <td>PANASONIC PRINTER-SCANNER-PHOTOCOPIER</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">8800659379</td> <td>LAPU</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">8800659379</td> <td>LAPU</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">8800852392</td> <td>BAJAJCEILINGFAN INALSA TEAMAKER KETTLE</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">8800852392</td> <td>(BAJAJ CEILING FAN AND INALSA TEA MAKER ELECTRIC KETTLE)</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">8826661468</td> <td>LAPU</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">8826661468</td> <td>LAPU</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">9560126160</td> <td>Panasonic DVD player</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">9560126160</td> <td>Panasonic DVD player</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">9560126160</td> <td>Panasonic DVD player</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">9560126160</td> <td>inalsa pop-up toaster</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">9560852871</td> <td>yamaha crux</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">9560852871</td> <td>panasonic 1 ton split ac</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">9560852873</td> <td>whirlpool Refigirater 230 Lt.</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">9560852873</td> <td>Panasonic 1.5 ton sprit AC</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">9560852873</td> <td>Maruti Alto</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">9717400181</td> <td>panasonicsdcamcorder sonyplaystation2</td> <td>
</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">9717400181</td> <td>Panasonicsdcardcamcorder Sonyplaystation2</td> <td>
</td> </tr> </tbody></table></td><td class="xl65" style="border-left:none;width:58pt" width="77">
</td><td class="xl65" style="border-left:none;width:58pt" width="77">
</td><td class="xl68" style="border-left:none;width:192pt" width="256">
</td><td class="xl70" style="width:71pt" width="95">
</td></tr><tr style="height:25.5pt" height="34"><td class="xl66" style="height:25.5pt; border-top:none;width:65pt" align="right" height="34" width="87">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="right" width="77">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="center" width="77">
</td><td class="xl69" style="border-top:none;border-left:none;width:192pt" width="256">
</td><td class="xl72" style="border-top:none;width:71pt" width="95">
</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt; border-top:none;width:65pt" align="right" height="17" width="87">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="right" width="77">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="center" width="77">
</td><td class="xl69" style="border-top:none;border-left:none;width:192pt" width="256">
</td><td class="xl72" style="border-top:none;width:71pt" width="95">
</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt; border-top:none;width:65pt" align="right" height="17" width="87">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="right" width="77">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="center" width="77">
</td><td class="xl69" style="border-top:none;border-left:none;width:192pt" width="256">
</td><td class="xl72" style="border-top:none;width:71pt" width="95">
</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt; border-top:none;width:65pt" align="right" height="17" width="87">Pls help
Regards
vishan Rana
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="right" width="77">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="center" width="77">
</td><td class="xl69" style="border-top:none;border-left:none;width:192pt" width="256">
</td><td class="xl72" style="border-top:none;width:71pt" width="95">
</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt; border-top:none;width:65pt" align="right" height="17" width="87">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="right" width="77">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="center" width="77">
</td><td class="xl69" style="border-top:none;border-left:none;width:192pt" width="256">
</td><td class="xl72" style="border-top:none;width:71pt" width="95">
</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt; border-top:none;width:65pt" align="right" height="17" width="87">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="right" width="77">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="center" width="77">
</td><td class="xl69" style="border-top:none;border-left:none;width:192pt" width="256">
</td><td class="xl72" style="border-top:none;width:71pt" width="95">
</td></tr><tr style="height:25.5pt" height="34"><td class="xl67" style="height:25.5pt; border-top:none;width:65pt" align="right" height="34" width="87">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="right" width="77">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="center" width="77">
</td><td class="xl69" style="border-top:none;border-left:none;width:192pt" width="256">
</td><td class="xl72" style="border-top:none;width:71pt" width="95">
</td></tr><tr style="height:25.5pt" height="34"><td class="xl66" style="height:25.5pt; border-top:none;width:65pt" align="right" height="34" width="87">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="right" width="77">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="center" width="77">
</td><td class="xl69" style="border-top:none;border-left:none;width:192pt" width="256">
</td><td class="xl72" style="border-top:none;width:71pt" width="95">
</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt; border-top:none;width:65pt" align="right" height="17" width="87">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="right" width="77">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="center" width="77">
</td><td class="xl69" style="border-top:none;border-left:none;width:192pt" width="256">
</td><td class="xl72" style="border-top:none;width:71pt" width="95">
</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt; border-top:none;width:65pt" align="right" height="17" width="87">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="right" width="77">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="center" width="77">
</td><td class="xl69" style="border-top:none;border-left:none;width:192pt" width="256">
</td><td class="xl72" style="border-top:none;width:71pt" width="95">
</td></tr><tr style="height:25.5pt" height="34"><td class="xl66" style="height:25.5pt; border-top:none;width:65pt" align="right" height="34" width="87">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="right" width="77">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="center" width="77">
</td><td class="xl69" style="border-top:none;border-left:none;width:192pt" width="256">
</td><td class="xl72" style="border-top:none;width:71pt" width="95">
</td></tr><tr style="height:25.5pt" height="34"><td class="xl66" style="height:25.5pt; border-top:none;width:65pt" align="right" height="34" width="87">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="right" width="77">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="center" width="77">
</td><td class="xl69" style="border-top:none;border-left:none;width:192pt" width="256">
</td><td class="xl72" style="border-top:none;width:71pt" width="95">
</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt; border-top:none;width:65pt" align="right" height="17" width="87">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="right" width="77">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="center" width="77">
</td><td class="xl69" style="border-top:none;border-left:none;width:192pt" width="256">
</td><td class="xl72" style="border-top:none;width:71pt" width="95">
</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt; border-top:none;width:65pt" align="right" height="17" width="87">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="right" width="77">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="center" width="77">
</td><td class="xl69" style="border-top:none;border-left:none;width:192pt" width="256">
</td><td class="xl72" style="border-top:none;width:71pt" width="95">
</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt; border-top:none;width:65pt" align="right" height="17" width="87">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="right" width="77">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="center" width="77">
</td><td class="xl69" style="border-top:none;border-left:none;width:192pt" width="256">
</td><td class="xl72" style="border-top:none;width:71pt" width="95">
</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt; border-top:none;width:65pt" align="right" height="17" width="87">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="right" width="77">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="center" width="77">
</td><td class="xl69" style="border-top:none;border-left:none;width:192pt" width="256">
</td><td class="xl72" style="border-top:none;width:71pt" width="95">
</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt; border-top:none;width:65pt" align="right" height="17" width="87">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="right" width="77">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="center" width="77">
</td><td class="xl69" style="border-top:none;border-left:none;width:192pt" width="256">
</td><td class="xl72" style="border-top:none;width:71pt" width="95">
</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt; border-top:none;width:65pt" align="right" height="17" width="87">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="right" width="77">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="center" width="77">
</td><td class="xl69" style="border-top:none;border-left:none;width:192pt" width="256">
</td><td class="xl72" style="border-top:none;width:71pt" width="95">
</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt; border-top:none;width:65pt" align="right" height="17" width="87">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="right" width="77">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="center" width="77">
</td><td class="xl69" style="border-top:none;border-left:none;width:192pt" width="256">
</td><td class="xl72" style="border-top:none;width:71pt" width="95">
</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt; border-top:none;width:65pt" align="right" height="17" width="87">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="right" width="77">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="center" width="77">
</td><td class="xl69" style="border-top:none;border-left:none;width:192pt" width="256">
</td><td class="xl72" style="border-top:none;width:71pt" width="95">
</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt; border-top:none;width:65pt" align="right" height="17" width="87">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="right" width="77">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="center" width="77">
</td><td class="xl69" style="border-top:none;border-left:none;width:192pt" width="256">
</td><td class="xl72" style="border-top:none;width:71pt" width="95">
</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt; border-top:none;width:65pt" align="right" height="17" width="87">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="right" width="77">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="center" width="77">
</td><td class="xl69" style="border-top:none;border-left:none;width:192pt" width="256">
</td><td class="xl72" style="border-top:none;width:71pt" width="95">
</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt; border-top:none;width:65pt" align="right" height="17" width="87">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="right" width="77">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="center" width="77">
</td><td class="xl69" style="border-top:none;border-left:none;width:192pt" width="256">
</td><td class="xl72" style="border-top:none;width:71pt" width="95">
</td></tr><tr style="height:12.75pt" height="17"><td class="xl66" style="height:12.75pt; border-top:none;width:65pt" align="right" height="17" width="87">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="right" width="77">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="center" width="77">
</td><td class="xl69" style="border-top:none;border-left:none;width:192pt" width="256">
</td><td class="xl72" style="border-top:none;width:71pt" width="95">
</td></tr><tr style="height:25.5pt" height="34"><td class="xl66" style="height:25.5pt; border-top:none;width:65pt" align="right" height="34" width="87">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="right" width="77">
</td><td class="xl66" style="border-top:none;border-left:none; width:58pt" align="center" width="77">
</td><td class="xl69" style="border-top:none;border-left:none;width:192pt" width="256">
</td><td class="xl72" style="border-top:none;width:71pt" width="95">
</td></tr></tbody></table>
 
Sort by date Sort by votes
i am no expert but i think ur question is not very clear.
give them a format of how u want the result to be like.
 
Upvote 0
Hi,
not sure if this is what you want.

With one helper Column in C:
C1 type 1
C2 type:=IF(A3=A2,C2,C2+1)
copy down till needed.

in D1 type:
=IF(C2=C1,"",ConcatIf($C$2:$C$26,C2,$B$2:$B$26,", "))

VBA code as follows:
Code: 
Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _
                    Optional Delimiter As String, Optional NoDuplicates As Boolean) As String
                    
    Rem the first three argumens of ConcatIf mirror those of SUMIF
    Rem the Delimiter and NoDuplicates arguments are optional (default "" and False)
    Dim i As Long, j As Long
    
    With compareRange.Parent
        Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1")))
    End With
    
    If compareRange Is Nothing Then Exit Function
    If stringsRange Is Nothing Then Set stringsRange = compareRange
    Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _
                                            stringsRange.Column - compareRange.Column)
    
    For i = 1 To compareRange.Rows.Count
        For j = 1 To compareRange.Columns.Count
            If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then
                If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then
                    ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j))
                End If
            End If
        Next j
    Next i
    ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1)
End Function

would that work for you?

HTML: 
MSISDN	GIFT Name	helper	formula
8527460456	this	1	this, that, that, then, there, those
8527460456	that	1	
8527460456	that	1	
8527460456	then	1	
8527460456	there	1	
8527460456	those	1	
8527461399	this	2	this, that
8527461399	that	2	
8800659379	that	3	that, then
8800659379	then	3	
8800852392	there	4	there, those
8800852392	those	4	
8826661469	this	5	this
8826661468	that	6	that
9560126160	that	7	that, then, there, those
9560126160	then	7	
9560126160	there	7	
9560126160	those	7	
9560852871	this	8	this
9560852872	that	9	that
9560852873	that	10	that, then, there
9560852873	then	10	
9560852873	there	10	
9717400181	those	11	those, that
9717400181	that	11
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
915
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
699
mumps
mumps
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G
E
  • Question Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
547
esben
E

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top