I am having x unique values A column and different values in B and C column's. How can I concatenate the B and C values which are having a unique value in column A
I am having x unique values A column and different values in B and C column's. How can I concatenate the B and C values which are having a unique value in column A
Since the data is already sorted by column A, this is pretty easy.
1) In D2: =IF(A2=A3,D3&","&B2,B2)
2) Copy D2 down the dataset
3) In E2: =IF(A2=A1, "", D2)
4) Copy E2:E38
5) Click on D2 and do a PASTE SPECIAL > VALUES
6) Clear column E
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Thanks JBeaucaire
It is very usefull
Could you help if data is not sorted
Yes, new step 1) is
1) Sort the data by Column A.
with out sorting the Column A, is any another way to get the output
you are not with a simple formula,you will need code to do that especially since i think your list is longer than that
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
By sorting the Column A I can easily get the same out put, but I need the same output without sorting the Column A. below is code for by sorting the column A
1) In D2: =IF(A2=A3,D3&","&B2,B2)
2) Copy D2 down the dataset
3) In E2: =IF(A2=A1, "", D2)
4) Copy E2:E38
5) Click on D2 and do a PASTE SPECIAL > VALUES
6) Clear column E
how are you going to get all those in one cell? youl need loads of helper columns and concatenate them
For that any code or any script is there? if possible could you please post it.
no idea !i'll ask someone
Ok, wasn't as hard as I'd though with no VBA, just a change to the formulas:
1) In D2: =IF(ISNUMBER(MATCH(A2,A3:A10000, 0)), INDEX(D3:D10000, MATCH(A2,A3:A10000, 0))&","&B2,B2)
2) Copy D2 down the dataset
3) In E2: =IF(ISNUMBER(MATCH(A2, $A$1:$A1, 0)), "", D2)
4) Copy E2:E10000
5) Click on D2 and do a PASTE SPECIAL > VALUES
6) Clear column E
Make sure the part of the formula in red is far enough down to encompass the whole dataset.
Thank you very much jerry.
I've marked this thread as SOLVED for you.
Next time, select Thread Tools from the links above and mark this thread as SOLVED. Thanks.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks