How to combine the data value if the base column has mutiple entries

[Sai Prasad]

Hi,

I have a list of duplicate Names with different values for a single name.

----------------------
| Name | Value |
----------------------
| ABC1 | Val A |
| ABC1 | Val B |
| ABC1 | Val C |
| FIJ2 | Val D |
| KLM3 | Val E |
| KLM3 | Val F |
| KLM3 | Val G |
| KLM3 | Val H |
----------------------

I want the data to be displayed in the below format

---------------------------------------
| Name | Value |
---------------------------------------
| ABC1 | Val A, Val B, Val C |
| FIJ2 | Val D |
| KLM3 | Val E, Val F, Val G, Val H |
---------------------------------------

I am using Excel 2010 and the data is huge. Please help...

Thanks,
Sai.

 

[Somendra Misra]

Hi Sai,

You want all values related to one name in one cell separated by comma or in different cells? Say A1 has name you want values in B1,B2,B3 or all values in B2 separated with comma?

Regards,

 

[Sai Prasad]

Hello Somendra,

The data should be in one cell
Ex: for ABC1 should be listed with Val A,Val B,Val C separated with comma.

Thanks,
Sai.

 

[Somendra Misra]

@Sai Prasad

I doubt Excel In-Built formula can not deliver such a result, are you OK with VBA?

Regards,

 

[Sai Prasad]

@Somendra Misra Sure....anything that helps

 

[Somendra Misra]

@Sai Prasad

See the file, Green Range is input, White over Blue range is output. I had created UDF to get the value with below syntax:

=concatValue(input name,Name,Value)

here input name should be a range as B2, Name and value should be range of input name and input value.

EDIT: changed the file.

Regards,

 

[Sai Prasad]

@Somendra Misra thanks a lot...it worked for me

 

[Somendra Misra]

Glad to help Sai, Welcome back!!!

Regards,