Summary of name and cocatenate data

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. :(

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

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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
.. in a formula.
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






Sorry that I can't post screenshot of table because I couldn't get the add-in to work. :(
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.

Which Add-In were you trying and what were the symptoms of it not working? That is, at what point did you get stuck?

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
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
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.
 
Upvote 0
I will try both the code and the formula ..
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.

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):

NAMEID
MaysonA01
AmayaA03
CaydenA04

<tbody>
</tbody>

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

AB
1NAMEID
2MaysonA01
3AmayaA03
4CaydenA04

<colgroup><col style="width:54pt" width="72" span="3"> </colgroup><tbody>
</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.



[FONT=&quot]A[/FONT]
[FONT=&quot]B[/FONT]
[FONT=&quot]C[/FONT]
[FONT=&quot]D[/FONT]
[FONT=&quot]E[/FONT]
[FONT=&quot]F[/FONT]
[FONT=&quot]G[/FONT]
[FONT=&quot]1[/FONT]​
[FONT=&quot]Name[/FONT]
[FONT=&quot]Run[/FONT]​
[FONT=&quot]Out[/FONT]​
[FONT=&quot]Name[/FONT]
[FONT=&quot]First[/FONT]
[FONT=&quot]Second[/FONT]
[FONT=&quot]2[/FONT]​
[FONT=&quot]Tim[/FONT]
[FONT=&quot]6[/FONT]
[FONT=&quot]1[/FONT]
[FONT=&quot]Tim[/FONT]
[FONT=&quot]6, 1[/FONT]
[FONT=&quot]2,3[/FONT]
[FONT=&quot]3[/FONT]​
[FONT=&quot]John[/FONT]
[FONT=&quot]5[/FONT]
[FONT=&quot]3[/FONT]
[FONT=&quot]John[/FONT]
[FONT=&quot]5, 3[/FONT]
[FONT=&quot]3, 4[/FONT]
[FONT=&quot]4[/FONT]​
[FONT=&quot]Len[/FONT]
[FONT=&quot]6[/FONT]
[FONT=&quot]3[/FONT]
[FONT=&quot]Len[/FONT]
[FONT=&quot]6, 3[/FONT]
[FONT=&quot]4, 2[/FONT]​
[FONT=&quot]5[/FONT]​
[FONT=&quot]John[/FONT]
[FONT=&quot]3[/FONT]
[FONT=&quot]4[/FONT]
[FONT=&quot]6[/FONT]​
[FONT=&quot]Len[/FONT]
[FONT=&quot]4[/FONT]​
[FONT=&quot]2[/FONT]​
[FONT=&quot]7[/FONT]​
[FONT=&quot]Tim[/FONT]
[FONT=&quot]2[/FONT]
[FONT=&quot]3[/FONT]
[FONT=&quot]8[/FONT]​

<tbody>
</tbody>

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.


Did you attend to this part of the information about the Add-In?

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
.. there may have been a little confusion as I may not have been clear enough to what I was requesting.
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



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.
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

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

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