Summary of name and cocatenate data

A

Adesa

New Member
Joined
Oct 7, 2019
Messages
6
Hi, Hope that someone can help me if this is possible to do in a formula.

Sorry that I can't post screenshot of table because I couldn't get the add-in to work. :sad:

Name Run Out

Tim 6 1
John 5 3
Len 6 3
John 3 4
Len 4 2
Tim 2 3

How i would like to summarise this data is to have each name uniquely listed followed by the Run and Out data for each person in linear format on a row, as displayed below.

Tim 6,1 2,3
John 5,3 3,4
Len 6,3 4,2

I know that it would involve LookUp and Concatenate but I am unsure whether this is possible to display how i would like. Any assistance would be greatly appreciated, even if it's to inform me that it isn't possible

Thanks
Adesa
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
Hi
May try
Code: 
Sub test()
    Dim a As Variant, lr, i, k, itm
    a = Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row).Resize(, 3)
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
            If Not .exists(a(i, 1)) Then
                .Add a(i, 1), a(i, 2) & "," & a(i, 3) & ","
            Else
                .Item(a(i, 1)) = .Item(a(i, 1)) & a(i, 2) & "," & a(i, 3)
            End If
        Next
        Range("h1:h" & .Count) = Application.Transpose(.keys)
        Range("i1:i" & .Count) = Application.Transpose(.items)
    End With
End Sub
 
Upvote 0
Adesa said:
.. in a formula.
Click to expand...
Assuming that your Excel version has the TEXTJOIN function try these.

E1 is copied down as far as you might ever need
F2 is an array formula so it should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formula can then be copied across to G2 and down.

Excel Workbook
ABCDEFG
1NameRunOutNameRunOut
2Tim61Tim6, 21, 3
3John53John5, 33, 4
4Len63Len6, 43, 2
5John34
6Len42
7Tim23
8
List & Concat






Adesa said:
Sorry that I can't post screenshot of table because I couldn't get the add-in to work. :sad:
Click to expand...
Which Add-In were you trying and what were the symptoms of it not working? That is, at what point did you get stuck?
 
Upvote 0
Opps
sorry miss reading
Code: 
Sub test()
    Dim a As Variant, lr, i, k, itm
    a = Range("a2:a" & Cells(Rows.Count, 1).End(xlUp).Row).Resize(, 3)
    With CreateObject("scripting.dictionary")
        For i = 1 To UBound(a)
            If Not .exists(a(i, 1)) Then
                .Add a(i, 1), a(i, 2) & "," & a(i, 3) & "\"
            Else
                .Item(a(i, 1)) = .Item(a(i, 1)) & a(i, 2) & "," & a(i, 3)
            End If
        Next
        Range("h2:h" & .Count + 1) = Application.Transpose(.keys)
        Range("i2:i" & .Count + 1) = Application.Transpose(.items)
        Range("i2:i" & .Count + 1).TextToColumns Destination:=Range("I2"), Other:=True, OtherChar _
                                                               :="\", FieldInfo:=Array(Array(1, 1), Array(3, 2)), TrailingMinusNumbers:=True
    End With
End Sub
 
Upvote 0
Thank you for the quick responses, much appreciated. I will try both the code and the formula to see which best suits my needs, then reply back with my progress.

Peter_SSs said:
Which Add-In were you trying and what were the symptoms of it not working? That is, at what point did you get stuck?
Click to expand...

MrExcel HTML Maker 20170807 downloaded from this board.

I am using Excel 2007 (a bit old I know, however, it does what i require). The add-in appears in the add-in list but I could not find it in the ribbon anywhere. I also checked to ensure that there was no check marks in the Trust Center settings and everything appeared to be in order.

Thanks again
Adesa
 
Upvote 0
Adesa said:
MrExcel HTML Maker 20170807 downloaded from this board.

I am using Excel 2007 (a bit old I know, however, it does what i require). The add-in appears in the add-in list but I could not find it in the ribbon anywhere. I also checked to ensure that there was no check marks in the Trust Center settings and everything appeared to be in order.

Thanks again
Adesa
Click to expand...
Did you attend to this part of the information about the Add-In?

N.B.
- With any of the Add-Ins with recent versions of Windows, you may need to "unblock" the file. In Windows Explorer, find the file, right-click, Properties, General tab, tick the 'Unblock' box down near the bottom.
Click to expand...
 
Upvote 0
Adesa said:
I will try both the code and the formula ..
Click to expand...
OK, if you are also considering code, then my suggestion would be:

Rich (BB code): 
Sub ListAndConcat()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  a = Range("A1", Range("C" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    If d.exists(a(i, 1)) Then
      d(a(i, 1)) = Replace(d(a(i, 1)), ";", ", " & a(i, 2) & ";") & ", " & a(i, 3)
    Else
      d(a(i, 1)) = a(i, 2) & ";" & a(i, 3)
    End If
  Next i
  With Range("E1:F1").Resize(d.Count)
    .Value = Application.Transpose(Array(d.Keys, d.Items))
    .Columns(2).TextToColumns DataType:=xlDelimited, Semicolon:=True, Comma:=False, Space:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1))
    .Resize(, 3).Columns.AutoFit
  End With
