+ Reply to Thread
Results 1 to 41 of 41

Join Cell Without Duplicate text and with Same reference.

  1. #1
    Registered User
    Join Date
    07-13-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    16

    Unhappy Join Cell Without Duplicate text and with Same reference.

    Attachment 581875


    Hi Everyone!
    Can You Help me. I run store and i want to categories my product base on product id in to four age groups.
    I have Attach the file and Image.
    I have manually created desired output in column "E".
    is there any way to get same result in "D" with any formula? Product ID are same for different Sizes. Main is product and Sub are different sizes.
    This is a sample file. Original file contains 5000 Product IDs.
    Last edited by Gajanan.Jadhav; 07-14-2018 at 05:09 AM.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    48,990

    Re: Join Cell Without Duplicate text and with Same reference.

    Firstly, why are there duplicates in the list (e.g. rows 24 and 25)?
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  3. #3
    Registered User
    Join Date
    07-13-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    16

    Re: Join Cell Without Duplicate text and with Same reference.

    Because This same product has different sizes which fits to that categories,
    I have applied vlookup for that and past special.
    age 1-2 : Kid
    age 2-7 : Toddler (This Product Goes In KID and Toddler )
    age 6-11: Younger Kid (This Product goes in Toddler and Older Kid.)
    age 10-16 : Older Kid.
    Last edited by Gajanan.Jadhav; 07-14-2018 at 05:22 AM.

  4. #4
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Join Cell Without Duplicate text and with Same reference.

    so this table is correct?

    Age Range
    Kid 1-2
    Kid 2-7
    Toddler 2-7
    Toddler 6-11
    Younger Kid 6-11
    Older Kid 6-11
    Older Kid 10-16
    sandy
    How to create an editor for Power Query with Notepad++ (tutorial)
    How to create timeline project with vertical today marker (2010, 2013, 2016 etc...) (examples)
    Tips for Excellent Spreadsheets

    What makes learning so hard is the amount of knowledge you have to unlearn
    Why is my program not doing what I expect?
    Because you set the wrong expectations. Rewire your brain

  5. #5
    Registered User
    Join Date
    07-13-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    16

    Re: Join Cell Without Duplicate text and with Same reference.

    Thank Sandy For reply>
    I have created column E manually.
    I want same result in column D with formula or logic.
    I am new to excel and trying to get that result from last week.
    I have applied vlookup to get column C.
    It is More Complicated as i have multiple range in database.

    Attachment 581952


    I have Uploaded Simplified excel.
    I just want to create D with all with reference to Product Id. Te result I want is in E which i have entered manually. But original file contains 5000 products to manually it is not possible and may cause error.

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Join Cell Without Duplicate text and with Same reference.

    I am confused. There is no age for each product i your attachment. There is in the non-editable picture in the last post. Please repost your sheet with all relevant information!!
    Glenn



  7. #7
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Cool Re: Join Cell Without Duplicate text and with Same reference.

    I'll wait for new sample file but for now you can check this (black table)

  8. #8
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    48,990

    Re: Join Cell Without Duplicate text and with Same reference.

    Is this what you are aiming for?

    Excel 2016 (Windows) 32 bit
    A
    B
    1
    Product ID Count
    2
    403350668 Kid|Younger Kid
    3
    422496541 Toddler|Younger Kid|Older Kid
    4
    568612016 Toddler|Kid
    5
    738431461 Kid|Toddler|Younger Kid|Older Kid
    6
    1793168769 Kid|Toddler|Younger Kid
    7
    3891491330 Kid|Younger Kid|Toddler
    8
    4968223609 Kid|Younger Kid|Older Kid|Toddler
    9
    5715146279 Kid|Younger Kid
    10
    7192251101 Toddler|Kid|Younger Kid|Older Kid
    11
    9890723832 Younger Kid|Toddler|Kid
    Sheet: Sheet4
    Attached Files Attached Files
    Last edited by AliGW; 07-15-2018 at 04:05 AM.

  9. #9
    Registered User
    Join Date
    07-13-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    16

    Re: Join Cell Without Duplicate text and with Same reference.

    Thanx Glenn Kennedy for reply.
    I have simplified the age range in first excel file to avoid confusion. Original file age ranges are more confusing.
    I have attach updated excel and range table with this reply

  10. #10
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    48,990

    Re: Join Cell Without Duplicate text and with Same reference.

    Have a look at the solution I offered - is it what you want? I see no need for duplicate rows.

  11. #11
    Registered User
    Join Date
    07-13-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    16

    Re: Join Cell Without Duplicate text and with Same reference.

    Thnax AliGW ,
    There are near about 5000 product IDs.
    Can You tell me how you removed duplicates ?
    I want logic so I can apply to those product ID and get all age categories.

  12. #12
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    48,990

    Re: Join Cell Without Duplicate text and with Same reference.

    The attachment shows you how using PowerQuery. There is also the Remove Duplicates option on the Data ribbon in Excel.

    This is the M code needed in PQ:

    Please Login or Register  to view this content.

  13. #13
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Join Cell Without Duplicate text and with Same reference.

    see post #7

  14. #14
    Registered User
    Join Date
    07-13-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    16

    Re: Join Cell Without Duplicate text and with Same reference.

    Original File have complicated age ranges . so I have just uploaded simplified excel file . File with variable age ranges are attach with this post

  15. #15
    Registered User
    Join Date
    07-13-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    16

    Re: Join Cell Without Duplicate text and with Same reference.

    As i said. I am new to excel . Can you tell me how to appy this PowerQuery and Can You suggest any online course? Is this forum provide any course for newbies ?

  16. #16
    Registered User
    Join Date
    07-13-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    16

    Re: Join Cell Without Duplicate text and with Same reference.

    #7 Thanx alot Sandy. Can you explain how you did that?

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    48,990

    Re: Join Cell Without Duplicate text and with Same reference.

    Try the updated file.
    Attached Files Attached Files

  18. #18
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Join Cell Without Duplicate text and with Same reference.

    That's odd... My reply vanished.

    Please Login or Register  to view this content.
    How to install your new code
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Press Alt+F11 to open the Visual Basic Editor
    Choose Insert > Module
    Edit > Paste the macro into the module that appeared
    Close the VBEditor
    Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


    Then an array formula:
    =ConcatAll(IF($A$2:$A$38=A2,$D$2:$D$38,"")," ¦ ",TRUE)

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Attached Files Attached Files

  19. #19
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Join Cell Without Duplicate text and with Same reference.

    Sure but tell me first is this correct for you?

    edit: you've Excel 2016 (hope Pro) so you have PowerQuery by default (Get&Transform)

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    48,990

    Re: Join Cell Without Duplicate text and with Same reference.

    @ Glenn - do you mean post #6? If so, it's still there ... It's the only other one you have made to this thread that I have seen.

  21. #21
    Registered User
    Join Date
    07-13-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    16

    Re: Join Cell Without Duplicate text and with Same reference.

    Quote Originally Posted by sandy666 View Post
    I'll wait for new sample file but for now you can check this (black table)
    Can You explain me how you to do that?

  22. #22
    Registered User
    Join Date
    07-13-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    16

    Re: Join Cell Without Duplicate text and with Same reference.

    Quote Originally Posted by Glenn Kennedy View Post
    That's odd... My reply vanished.

    Please Login or Register  to view this content.
    How to install your new code
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Press Alt+F11 to open the Visual Basic Editor
    Choose Insert > Module
    Edit > Paste the macro into the module that appeared
    Close the VBEditor
    Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


    Then an array formula:
    =ConcatAll(IF($A$2:$A$38=A2,$D$2:$D$38,"")," ¦ ",TRUE)

    Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

    You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

    Don't type the curly brackets yourself - it won't work...
    Thnx. I also wants to remove duplicate product IDS after adding all age groups.

  23. #23
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    48,990

    Re: Join Cell Without Duplicate text and with Same reference.

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below.

  24. #24
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    48,990

    Re: Join Cell Without Duplicate text and with Same reference.

    I think you need to decide whose solution you want to go with - Sandy's or Glenn's - and then get further details from there.

  25. #25
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Join Cell Without Duplicate text and with Same reference.

    Sure, I assume result is correct for you.
    Using PowerQuery (Get&Transform)

    Please Login or Register  to view this content.
    in short
    - mereged two tables
    - grouped ID
    - Extract distinct values separated by | to single cell

    I'm talking about post#1 and post#4
    with updated file Glenn is a winner
    Last edited by sandy666; 07-15-2018 at 04:45 AM.

  26. #26
    Registered User
    Join Date
    07-13-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    16

    Re: Join Cell Without Duplicate text and with Same reference.

    Thanx Eveveyone.
    I am totally new to excel and just got a job at store. My job involves working with excel and I wants to learn it. I am very thankfull to all. I will upload the original store file with owners permission.
    As i am new to forum please forgive me if i made any mistake.

    I am now trying to understand what you all did with file.
    I will post my query if i dont understand anyting.

    thanks all
    Last edited by Gajanan.Jadhav; 07-15-2018 at 04:47 AM.

  27. #27
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Join Cell Without Duplicate text and with Same reference.

    ?? Like this:

    =IF(COUNTIF($A$2:$A2,A2)=1,ConcatAll(IF($A$2:$A$38=A2,$D$2:$D$38,"")," ¦ ",TRUE),"")

    then filter and copy/paste values
    Attached Files Attached Files

  28. #28
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Join Cell Without Duplicate text and with Same reference.

    You are welcome
    Have a nice day

  29. #29
    Registered User
    Join Date
    07-13-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    16

    Re: Join Cell Without Duplicate text and with Same reference.

    I will ask my boss about which solution is correct. I will approach him with both solution.

  30. #30
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Join Cell Without Duplicate text and with Same reference.

    Sure, then we can choose one direction

  31. #31
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    48,990

    Re: Join Cell Without Duplicate text and with Same reference.

    There's a solution in post #17 that your boss may wish to consider, too, which has the duplicate rows removed.

  32. #32
    Registered User
    Join Date
    07-13-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    16

    Re: Join Cell Without Duplicate text and with Same reference.

    Quote Originally Posted by Glenn Kennedy View Post
    ?? Like this:

    =IF(COUNTIF($A$2:$A2,A2)=1,ConcatAll(IF($A$2:$A$38=A2,$D$2:$D$38,"")," ¦ ",TRUE),"")

    then filter and copy/paste values
    Why I'm getting #NAME error? In same sheet?

  33. #33
    Registered User
    Join Date
    07-13-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    16

    Re: Join Cell Without Duplicate text and with Same reference.

    Quote Originally Posted by AliGW View Post
    There's a solution in post #17 that your boss may wish to consider, too, which has the duplicate rows removed.
    I will tell you which solution my boss like once i discuss with him.

  34. #34
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    48,990

    Re: Join Cell Without Duplicate text and with Same reference.

    Thanks - that's fine. We shall wait to hear back from you.

  35. #35
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    48,990

    Re: Join Cell Without Duplicate text and with Same reference.

    Quote Originally Posted by Gajanan.Jadhav View Post
    Why I'm getting #NAME error? In same sheet?
    Did you set up the VBA code first?

  36. #36
    Forum Expert sandy666's Avatar
    Join Date
    02-05-2015
    Location
    Any Country
    MS-Off Ver
    farerwell
    Posts
    8,749

    Re: Join Cell Without Duplicate text and with Same reference.

    Quote Originally Posted by Gajanan.Jadhav View Post
    Why I'm getting #NAME error? In same sheet?
    You need to allow to run VBA (probably yellow bar on top) or you didn't read whole Glenn's post

  37. #37
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Join Cell Without Duplicate text and with Same reference.

    ... or macros are not enabled...

  38. #38
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Join Cell Without Duplicate text and with Same reference.

    I have added another formula, to make for a simpler (no manual filter) solution. You STILL need to enable macros, though.
    Attached Files Attached Files

  39. #39
    Registered User
    Join Date
    07-13-2018
    Location
    London, England
    MS-Off Ver
    2016
    Posts
    16

    Re: Join Cell Without Duplicate text and with Same reference.

    Thank You Very Much AliGW , Sandy and Glenn Kennedy. You saved my job. AliGW your solution work rocks. Glenn Thank for formula it saved alot time.

  40. #40
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    30,754

    Re: Join Cell Without Duplicate text and with Same reference.

    You're welcome.



    If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

    It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

  41. #41
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    48,990

    Re: Join Cell Without Duplicate text and with Same reference.

    Glad to have helped.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. Replies: 1
    Last Post: 10-09-2015, 06:27 PM
  2. Indirect Sheet reference with cell reference left function and text
    By teststrip in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-01-2015, 10:31 AM
  3. Need Formula to Join to Columns and Duplicate
    By mhedge in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-17-2015, 07:53 PM
  4. Join text & cell reference in a link.
    By Glenn Kennedy in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-23-2013, 09:32 AM
  5. join tables with duplicate records ??
    By okl in forum Access Tables & Databases
    Replies: 1
    Last Post: 11-16-2009, 03:40 PM
  6. join text from multiple cells to one cell
    By stoey in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-27-2009, 06:28 PM
  7. Delete Spaces and Join Text in Cell
    By Diggsy in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-04-2005, 11:05 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1