Merge cells based on the no. of cells that has values

Hi,

I am new to excel programming. Kindly help me in resolving my problems.

The requirement is to dynamically generate a template in sheet1 based on the data available in sheet2. I have attached a sample excel file.

For the given data in sheet2, when a button labeled 'Generate Template' is clicked, it should generate the sheet 1 as per the below requirement.

The script should identify the no. of unique values available in column C. For each value available in column C, it should identify the number of non-empty cells available from E in the corresponding row. For example, for the abc.doc available in C3, there are 2 validations from E3:G3. Based on this, the script should merge B2:C2 (2 cells).

Kindly help me in implementing it.

1. How do I identify the unique values available in a column - in specific column C ?
2. How do I identify how many cells have value from column E in a row ?
3. How do I dynamically merge cells based on the number of cells that have value after column E in sheet 2 ?

Thanks & Regards
Yuvi

Hi Ajay,

In the same sample sheet, for example, for the abc.doc available in C3 of Sheet 2, there are 2 validations from E3:G3. Now, as there are 2 validations available for C3 in sheet2, I want to merge 2 cells B2:C2 in sheet1.

If there were say 4 validations available for C3 in Sheet 2, I have to merge B2:E2 and set the value as abc.doc.

Thanks
Yuvi

You can use Offset to select the cells relative to a cell, and MergeCells to merge them.
For example, following code merges cell B7 and the cell in same row (0) and next column (1). i.e. cell C7.

Please Login or Register  to view this content.

hth
Ajay

Thanks Ajay.

I could implement the excel the following.

1. Identifying the cells that has values in column C in sheet 2
2. Calculating the number of validations the corresponding cell in column C has. For example, in the attached excel, in sheet 2 there are 5 validations for the cell C3.

Based on this, I have written the code for command1_click in sheet2 to display the validations in the sheet1. Now, I am stuck with 2 more problems.

1. The fileName available in column C of sheet 2 should be the title for the corresponding validations in the sheet1. For example, for cells B4:F4 the corresponding B3:F3 should be merged and should have the values as that of C3 in sheet2, i.e. xx.jsp.

2. Also, In cell following the validations in sheet1, G5:G25, L5:L25, I have to populate a list of values dynamically.

Both the requirements have been highlighted in yellow in Sheet1 of attached excel.

Help me in resolving these !

Thanks
Yuvi

If I understood your requirements properly,

1. The fileName available in column C of sheet 2 should be the title for the corresponding validations in the sheet1. For example, for cells B4:F4 the corresponding B3:F3 should be merged and should have the values as that of C3 in sheet2, i.e. xx.jsp.

Please Login or Register  to view this content.

(This assumes you have already merged B3:F3)

2. Also, In cell following the validations in sheet1, G5:G25, L5:L25, I have to populate a list of values dynamically.

1. Create a named range
http://spreadsheets.about.com/od/exc...amed_range.htm
2. Select cell where you want list of values(G5, for example). Go to menu option Data -> Data Validation. On "Settings" tab in pop up, choose "List" in "Allow" drop down and set Source as "=Mylist" (without the quotes), where Mylist is the name of named range created above.

hth
Ajay

Hi Ajay,

1. The cells are not merged default. I have to programmatically merge it and add the text to the merged cells.

2. The dynamic value population should happen dynamically. The cells in which the list of values are to be populated can be determined only at run time. Hence, I have programmatically populate the list of values into a cell.

Regards
Yuvi