LinkBack URL
About LinkBacks
Hi,
I have attached a document. In Column A is a list of names separated by commas. In Column B is the output/result that I would like to have to summarise the list of names.
If you need further information, please let me know.
Thanks
Hi,
I have attached a document. In Column A is a list of names separated by commas. In Column B is the output/result that I would like to have to summarise the list of names.
If you need further information, please let me know.
Thanks
Last edited by sirdon; 05-12-2020 at 12:50 AM.
Hi sirdon, may be you can download power query (excel 2010 add-on)
this can be done by power query editor (one off steps mostly user interface and some simple M. code) to achieve what you want
Attached is the results and also the code for you to paste to power query editor
you can insert a new value in Sheet1 row 6, then goto output worksheet and right click on the table refresh, you can see the result instanty
Chris
Christopher Yap
Will see if someone else comes up with a different way without having to do that. Ideally i need this to work in googlesheets as well.
Here is a google link for the document: https://docs.google.com/spreadsheets...it?usp=sharing
Please update the thread title to say that this needs to work on Excel and GoogleSheets.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
This one works with Excel 365, Excel online, Google sheet
Pleaes try
Formula:
Please Login or Register to view this content.
For score sort Excel and Google sheet work diffrently
This only works with Excel
Formula:Please Login or Register to view this content.
This one only works with Google sheet
Formula:Please Login or Register to view this content.
Thank you Bo_Ry, this works great. A huge appreciation to everyone again. Thanks
Bo_Ry, I just noticed, there is an issue when i only have one name. An error comes up. Also if there is no name then return a blank cell.
Here is the link again to view it: https://docs.google.com/spreadsheets...it?usp=sharing
Thanks,
Last edited by sirdon; 05-14-2020 at 12:44 AM.
Fixed by add =if(iserror(find(",",A2)),A2,
and new formula in C4
=ArrayFormula(TEXTJOIN("",,query(TRANSPOSE(trim(SPLIT(A2,",)"))), "select Col1, count(Col1) …
That is great. I have now discovered another issue.
If i have the same name twice and there is no other name, the first two formulas come up with an error. The third formula works, but an comma is displayed. Again the link will display this.
As always I am greatly appreciated.
Thanks,
Bo_Ry, Sorry to keep at this, but obviously i keep testing things. If you now look at the link and see formula three (3), when i add in an extra name, there is no comma at all to separate the names. So i don't want a comma if there is only one (1) name. But once there is two (2) or more names, i need a comma to separate them. Many thanks
Bo-Ry, thank you so much. Works amazingly well. Its a huge time and effort from you and i very much appreciate it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks