Summary of name and cocatenate data

[Adesa]

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

 

[Adesa]

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?


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.

That code works fine for me.

Headings... would be ok to use Game1 Game2 Game3 etc though there could be up to 10 required.

I couldn't get anything when I right clicked in the ribbon area... just blank, nothing as if right click was disabled. Also there was no option for HTML Maker in the right click menu when I selected some data. I will try on another machine tomorrow, maybe that will let me know if its the add-in or this computer.

Regards
Adesa

 

[Peter_SSs]

Headings... would be ok to use Game1 Game2 Game3 etc though there could be up to 10 required.

Assuming data in columns A:C and nothing in columns to the right of that, try replacing the last section of my code with this

  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))
    With .CurrentRegion
      .Rows(0).FormulaR1C1 = "=""Game""&COLUMNS(RC" & .Column & ":RC)-1"
      .Cells(0, 1).Value = "Name"
      .Rows(0).Value = .Rows(0).Value
      .EntireColumn.AutoFit
    End With
  End With
End Sub

I couldn't get anything when I right clicked in the ribbon area... just blank, nothing as if right click was disabled.

So, even without considering the HTML maker, you cannot customize your ribbon in any way?
Are you in a work environment where IT may be blocking you from customizing your ribbon (or installing Add-Ins)?

 

[Adesa]

Thank you Peter, that code works fine and produces the output that i was looking for.

So, even without considering the HTML maker, you cannot customize your ribbon in any way?
Are you in a work environment where IT may be blocking you from customizing your ribbon (or installing Add-Ins)?

The computer is a home desktop and does not have any restrictions on it. I tried the add-in on a laptop with Office 2010 without any issue, so there must be an issue of some kind with the computer at home and I will investigate further.

Thanks again
Adesa

 

[Peter_SSs]

Thank you Peter, that code works fine and produces the output that i was looking for.

Good news!

The computer is a home desktop and does not have any restrictions on it. I tried the add-in on a laptop with Office 2010 without any issue, so there must be an issue of some kind with the computer at home and I will investigate further.

One thing to check would be to delve into the Trust Center -> Trust Center Settings .. of Excel (File -> Options) in each machine and see if that turns up any differences that might be worth testing.

 

[Adesa]

I will certainly look at the Trust Center to see if there are any differences.

Once again, thank you for your patience and assistance it most certainly is appreciated.

You can now close this thread as my initial help question has been answered and solved.

Regards
Adesa

 

[Peter_SSs]

No problem, glad to help.

FYI: At MrExcel we don't actually 'close' threads or formally mark them as 'solved' since it is not uncommon, after the original poster has indicated they are quite satisfied with the result, for an even better answer to be posted. That 'better' answer may then be useful to the original poster and/or to other people reading the thread in the future.