Concatenating unique values in a cell

Matharu

New Member
Joined
Apr 23, 2009
Messages
26
Hi,
I have some data in a sheet and i want the last cell in the row to contain the unique values in that row concatenated. How can it be done?

This is what i want

<TABLE style="WIDTH: 478pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=637 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=8 width=64><COL style="WIDTH: 94pt; mso-width-source: userset; mso-width-alt: 4571" width=125><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 384pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: silver" width=512 colSpan=8 height=18>Row</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 94pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: silver" width=125>Result that I want</TD></TR><TR style="HEIGHT: 51.75pt" height=69><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 51.75pt; BACKGROUND-COLOR: transparent" height=69>a</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">b</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">b</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">c</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">c</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">a</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">a</TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">d</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; WIDTH: 94pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=125>a
b
c
d
</TD></TR></TBODY></TABLE>


Thanking in advance..
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Maybe a UDF like

Code:
Function MyConcat(InputRange As Range) As Variant
Dim c As Range, cUnique As New Collection, temp As Variant

On Error Resume Next
For Each c In InputRange.Cells
    cUnique.Add c.Value, CStr(c.Value)
Next
On Error GoTo 0
    
For Each x In cUnique
temp = temp & cUnique(x)
Next

MyConcat = temp
End Function

Used like

Excel Workbook
ABCDEFGHI
1abbccaadabcd
Sheet1


Although I'm wondering if there's a way to use Join on a Collection instead of iterating through it again
 
Upvote 0
=JoinUniq(A1:H1,Char(10))
Code:
Function JoinUniq(ByRef rng As Range, ByVal myJoin As String) As String
Dim a, e
a = rng.Value
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For Each e In a
        .item(e) = Empty
    Next
    JoinUniq = Join$(.keys, myJoin)
End With
End Function
 
Upvote 0
Hi gauntletxg,
I tried inserting the newline character but its not inserting a new line. I get a square char printed... how to avoid it?

<TABLE style="WIDTH: 401pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=535 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3181" width=87><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl89 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 336pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" width=448 colSpan=7 height=17>My Data</TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 65pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=87>Result I'm getting</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>a</TD><TD class=xl87 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>b</TD><TD class=xl87 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>b</TD><TD class=xl87 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>c</TD><TD class=xl87 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>a</TD><TD class=xl87 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>d</TD><TD class=xl87 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>d</TD><TD class=xl88 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">a♫b⌂c⌂d⌂</TD></TR></TBODY></TABLE>

I'm using this:

Function MyConcat(InputRange As Range) As Variant
Dim c As Range, cUnique As New Collection, temp As Variant
On Error Resume Next
For Each c In InputRange.Cells
cUnique.Add c.Value, CStr(c.Value)
Next
On Error GoTo 0

For Each x In cUnique
temp = temp & cUnique(x) & vbLf
Next
MyConcat = temp
End Function


I tried using 'vbCr' also but in vain...

My main motive is to achieve this result:

<TABLE style="WIDTH: 447pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=596 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=7 width=64><COL style="WIDTH: 111pt; mso-width-source: userset; mso-width-alt: 5412" width=148><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl89 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" width=64 height=17>title1</TD><TD class=xl89 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=64>title2</TD><TD class=xl89 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=64>title3</TD><TD class=xl89 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=64>title4</TD><TD class=xl89 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=64>title5</TD><TD class=xl89 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=64>title6</TD><TD class=xl89 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=64>title7</TD><TD class=xl88 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 111pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=148>Result</TD></TR><TR style="HEIGHT: 63.75pt" height=85><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 63.75pt; BACKGROUND-COLOR: transparent" height=85>a</TD><TD class=xl87 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>b</TD><TD class=xl87 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>b</TD><TD class=xl87 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>c</TD><TD class=xl87 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>a</TD><TD class=xl87 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>d</TD><TD class=xl87 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>d</TD><TD class=xl90 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 111pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=148>title1, title5: a
title2, title3: b
title4: c
title6, title7: d

</TD></TR></TBODY></TABLE>

is it possible?
 
Upvote 0
=JoinUniq(A1:H2,Char(10))
Code:
Function JoinUniq(ByRef rng As Range, ByVal myJoin As String) As String
Dim a, i As Long, e
a = rng.Value
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = 1 To UBound(a, 2)
        .item(a(2, i)) = .item(a(2, i)) & IIf(.item(a(2, i)) <> "", ",") & a(1, i)
    Next
    Each e In .keys
        JoinUniq = JoinUniq & myJoin & .item(e) & ":" & e
    Next
End With
JoinUniq = Mid$(JoinUniq, Len(myJoin) + 1)
End Function
 
Upvote 0
=JoinUniq(A1:H2,Char(10))
Code:
Function JoinUniq(ByRef rng As Range, ByVal myJoin As String) As String
Dim a, i As Long, e
a = rng.Value
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = 1 To UBound(a, 2)
        .item(a(2, i)) = .item(a(2, i)) & IIf(.item(a(2, i)) <> "", ",") & a(1, i)
    Next
    Each e In .keys
        JoinUniq = JoinUniq & myJoin & .item(e) & ":" & e
    Next
End With
JoinUniq = Mid$(JoinUniq, Len(myJoin) + 1)
End Function

Hey Seiya
I have multiple rows in the table, so when i tried dragging formula, it did'nt work properly.What i need to change in the code to make it work for lower rows also???
 
Upvote 0
You are requesting different tasks post by post.

What are you actually trying to do?
 
Upvote 0
Actually i'm making this macro for my cousin and his requirements are also changing. I apologise if its annoying . I tried my best to do it myself also but i couldn't understand the code properly.:confused:.

Now i've talked to him and this is the final thing required.

<TABLE style="WIDTH: 298pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=397 border=0 x:str><COLGROUP><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1901" width=52><COL style="WIDTH: 39pt; mso-width-source: userset; mso-width-alt: 1901" span=3 width=52><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" width=50><COL style="WIDTH: 104pt; mso-width-source: userset; mso-width-alt: 5083" width=139><TBODY><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 39pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: silver" width=52 height=18>title1</TD><TD class=xl84 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 39pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=52>title2</TD><TD class=xl84 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 39pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=52>title3</TD><TD class=xl84 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 39pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=52>title4</TD><TD class=xl84 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 38pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=50>title5</TD><TD class=xl85 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 104pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: silver" width=139>Result</TD></TR><TR style="HEIGHT: 38.25pt" height=51><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; WIDTH: 39pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 38.25pt; BACKGROUND-COLOR: transparent" width=52 height=51>a1</TD><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">b1</TD><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">a1</TD><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">b1</TD><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">b1</TD><TD class=xl89 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 104pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=139>title1, title3: a1
title2, title4, title5: b1

</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; WIDTH: 39pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=52 height=34>a2</TD><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">b2</TD><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">a2</TD><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">b2</TD><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">b2</TD><TD class=xl89 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 104pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=139>title1, title3: a2
title2, title4, title5: b2

</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; WIDTH: 39pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=52 height=17>.</TD><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">.</TD><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">.</TD><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">.</TD><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">.</TD><TD class=xl89 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 104pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=139>….</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; WIDTH: 39pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=52 height=17>.</TD><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">.</TD><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">.</TD><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">.</TD><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">.</TD><TD class=xl89 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 104pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=139>….</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; WIDTH: 39pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=52 height=18>.</TD><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">.</TD><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">.</TD><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">.</TD><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">.</TD><TD class=xl89 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 104pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=139>….</TD></TR><TR style="HEIGHT: 26.25pt" height=35><TD class=xl87 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; WIDTH: 39pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 26.25pt; BACKGROUND-COLOR: transparent" width=52 height=35>an</TD><TD class=xl88 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">bn</TD><TD class=xl88 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">an</TD><TD class=xl88 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">bn</TD><TD class=xl88 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">bn</TD><TD class=xl89 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 104pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=139>title1, title3: an
title2, title4, title5: bn

</TD></TR></TBODY></TABLE>

The code you provided earlier is working perfectly for the first row but in case of the other rows its not giving the results required. Please help.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

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