+ Reply to Thread
Results 1 to 11 of 11

How to join data (column B) based on grouped duplicate values (column A)

  1. 04-20-2022, 06:37 AM #1
    Marcel_G
    Marcel_G is offline
    Registered User
    Join Date
    04-20-2022
    Location
    Prague, Czechia
    MS-Off Ver
    MS Office 2019
    Posts
    6

    Question How to join data (column B) based on grouped duplicate values (column A)

    Could someone please help me solve the following problem?

    I have a list of journals with two columns: ISSN and [subject] Field.

    What I need is this:
    Go through column A (ISSN), find all occurrences of each ISSN code in it, and then join together all their data from column B (Field), and output the result to column C (Result) - see attached image.

    excel.jpg

    PS: I now use the following formula for the result in the cell C2:
    Please Login or Register  to view this content.
    The problem is that I have to manually edit the "B2:B4" part on each line. Would anyone have an idea how to fully automate this?

    Thank you very much in advance to anyone who is willing to help me!
    Reply With QuoteRegister To Reply
  2. 04-20-2022, 06:51 AM #2
    WideBoyDixon
    WideBoyDixon is offline
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: How to join data (column B) based on grouped duplicate values (column A)

    Can't you just use an adaptation of your existing formula?

    Please Login or Register  to view this content.
    WBD
    Office 365 on Windows 11, looking for ✶ rep!
    Reply With QuoteRegister To Reply
  3. 04-20-2022, 07:02 AM #3
    Marcel_G
    Marcel_G is offline
    Registered User
    Join Date
    04-20-2022
    Location
    Prague, Czechia
    MS-Off Ver
    MS Office 2019
    Posts
    6

    Re: How to join data (column B) based on grouped duplicate values (column A)

    When I edit it as you suggest, it gives me a #VALUE! error.
    Reply With QuoteRegister To Reply
  4. 04-20-2022, 07:10 AM #4
    WideBoyDixon
    WideBoyDixon is offline
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: How to join data (column B) based on grouped duplicate values (column A)

    Then please attach an example book that shows the error with this formula. Without the example, it's very difficult to see what the problem might be.

    WBD
    Reply With QuoteRegister To Reply
  5. 04-20-2022, 07:17 AM #5
    Marcel_G
    Marcel_G is offline
    Registered User
    Join Date
    04-20-2022
    Location
    Prague, Czechia
    MS-Off Ver
    MS Office 2019
    Posts
    6

    Re: How to join data (column B) based on grouped duplicate values (column A)

    Here's a picture:
    excel2.jpg
    .
    PS: It seems I am not allowed to add an excel file attachment.
    I use Czech version of Excel:
    =TEXTJOIN(" * ";PRAVDA;KDYŽ(A10=$A$2:$A$2000;$B$2:$B$2000;""))
    In English it would be:
    =TEXTJOIN(" * ",TRUE,IF(A10=$A$2:$A$2000,$B$2:$B$2000,""))
    Last edited by Marcel_G; 04-20-2022 at 07:19 AM.
    Reply With QuoteRegister To Reply
  6. 04-20-2022, 07:22 AM #6
    WideBoyDixon
    WideBoyDixon is offline
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: How to join data (column B) based on grouped duplicate values (column A)

    But then it's working on the other two lines? Or are they still using the old formula? I don't know why you can't add an attachment - click "Go Advanced" on your reply then "Manage Attachments" and upload the XLSX file - is that not possible?

    WBD
    Reply With QuoteRegister To Reply
  7. 04-20-2022, 07:26 AM #7
    Marcel_G
    Marcel_G is offline
    Registered User
    Join Date
    04-20-2022
    Location
    Prague, Czechia
    MS-Off Ver
    MS Office 2019
    Posts
    6

    Re: How to join data (column B) based on grouped duplicate values (column A)

    Yes, on the other rows there are the old formula.
    As a newbie I am not allowed to add links nor attachments (other than pictures).
    I have uploaded my file to https ... golias.net [slash] magazines.xlsx (if you can decipher it).
    Reply With QuoteRegister To Reply
  8. 04-20-2022, 07:34 AM #8
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,025

    Re: How to join data (column B) based on grouped duplicate values (column A)

    Maybe this:

    =TEXTJOIN("*",,FILTER($B$2:$B$1957,$A$2:$A$1957=A2))
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  9. 04-20-2022, 07:42 AM #9
    Marcel_G
    Marcel_G is offline
    Registered User
    Join Date
    04-20-2022
    Location
    Prague, Czechia
    MS-Off Ver
    MS Office 2019
    Posts
    6

    Re: How to join data (column B) based on grouped duplicate values (column A)

    Yay! That's it!
    PS: The only problem is that on my desktop I use MS Office 2019 which does not have FILTER function, but fortunately, we use Office 365 at work.
    PPS: Now I see that you probably added somehow (?) this function info the attached file.
    So thank you VERY MUCH !!!
    Reply With QuoteRegister To Reply
  10. 04-20-2022, 07:51 AM #10
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,025

    Re: How to join data (column B) based on grouped duplicate values (column A)

    That was fortunate... I didn't even notice that you are using 2019....

    You're welcome.


    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  11. 04-20-2022, 07:55 AM #11
    Marcel_G
    Marcel_G is offline
    Registered User
    Join Date
    04-20-2022
    Location
    Prague, Czechia
    MS-Off Ver
    MS Office 2019
    Posts
    6

    Re: How to join data (column B) based on grouped duplicate values (column A)

    Done ...
    Thank you for the instructions!
    Last edited by Marcel_G; 04-20-2022 at 07:56 AM. Reason: Typo
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Filtering specific values in a column based off grouped features in other columns
    By fpkid1 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-25-2021, 08:45 PM
  2. [SOLVED] Removing duplicate rows based on single column duplicate values
    By Miasav90 in forum Excel General
    Replies: 7
    Last Post: 09-23-2021, 08:40 AM
  3. [SOLVED] Duplicate Data in Rows grouped with adjacent data transposed into Column
    By KBarnard01 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-22-2020, 10:23 AM
  4. [SOLVED] Total values in one column based on duplicate copies in another column
    By grim72 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-19-2018, 02:31 PM
  5. Remove rows with duplicate values in one column based on value of another column
    By jolleyje in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-15-2013, 06:20 AM
  6. How can I change duplicate values in a column based on the value in an another column?
    By JKING in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 07-30-2012, 04:21 PM
  7. Full outer join of data across two workbooks based on a common column
    By godric7gt in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-11-2012, 02:23 AM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1