+ Reply to Thread
Results 1 to 29 of 29

combine columns datas for identical values in first column

  1. #1
    Forum Contributor
    Join Date
    04-21-2012
    Location
    rome, italy
    MS-Off Ver
    365
    Posts
    131

    combine columns datas for identical values in first column

    see attached excel.

    i have a list made by 4 columns. the columns C is not important, the columns A,B,D contains data i need to reorganize.

    In column A there are values ("point"), some are repeated several times and for each one there are values in column B but only sometime in comlum D.

    For every value ("point") i need to write, in a single cell, all the "types" and if a "value" is present it should be written between commas after the type.

    is there a way to make it by formulas?
    Attached Files Attached Files
    Last edited by vermaccio; 01-05-2022 at 05:05 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: reoganize datas

    Administrative Note:

    POOR THREAD TITLE

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    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.

  3. #3
    Forum Contributor
    Join Date
    04-21-2012
    Location
    rome, italy
    MS-Off Ver
    365
    Posts
    131

    Re: combine columns datas for identical values in first column

    i tryied to rewrite tile and description. i hope is better now.

  4. #4
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: combine columns datas for identical values in first column

    IN I6 then copied down.

    =TEXTJOIN("",TRUE,IF($A$2:$A$18=$H6,$B$2:$B$18&IF($D$2:$D$18<>"","("&$D$2:$D$18&")",""),""))

    It is an ARRAY function. For me ARRAY entry has to be done(Ctrl+Shift+Enter Pressed together). Since your version is 365 ARRAY entry may not be required. Try normal entry.
    Attached Files Attached Files
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  5. #5
    Forum Contributor
    Join Date
    04-21-2012
    Location
    rome, italy
    MS-Off Ver
    365
    Posts
    131

    Re: combine columns datas for identical values in first column

    ok, but you have to compile automatically the column H. i haven't it. how can i do it?

  6. #6
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: combine columns datas for identical values in first column

    You can put this in H6 only
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    to get the col A values

  7. #7
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: combine columns datas for identical values in first column

    Please try

    =CONCAT(FILTER($B$2:$B$18&TEXT($D$2:$D$18;"(0);(-0);");$A$2:$A$18=H6))

    or the whole table
    =LET(z;A2:D18;p;INDEX(z;;1);t;INDEX(z;;2);CHOOSE({1\2};UNIQUE(FILTER(p;p));FILTERXML(CONCAT("<x><m>";IF(t=0;"</m><m>";t)&TEXT(INDEX(z;;4);"(0);(-0);");"</m></x>");"//m")))
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    04-21-2012
    Location
    rome, italy
    MS-Off Ver
    365
    Posts
    131

    Re: combine columns datas for identical values in first column

    thank you. solved

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: combine columns datas for identical values in first column

    Glad to help & thanks for the feedback.

  10. #10
    Forum Contributor
    Join Date
    04-21-2012
    Location
    rome, italy
    MS-Off Ver
    365
    Posts
    131

    Re: combine columns datas for identical values in first column

    ehm.... can you help to put the result in a different sheet? i tried but it doesn.'t work.
    thank you
    Attached Files Attached Files

  11. #11
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: combine columns datas for identical values in first column

    With Bo_Ry's 2nd formula you can do it like
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  12. #12
    Forum Contributor
    Join Date
    04-21-2012
    Location
    rome, italy
    MS-Off Ver
    365
    Posts
    131

    Re: combine columns datas for identical values in first column

    i tested it but i have problems
    here attached the real datas i am working on.
    i don't understand where is the problem because i used your formula.

    as you can see in the "result" sheet the row 2,3,4 have wrong results.

    note:
    starting from row 6 there are "errors" only because formula apply to 50 rows while the "test2" sheet contaings data only for only 31 rows.
    This because the "test2" sheet can contain an unkonwn number of rows. eventually is there a way to stop the formula at last data in the "test2" sheet?
    Attached Files Attached Files

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: combine columns datas for identical values in first column

    You might want to remove the SOLVED tag for now.

  14. #14
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,004

    Re: combine columns datas for identical values in first column

    Please Login or Register  to view this content.
    Attached Files Attached Files
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  15. #15
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: combine columns datas for identical values in first column

    Please try

    =LET(z,test2!C2:F50,p,INDEX(z,,1),t,INDEX(z,,2),CHOOSE({1,2},UNIQUE(FILTER(p,p)),FILTERXML("<x><m>"&SUBSTITUTE(TRIM(CONCAT(IF(t=0," ",t)&TEXT(INDEX(z,,4),"(0);(-0);")))&"</m></x>"," ","</m><m>"),"//m")))
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    04-21-2012
    Location
    rome, italy
    MS-Off Ver
    365
    Posts
    131

    Re: combine columns datas for identical values in first column

    i copied the VB in my excel file. then i created the button but i obtain a mistake in line.

    here attached the file where i tried to insert your macro.
    can, please, tell me what's wrong so i can correct in my final file?
    (this is just a part of the real file, i deleted many other sheets because the excel file is too big to upload here), so i need to understand where is the problem.

    thank you
    Attached Files Attached Files

  17. #17
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,004

    Re: combine columns datas for identical values in first column

    Error is tab name:

    "restraint_result" not ""retraint_result"
    Attached Files Attached Files
    Last edited by JohnTopley; 12-29-2021 at 12:28 PM.

  18. #18
    Forum Contributor
    Join Date
    04-21-2012
    Location
    rome, italy
    MS-Off Ver
    365
    Posts
    131

    Re: combine columns datas for identical values in first column

    thank you very much to all. problem solved

  19. #19
    Forum Contributor
    Join Date
    04-21-2012
    Location
    rome, italy
    MS-Off Ver
    365
    Posts
    131

    Re: combine columns datas for identical values in first column

    Bo-Ry:
    i need your help to extend the function.
    see attached file.
    i need to extract oyher datas from another column ("I") but in two differerent ways.

    1)only the last text for a point (you can have nothing or one or more text for a node, in several rows)
    2)all the text for a point

    in the excel file there is an example (see red text) in the result sheet.
    Attached Files Attached Files

  20. #20
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,004

    Re: combine columns datas for identical values in first column

    Please Login or Register  to view this content.

    NOTE: tab names changed!
    Attached Files Attached Files

  21. #21
    Forum Contributor
    Join Date
    04-21-2012
    Location
    rome, italy
    MS-Off Ver
    365
    Posts
    131

    Re: combine columns datas for identical values in first column

    thank you, it works.
    but is it possibile to have the same solution suing formulas?

  22. #22
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,004

    Re: combine columns datas for identical values in first column

    I do not have 365 so I'll leave it to Bo_Ry (and others) to provide a formula solution.

  23. #23
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: combine columns datas for identical values in first column

    Last try

    PHP Code: 
    =LET(z,test2!C2:I50,p,INDEX(z,,1),t,INDEX(z,,2),u,UNIQUE(FILTER(p,p)),
    d,IFERROR(SUBSTITUTE(FILTERXML("<x><m>"&TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(CONCAT(IF(p,"|"&INDEX(z,,7)," ")))," ","</m><m>"),"|"," "))&"</m></x>","//m")," ","|"),""),
    CHOOSE({1,2,3,4},u,FILTERXML("<x><m>"&SUBSTITUTE(TRIM(CONCAT(IF(t=0," ",t)&TEXT(INDEX(z,,4),"(0);(-0);")))&"</m></x>"," ","</m><m>"),"//m"),TRIM(RIGHT(SUBSTITUTE(d,"|",REPT(" ",9)),9)),d)) 
    Attached Files Attached Files

  24. #24
    Forum Contributor
    Join Date
    04-21-2012
    Location
    rome, italy
    MS-Off Ver
    365
    Posts
    131

    Re: combine columns datas for identical values in first column

    JohnTopley:

    there is a problem in your VBA.
    see attached file

    If in sheet "Restraints", cell I26, I27, I28 have values,

    result in sheet "restraint_result", cell C6 is wrong.
    Attached Files Attached Files
    Last edited by vermaccio; 01-05-2022 at 05:04 AM.

  25. #25
    Forum Contributor
    Join Date
    04-21-2012
    Location
    rome, italy
    MS-Off Ver
    365
    Posts
    131

    Re: combine columns datas for identical values in first column

    Bo_RY:

    if in a cell there are spaces in text, result are wrong. see attached file

    sheet "test2": cell I2

    the result are all wrong
    Attached Files Attached Files
    Last edited by vermaccio; 01-05-2022 at 05:05 AM.

  26. #26
    Forum Contributor
    Join Date
    04-21-2012
    Location
    rome, italy
    MS-Off Ver
    365
    Posts
    131

    Re: combine columns datas for identical values in first column

    note:
    the 2 attachment for Bo_ry and Johntopley have the same name but are different files. infact one is with macro (.xlsm), the other is without (.xlxs).
    sorry for the confusion.....

  27. #27
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,004

    Re: combine columns datas for identical values in first column

    See attached: I had already realised there could be more than 2 values in text.
    Attached Files Attached Files

  28. #28
    Forum Contributor
    Join Date
    04-21-2012
    Location
    rome, italy
    MS-Off Ver
    365
    Posts
    131

    Re: combine columns datas for identical values in first column

    thank you. now let's wait for formula solution

  29. #29
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: combine columns datas for identical values in first column

    In A2

    Please Login or Register  to view this content.
    ARRAY formula in B2

    Please Login or Register  to view this content.
    In C2

    Please Login or Register  to view this content.
    ARRAY formula in D2

    Please Login or Register  to view this content.
    All copied down.
    Since you are having 365 version ARRAY entry may not be required.
    To enter ARRAY formula
    Copy and paste the formula in cell
    Press F2
    Press Ctrl+Shift+Enter together
    Excel covers the formula with {}.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Code runs in small datas but not working perfectly in bigger datas
    By RAJESH SHAH in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-04-2021, 10:28 PM
  2. [SOLVED] Getting datas every 3 rows
    By RoD69 in forum Excel General
    Replies: 8
    Last Post: 10-01-2013, 04:51 AM
  3. compare oracle table datas and excel datas
    By james94539 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-01-2013, 03:48 PM
  4. Import Datas
    By shyam sundar in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-21-2013, 02:56 PM
  5. datas in workbook 2 is not getting updated when datas in workbook 1 is changed
    By share knowledge in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-04-2011, 12:21 PM
  6. sort certain datas in a wide range of datas
    By go4cdt in forum Excel General
    Replies: 2
    Last Post: 03-20-2006, 12:45 PM
  7. [SOLVED] re : Finding the datas and deleting datas which are not found.
    By ddiicc in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-30-2005, 09:05 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1