End Sub
 
Last edited:
Upvote 0
Sorry that I can't post screenshot of table because I couldn't get the add-in to work.
Click to expand...

You can post a table without using add-in.
To copy a simple table from worksheet:
1. In your worksheet select the table/range
2. In the menu, select border icon > select All Borders > then copy.
3. Back to the thread > in the reply box paste the table


A simple table would look like this (there are no column letter & row number):

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]NAME[/TD]
[TD="class: xl65, width: 64"]ID[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Mayson[/TD]
[TD="class: xl65, width: 64"]A01[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Amaya[/TD]
[TD="class: xl65, width: 64"]A03[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 64"]Cayden[/TD]
[TD="class: xl65, width: 64"]A04[/TD]
[/TR]
</tbody>[/TABLE]

If you want, you can arrange the data by typing the column letter & row number manually, like this:


<colgroup><col style="width:54pt" width="72" span="3"> </colgroup><tbody>
[TD="class: xl63, width: 72"] [/TD]
[TD="class: xl64, width: 72"]A[/TD]
[TD="class: xl64, width: 72"]B[/TD]

[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl65, width: 72"]NAME[/TD]
[TD="class: xl65, width: 72"]ID[/TD]

[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl65, width: 72"]Mayson[/TD]
[TD="class: xl65, width: 72"]A01[/TD]

[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl65, width: 72"]Amaya[/TD]
[TD="class: xl65, width: 72"]A03[/TD]

[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl65, width: 72"]Cayden[/TD]
[TD="class: xl65, width: 72"]A04[/TD]

</tbody>
 
Upvote 0
<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:TrackMoves/> <w:TrackFormatting/> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:DoNotPromoteQF/> <w:LidThemeOther>EN-AU</w:LidThemeOther> <w:LidThemeAsian>X-NONE</w:LidThemeAsian> <w:LidThemeComplexScript>X-NONE</w:LidThemeComplexScript> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> <w:SplitPgBreakAndParaMark/> <w:DontVertAlignCellWithSp/> <w:DontBreakConstrainedForcedTables/> <w:DontVertAlignInTxbx/> <w:Word11KerningPairs/> <w:CachedColBalance/> </w:Compatibility> <m:mathPr> <m:mathFont m:val="Cambria Math"/> <m:brkBin m:val="before"/> <m:brkBinSub m:val="--"/> <m:smallFrac m:val="off"/> <m:dispDef/> <m:lMargin m:val="0"/> <m:rMargin m:val="0"/> <m:defJc m:val="centerGroup"/> <m:wrapIndent m:val="1440"/> <m:intLim m:val="subSup"/> <m:naryLim m:val="undOvr"/> </m:mathPr></w:WordDocument> </xml><![endif]--> Thanks Akuini, I will try that if needed.

Peter, thank you for your assistance and patience it is appreciated.

The version of Excel that I am using does not have the TEXTJOIN function so I could not test the formula that you provided. I could test the code in which you suggested and found that it produced the same result as the output for your formula in Post#3.
The output produced isn’t quite what I was looking for as there may have been a little confusion as I may not have been clear enough to what I was requesting. So I will attempt to explain further with some colour in the table.



[TABLE="class: MsoNormalTable"]
<tbody>[TR]
[TD="width: 13"][/TD]
[TD="width: 43"]
[FONT=&quot]A[/FONT]​
[/TD]
[TD]
[FONT=&quot]B[/FONT]​
[/TD]
[TD]
[FONT=&quot]C[/FONT]​
[/TD]
[TD]
[FONT=&quot]D[/FONT]​
[/TD]
[TD]
[FONT=&quot]E[/FONT]​
[/TD]
[TD]
[FONT=&quot]F[/FONT]​
[/TD]
[TD]
[FONT=&quot]G[/FONT]​
[/TD]
[/TR]
[TR]
[TD="width: 13"]
[FONT=&quot]1[/FONT]​
[/TD]
[TD="width: 43"] [FONT=&quot]Name[/FONT]
[/TD]
[TD]
[FONT=&quot]Run[/FONT]​
[/TD]
[TD]
[FONT=&quot]Out[/FONT]​
[/TD]
[TD][/TD]
[TD] [FONT=&quot]Name[/FONT]
[/TD]
[TD] [FONT=&quot]First[/FONT]
[/TD]
[TD] [FONT=&quot]Second[/FONT]
[/TD]
[/TR]
[TR]
[TD="width: 13"]
[FONT=&quot]2[/FONT]​
[/TD]
[TD="width: 43"] [FONT=&quot]Tim[/FONT]
[/TD]
[TD]
[FONT=&quot]6[/FONT]
​
[/TD]
[TD]
[FONT=&quot]1[/FONT]​
[/TD]
[TD][/TD]
[TD] [FONT=&quot]Tim[/FONT]
[/TD]
[TD]
[FONT=&quot]6, 1[/FONT]
​
[/TD]
[TD]
[FONT=&quot]2,3[/FONT]
​
[/TD]
[/TR]
[TR]
[TD="width: 13"]
[FONT=&quot]3[/FONT]​
[/TD]
[TD="width: 43"] [FONT=&quot]John[/FONT]
[/TD]
[TD]
[FONT=&quot]5[/FONT]
​
[/TD]
[TD]
[FONT=&quot]3[/FONT]
​
[/TD]
[TD][/TD]
[TD] [FONT=&quot]John[/FONT]
[/TD]
[TD]
[FONT=&quot]5, 3[/FONT]
​
[/TD]
[TD]
[FONT=&quot]3, 4[/FONT]
​
[/TD]
[/TR]
[TR]
[TD="width: 13"]
[FONT=&quot]4[/FONT]​
[/TD]
[TD="width: 43"] [FONT=&quot]Len[/FONT]
[/TD]
[TD]
[FONT=&quot]6[/FONT]
​
[/TD]
[TD]
[FONT=&quot]3[/FONT]​
[/TD]
[TD][/TD]
[TD] [FONT=&quot]Len[/FONT]
[/TD]
[TD]
[FONT=&quot]6, 3[/FONT]
​
[/TD]
[TD]
[FONT=&quot]4, 2[/FONT]​
[/TD]
[/TR]
[TR]
[TD="width: 13"]
[FONT=&quot]5[/FONT]​
[/TD]
[TD="width: 43"] [FONT=&quot]John[/FONT]
[/TD]
[TD]
[FONT=&quot]3[/FONT]
​
[/TD]
[TD]
[FONT=&quot]4[/FONT]​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 13"]
[FONT=&quot]6[/FONT]​
[/TD]
[TD="width: 43"] [FONT=&quot]Len[/FONT]
[/TD]
[TD]
[FONT=&quot]4[/FONT]​
[/TD]
[TD]
[FONT=&quot]2[/FONT]​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 13"]
[FONT=&quot]7[/FONT]​
[/TD]
[TD="width: 43"] [FONT=&quot]Tim[/FONT]
[/TD]
[TD]
[FONT=&quot]2[/FONT]
​
[/TD]
[TD]
[FONT=&quot]3[/FONT]​
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="width: 13"]
[FONT=&quot]8[/FONT]​
[/TD]
[TD="width: 43"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The output that I am looking for is to have the Name followed by a Concatenation of each Run&Out for that Name as I have in the example Table above. Potentially there could be up to 10 instances of each individual Name thereby requiring additional column/fields in the output.


Peter_SSs said:
Did you attend to this part of the information about the Add-In?
Click to expand...

Yes I ensured that the file wasn't blocked. When I opened the VBA editor I could see the MrExcelHTMLMaker20170807 Project with 8 Modules included. It appears to load the code but somehow doesn’t run the necessary routine to enable ribbon activation as I still could not find any reference to it on any of the tab ribbons.

Regards

Adesa
 
Upvote 0
Adesa said:
.. there may have been a little confusion as I may not have been clear enough to what I was requesting.
Click to expand...
No, the issue was that I didn't read it carefully enough. :oops:

The good news is that makes the code simpler. :)
I haven't worried about headings at the moment, but see if this is closer to what you want.
If so, and you want headings added, can you clarify exactly what those headings should be?

Rich (BB code): 
Sub ListAndConcat()
  Dim d As Object
  Dim a As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  a = Range("A2", Range("C" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    d(a(i, 1)) = d(a(i, 1)) & ";" & a(i, 2) & ", " & a(i, 3)
  Next i
  With Range("E2:F2").Resize(d.Count)
    .Value = Application.Transpose(Array(d.Keys, d.Items))
    .Columns(2).TextToColumns DataType:=xlDelimited, Semicolon:=True, Comma:=False, Space:=False, FieldInfo:=Array(Array(1, 9))
    .CurrentRegion.Columns.AutoFit
  End With
End Sub



Adesa said:
Yes I ensured that the file wasn't blocked. When I opened the VBA editor I could see the MrExcelHTMLMaker20170807 Project with 8 Modules included. It appears to load the code but somehow doesn’t run the necessary routine to enable ribbon activation as I still could not find any reference to it on any of the tab ribbons.
Click to expand...
If you right click the ribbon and choose 'Customize the Ribbon ...' on the right hand side is the Add-ins checkbox ticked?

Also, if you select some data on a worksheet & right click, are there any options in that menu like "Generate Html (use Default)' etc as that is another way to launch the HTML Maker.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Same color for same category for stacked column chart and formatting data labels together
Replies
3
Views
296
naveyafluz
N
B
  • Solved
macro to print selection when there is data in rows
2 3
Replies
24
Views
2K
bh24524
B
S
  • Question Question
combining data from multiple workbooks with the same sheet name into a new workbook
Replies
3
Views
459
rlv01
R
H
  • Question Question
Making a running 365 absence tracker
Replies
3
Views
641
HorizonHunter
H
B
  • Question Question
XML to PDF
Replies
0
Views
280
BijanBorazjani
B

Forum statistics

Threads
1,223,888
Messages
6,175,212
Members
452,618
Latest member
Tam84

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