I’m working on a function to Merge cells in Excel and I’m struggling with the “nested” loop portion.
My goal is basically to have row lines go from left to right, so for example, in the below screenshots, you can see that the “Type” column is identical values, but there are 3x “merge areas” due to the column B having different values.
As it stands, my code works and detects an “overlap”, but it always merges the entire “2nd range”. This 2nd range needs to basically be re-checked for overlaps so that it too doesn’t overlap.
Example Data:
Desired Output:
Current Output:
Here is my code, note there might be some custom functions and/or stuff that is related to Excel that isn’t 100% necessary, but the “context” should be clear hopefully from the code. If needed to trim down more or clarify, let me know via comments!
You can see my attempt at the “nested loop” is commented out via /* & */ and the “sorta working” code is below that //Second Merge - This containers further overlaps and needs another nested loop/check
var ws = context.workbook.worksheets.getActiveWorksheet();
var Used_Rng_And_Props = await Ranges.Get_Used_Rng_And_Props(context, ws)
var Merges_Arr_Of_Objs = []
var Merges_Obj = {}
for (var column_index = 0; column_index < Used_Rng_And_Props.columnCount; ++column_index) {
for (var row_index = 0; row_index < Used_Rng_And_Props.rowCount; ++row_index) {
var val = Used_Rng_And_Props.values[row_index][column_index]
if (row_index + 1 < Used_Rng_And_Props.rowCount) {
var next_val = Used_Rng_And_Props.values[row_index + 1][column_index]
} else {
var next_val = null
}
if (val == next_val) {
for (var merge_check_row_index = row_index + 1; merge_check_row_index < Used_Rng_And_Props.rowCount; ++merge_check_row_index) {
var merge_check_row_index_val = Used_Rng_And_Props.values[merge_check_row_index][column_index]
if (merge_check_row_index_val != val || merge_check_row_index == Used_Rng_And_Props.rowCount - 1) {
if (merge_check_row_index == Used_Rng_And_Props.rowCount - 1 && val == Used_Rng_And_Props.values[Used_Rng_And_Props.rowCount - 1][column_index]) {
var rng_row_count = merge_check_row_index - row_index + 1
} else {
var rng_row_count = merge_check_row_index - row_index
}
var rng_start_row_index = row_index
var rng_end_row_index = row_index + rng_row_count - 1
var rng = ws.getRangeByIndexes(rng_start_row_index, column_index, rng_row_count, 1)
var Col_Letter = await Ranges.Get_Col_Letters_From_Index(column_index)
var obj = Merges_Obj[Col_Letter]
if (obj == undefined) {
Merges_Obj[Col_Letter] = {}
obj = Merges_Obj[Col_Letter]
obj[0] = {}
obj[0]['start'] = rng_start_row_index
obj[0]['end'] = rng_end_row_index
} else {
var int = Object.keys(obj).length
obj[int] = {}
obj[int]['start'] = rng_start_row_index
obj[int]['end'] = rng_end_row_index
}
if (column_index > 0) {
var overlap_merge_bool = false
var Col_Letter = await Ranges.Get_Col_Letters_From_Index(column_index - 1)
var column_index_merges_obj = Merges_Obj[Col_Letter]
if (column_index_merges_obj != undefined) {
var keys = Object.keys(column_index_merges_obj)
for (var ki = 0; ki < keys.length; ++ki) {
var obj = column_index_merges_obj[ki]
var merge_start_row_index = obj['start']
var merge_end_row_index = obj['end']
if (rng_end_row_index > merge_start_row_index && rng_end_row_index > merge_end_row_index && row_index < merge_end_row_index) {
console.log('overlap merge:')
console.log('obj:')
console.log(obj)
console.log('row_index:' + row_index + " column_index:" + column_index + " merge_check_row_index:" + merge_check_row_index)
console.log('rng_end_row_index:' + rng_end_row_index)
overlap_merge_bool = true
ki = keys.length + 1
}
}
}
if (overlap_merge_bool == false) {
rng.merge()
await Format_Merged_Area(context, rng)
} else {
console.log('column_index_merges_obj:')
console.log(column_index_merges_obj)
//First Merge - This is good
var first_merge_rng_row_count = merge_end_row_index - row_index + 1
var first_merge_rng_row_end_index = row_index + first_merge_rng_row_count - 1
console.log('first_merge_rng_row_count:' + first_merge_rng_row_count + " first_merge_rng_row_end_index:" + first_merge_rng_row_end_index)
rng = ws.getRangeByIndexes(row_index, column_index, first_merge_rng_row_count, 1)
rng.merge()
await Format_Merged_Area(context, rng)
/*
var keys = Object.keys(column_index_merges_obj)
for (var ki = 0; ki < keys.length; ++ki) {
var obj = column_index_merges_obj[ki]
var merge_start_row_index = obj['start']
var merge_end_row_index = obj['end']
if (ki + 1 <= keys.length) { //Not correct>? Fixed by Undefined Check
var next_obj = column_index_merges_obj[ki + 1]
if (next_obj != undefined) {
if (next_obj['end'] > rng_end_row_index - merge_end_row_index) {
console.log('WereIn')
var second_merge_rng_row_count = rng_end_row_index - next_obj['start'] // + 1
console.log('second_merge_rng_row_count:' + second_merge_rng_row_count)
var start_row = next_obj['start'] //+ 1
console.log('start_row:' + start_row)
rng = ws.getRangeByIndexes(start_row, column_index, second_merge_rng_row_count, 1)
rng.merge()
await Format_Merged_Area(context, rng)
}
}
}
}
*/
//Second Merge - This containers further overlaps and needs another nested loop/check
console.log('merge_end_row_index:' + merge_end_row_index)
var second_merge_rng_row_count = rng_end_row_index - merge_end_row_index
rng = ws.getRangeByIndexes(merge_end_row_index + 1, column_index, second_merge_rng_row_count, 1)
rng.merge()
await Format_Merged_Area(context, rng)
}
} else {
rng.merge()
await Format_Merged_Area(context, rng)
}
row_index = merge_check_row_index - 1
merge_check_row_index = Used_Rng_And_Props.rowCount + 1
}
}
}
}
}