Concatenating Columns values

**sarathyvb** · 10-24-2007, 04:41 PM

I'm new to VB and want to know how does it work for merging cells with common values and concatenate values is they are different in an excel sheet.

For ex below is the sheet with the values

A B C D E
1 2 3 4 5
1 2 4 5 6
1 2 4 5 6

and I want the script to produce an output like

A
1

B
2

C
3,4

D
4,5

E
5,6

Can anyone please help me in achieving the above result using vb script.

Thanks in advance.

**Dav** · 10-24-2007, 06:00 PM

I am nor sure what the result you want is from the example, can you restate the problem, the solution will be straightforward i think , but i am not clear what you want exactly

Regards

dav

**sarathyvb** · 10-24-2007, 06:24 PM

The requirement is to check the number of cells that are common in column A and Column B, merge them to one row and concatenate the rest of the column values using "," for the other cells C, D and E.

Attached zip file has an excel sheet in which sheet1 has the actual values and sheet2 has the values combined values for which I'm looking for. And I need the vb script for doing so.

**Dav** · 10-25-2007, 06:32 AM

Apart from the fact you have the id number for 2 of your values in the sheet, is this what you require

Let me know

Regards

Dav

**sarathyvb** · 10-25-2007, 10:55 AM

Sorry, I was not able to grasp your point. But to make it clear here is what I want, in an excel sheet I have to look up for column values in A and B with similar values and if found those should be made as a single row by concatenating the rest of the column values using comma.

Hope this helps. Also have attached a jpg for better understanding, where table 1 is the data that I receive and table 2 should be the output created by script.

**Dav** · 10-25-2007, 11:04 AM

I have added a button

I assume you have the excel options set to allow macros

Click the button on sheet 2

Does that help

regards

Dav

**sarathyvb** · 10-25-2007, 11:39 AM

Dav,

This is what I was looking for and thanks a ton for helping me out with the script.

If possible can you help me in doing the same by using a for loop for columns also as you did for rows.

Anyways Thanks again and appreciated for getting this done.

**Dav** · 10-25-2007, 12:06 PM

columns are much the same as rows. what are you wanting to do?

Regards

Dav

**sarathyvb** · 10-25-2007, 01:37 PM

Dav thanks for providing me the macro.

Can anyone please help me in resolving the problem in the below script, the concatenating part is working fine using the Match function, but the problem I have is I'm not sure how to delete the rows that have been concatenated. I tried using the "ActiveCell.EntireRow.Delete" but it is not working as expected. If someone could help me out in this it will be great.

Thanks in advance.

Please Login or Register  to view this content.

**sarathyvb** · 10-30-2007, 01:40 PM

Can anyone please have a look at the above thread and let me know the appropriate delete statement that has to be used.

Thanks

**Reafidy** · 10-30-2007, 06:11 PM

You need to use code tags for your code.

It makes it a lot easier for us to read it. It gives me a headache looking at it like that so Im not going to but heres one way to delete rows:

Please Login or Register  to view this content.

**mikerickson** · 10-30-2007, 07:53 PM

I tried using the "ActiveCell.EntireRow.Delete" but it is not working as expected.

What does it do that you don't want or not do that you do want?

**shg** · 10-30-2007, 08:06 PM

This post has gone far afield of the original question.

Why are you creating a new instance of Excel instead of opening the workbook in the current instance?