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
 
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
 
Upvote 0

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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

Rich (BB code):
  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)?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
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