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.

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

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.

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!!

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

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

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.

Try the updated file.

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...

@ 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.

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.

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.

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

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

?? 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

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

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

Originally Posted by Gajanan.Jadhav

Why I'm getting #NAME error? In same sheet?

Did you set up the VBA code first?

... or macros are not enabled...

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

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.

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.