itil

ITIL Reporting

excel

ITIL 4

excel

Power BI

excel

Excel Formula | Macro | Script

Identify same values after finding words using an office script in an excel report

This script helps me to copy and paste data from another sheet after identifying identical values but not before to find some specific words. First, from the sheet1, it looks for the particular words (for my example, words starting with “INCxxx” in column D) then its categories (for my example, “NT” and “unix” in column E). So if the sheet1 has no “INCxxx”, it will do nothing if not, it will check if there are the words “NT” and/or “unix” and of course, it there are none, nothing will be done.

Once the first step is OK, from the sheet2, it will match if in column A, B, C and D have the same values. If yes, it will report back to the sheet1 as “NT unix” with its corresponding values. If no, it will do each “NT” and each “unix” separately. And to end, the script will sort the column A.

sheet1
macro excel
sheet2
macro excel
Result in sheet1
macro excel

 

When I use the script ?

When I have to find same values for some specific words in order to have them in one single row.

 

How to create the script ?

Read How to create, edit and select an Office Script in an excel report

 

How to create the button to associate it with the script ?

Read How to create a button and associated it to an Office Script in an excel report

 

How is/are the script(s) ?

Copy the code below and paste it into your script. You will see my comments in green if exist so follow the help to adapt to your need.

Option 1, recommended for 2 words maximum although it can be used with more:


function main(workbook: ExcelScript.Workbook) {
  let mainsheet = workbook.getActiveWorksheet();
  // change sheet2 by yours
  let sheet2 = workbook.getWorksheet("Sheet2");
  let lastrow1 = getLastRow1(mainsheet, mainsheet.getRange("A:A"));
  // change D2:D by yours
  let columnD = mainsheet.getRange(`D2:D${lastrow1}`).getRowCount();
  for (let i = 1; i <= columnD + 1; i++) {
    // change 3 (column D), 4 (column E), 0  (column A) and 1  (column B) by yours
    // note: 0 = column A, 1 = column B, 2 = column C, etc.
    let word1 = mainsheet.getCell(i, 3).getText().toLowerCase();
    let word2 = mainsheet.getCell(i, 4).getText().toLowerCase();
    let firstcell = mainsheet.getCell(i, 0).getAddress();
    let lastcell = mainsheet.getCell(i, 1).getAddress();
    // change incxxx by yours
    if (word1.includes("incxxx")) {
      let lastrow2 = getLastRow2(sheet2, sheet2.getRange("A:A"));
      // change E2:E by yours
      if (sheet2.getRange(`E2:E${lastrow2}`).find(word1, { completeMatch: true, matchCase: false })) {
        let columnE = sheet2.getRange(`E2:E${lastrow2}`).getRowCount();
        for (let j = 2; j <= columnE + 1; j++) {
          // change 4 if not column E
          let word3 = sheet2.getCell(j, 4).getText().toLowerCase();
            // change nt unix by yours
          if (word2.includes("nt unix") || word2.includes("unix nt")) {
            // change 0 (column A), 1 (column B), 2  (column C) and 3  (column D) by yours
            let value1 = sheet2.getCell(j, 0).getValue();
            let value2 = sheet2.getCell(j, 1).getValue();
            let value3 = sheet2.getCell(j, 2).getValue();
            let value4 = sheet2.getCell(j, 3).getValue();
            if (word1 == word3 && value1 == value3 && value2 == value4) {
              // to include 0 change > by >=
              if (value1 > 0) {
                let newrow1 = getLastRow1(mainsheet, mainsheet.getRange("A:A"));
                // change C, D, E and F by yours
                // change 0 (column A), 1  (column B) and 5  (column F) by yours
                mainsheet.getRange("C" + (newrow1 + 1)).copyFrom(sheet2.getCell(j, 0));
                mainsheet.getRange("D" + (newrow1 + 1)).copyFrom(sheet2.getCell(j, 1));
                mainsheet.getRange("E" + (newrow1 + 1)).setValue("NT unix");
                mainsheet.getRange("A" + (newrow1 + 1)).copyFrom(`${firstcell}:${lastcell}`);
                mainsheet.getRange("F" + (newrow1 + 1)).copyFrom(mainsheet.getCell(i, 5));
              }
            }
            if ((word1 == word3 && value1 != value3) || (word1 == word3 && value1 == value3 && value2 != value4)) {
              if (value1 > 0) {
                let newrow1 = getLastRow1(mainsheet, mainsheet.getRange("A:A"));
                mainsheet.getRange("C" + (newrow1 + 1)).copyFrom(sheet2.getCell(j, 0));
                mainsheet.getRange("D" + (newrow1 + 1)).copyFrom(sheet2.getCell(j, 1));
                mainsheet.getRange("E" + (newrow1 + 1)).setValue("NT");
                mainsheet.getRange("A" + (newrow1 + 1)).copyFrom(`${firstcell}:${lastcell}`);
                mainsheet.getRange("F" + (newrow1 + 1)).copyFrom(mainsheet.getCell(i, 5));
              }
            }
            if ((word1 == word3 && value1 != value3) || (word1 == word3 && value1 == value3 && value2 != value4)) {
              if (value3 > 0) {
                let newrow1 = getLastRow1(mainsheet, mainsheet.getRange("A:A"));
                mainsheet.getRange("C" + (newrow1 + 1)).copyFrom(sheet2.getCell(j, 2));
                mainsheet.getRange("D" + (newrow1 + 1)).copyFrom(sheet2.getCell(j, 3));
                mainsheet.getRange("E" + (newrow1 + 1)).setValue("unix");
                mainsheet.getRange("A" + (newrow1 + 1)).copyFrom(`${firstcell}:${lastcell}`);
                mainsheet.getRange("F" + (newrow1 + 1)).copyFrom(mainsheet.getCell(i, 5));
              }
            }
          } else {
            if (word2 == "nt" && word1 == word3) {
              let value1 = sheet2.getCell(j, 0).getValue();
              if (value1 > 0) {
                let newrow1 = getLastRow1(mainsheet, mainsheet.getRange("A:A"));
                mainsheet.getRange("C" + (newrow1 + 1)).copyFrom(sheet2.getCell(j, 0));
                mainsheet.getRange("D" + (newrow1 + 1)).copyFrom(sheet2.getCell(j, 1));
                mainsheet.getRange("E" + (newrow1 + 1)).setValue("NT");
                mainsheet.getRange("A" + (newrow1 + 1)).copyFrom(`${firstcell}:${lastcell}`);
                mainsheet.getRange("F" + (newrow1 + 1)).copyFrom(mainsheet.getCell(i, 5));
              }
            } else {
              if (word2 == "unix" && word1 == word3) {
                let value3 = sheet2.getCell(j, 2).getValue();
                if (value3 > 0) {
                  let newrow1 = getLastRow1(mainsheet, mainsheet.getRange("A:A"));
                  mainsheet.getRange("C" + (newrow1 + 1)).copyFrom(sheet2.getCell(j, 2));
                  mainsheet.getRange("D" + (newrow1 + 1)).copyFrom(sheet2.getCell(j, 3));
                  mainsheet.getRange("E" + (newrow1 + 1)).setValue("unix");
                  mainsheet.getRange("A" + (newrow1 + 1)).copyFrom(`${firstcell}:${lastcell}`);
                  mainsheet.getRange("F" + (newrow1 + 1)).copyFrom(mainsheet.getCell(i, 5));
                }
              }
            }
          }
        }
        let clcd1 = mainsheet.getCell(i, 0).getAddress();
        let clcd2 = mainsheet.getCell(i + 1, 0).getAddress();
        let clcd3 = getLastRow1(mainsheet, mainsheet.getRange("A:A"));
        mainsheet.getRange(`${clcd1}`).copyFrom(`${clcd2}:L${clcd3}`);
        // change F by yours
        mainsheet.getRange(`A${clcd3}:F${clcd3}`).clear(ExcelScript.ClearApplyTo.contents);
      }
    }
  }
  let finalrow1 = getLastRow1(mainsheet, mainsheet.getRange("A:A"));
  // change F by yours
  // key: 0 -> sort in column A, if sort in another column, change 0
  // for descending, change true by false
  mainsheet.getRange("A2:F" + finalrow1).getSort().apply([{ key: 0, ascending: true }], false, false, ExcelScript.SortOrientation.rows);
}
// change mainsheet by yours
function getLastRow1(mainsheet: ExcelScript.Worksheet, rng: ExcelScript.Range) {
  let lastCell1 = mainsheet.getCell(rng.getEntireColumn().getCellCount() - 1, rng.getColumnIndex());
  if (lastCell1.getValue().toString().length <= 0) { return lastCell1.getExtendedRange(ExcelScript.KeyboardDirection.up).getRowIndex() + 1; }
}
// change sheet2 by yours
function getLastRow2(sheet2: ExcelScript.Worksheet, rng: ExcelScript.Range) {
  let lastCell2 = sheet2.getCell(rng.getEntireColumn().getCellCount() - 1, rng.getColumnIndex());
  if (lastCell2.getValue().toString().length <= 0) { return lastCell2.getExtendedRange(ExcelScript.KeyboardDirection.up).getRowIndex() + 1; }
}

Option 2, recommended for 3 or more words (although it can be used with less):


function main(workbook: ExcelScript.Workbook) {
    let mainsheet = workbook.getActiveWorksheet();
    // change sheet2 by yours
    let sheet2 = workbook.getWorksheet("Sheet2");
    let lastrow1 = getLastRow1(mainsheet, mainsheet.getRange("A:A"));
    // change D2:D by yours
    let columnD = mainsheet.getRange(`D2:D${lastrow1}`).getRowCount();
    for (let i = 1; i <= columnD + 1; i++) {
        // change 3 (column D), 4 (column E), 0  (column A) and 1  (column B) by yours
        // note: 0 = column A, 1 = column B, 2 = column C, etc.
        let word1 = mainsheet.getCell(i, 3).getText().toLowerCase();
        let word2 = mainsheet.getCell(i, 4).getText().toLowerCase();
        let firstcell = mainsheet.getCell(i, 0).getAddress();
        let lastcell = mainsheet.getCell(i, 1).getAddress();
        // change incxxx by yours
        if (word1.includes("incxxx")) {
            let lastrow2 = getLastRow2(sheet2, sheet2.getRange("A:A"));
            // change E2:E by yours
            if (sheet2.getRange(`E2:E${lastrow2}`).find(word1, { completeMatch: true, matchCase: false })) {
                let columnE = sheet2.getRange(`E2:E${lastrow2}`).getRowCount();
                for (let j = 2; j <= columnE + 1; j++) {
                    // change 4 if not column E
                    let word3 = sheet2.getCell(j, 4).getText().toLowerCase();
                    // change nt unix by yours
                    if (word2.includes("nt") || word2.includes("unix")) {
                        // change 0 (column A), 1 (column B), 2  (column C) and 3  (column D) by yours
                        let value1 = sheet2.getCell(j, 0).getValue();
                        let value2 = sheet2.getCell(j, 1).getValue();
                        let value3 = sheet2.getCell(j, 2).getValue();
                        let value4 = sheet2.getCell(j, 3).getValue();
                        if (word2.includes("nt") && word2.includes("unix") && word1 == word3 && value1 == value3 && value2 == value4) {
                            // to include 0 change > by >=
                            if (value1 > 0) {
                                let newrow1 = getLastRow1(mainsheet, mainsheet.getRange("A:A"));
                                // change C, D, E and F by yours
                                // change 0 (column A), 1  (column B) and 5  (column F) by yours
                                mainsheet.getRange("C" + (newrow1 + 1)).copyFrom(sheet2.getCell(j, 0));
                                mainsheet.getRange("D" + (newrow1 + 1)).copyFrom(sheet2.getCell(j, 1));
                                mainsheet.getRange("E" + (newrow1 + 1)).setValue("NT unix");
                                mainsheet.getRange("A" + (newrow1 + 1)).copyFrom(`${firstcell}:${lastcell}`);
                                mainsheet.getRange("F" + (newrow1 + 1)).copyFrom(mainsheet.getCell(i, 5));
                            }
                        } else {
                            if (word2.includes("nt") && word2.includes("unix") && word1 == word3) {
                                if (value1 > 0) {
                                    let newrow1 = getLastRow1(mainsheet, mainsheet.getRange("A:A"));
                                    mainsheet.getRange("C" + (newrow1 + 1)).copyFrom(sheet2.getCell(j, 0));
                                    mainsheet.getRange("D" + (newrow1 + 1)).copyFrom(sheet2.getCell(j, 1));
                                    mainsheet.getRange("E" + (newrow1 + 1)).setValue("NT");
                                    mainsheet.getRange("A" + (newrow1 + 1)).copyFrom(`${firstcell}:${lastcell}`);
                                    mainsheet.getRange("F" + (newrow1 + 1)).copyFrom(mainsheet.getCell(i, 5));
                                }
                                if (value3 > 0) {
                                    let newrow1 = getLastRow1(mainsheet, mainsheet.getRange("A:A"));
                                    mainsheet.getRange("C" + (newrow1 + 1)).copyFrom(sheet2.getCell(j, 2));
                                    mainsheet.getRange("D" + (newrow1 + 1)).copyFrom(sheet2.getCell(j, 3));
                                    mainsheet.getRange("E" + (newrow1 + 1)).setValue("unix");
                                    mainsheet.getRange("A" + (newrow1 + 1)).copyFrom(`${firstcell}:${lastcell}`);
                                    mainsheet.getRange("F" + (newrow1 + 1)).copyFrom(mainsheet.getCell(i, 5));
                                }
                            } else {
                                if (word2 == "nt" && word1 == word3) {
                                    if (value1 > 0) {
                                        let newrow1 = getLastRow1(mainsheet, mainsheet.getRange("A:A"));
                                        mainsheet.getRange("C" + (newrow1 + 1)).copyFrom(sheet2.getCell(j, 0));
                                        mainsheet.getRange("D" + (newrow1 + 1)).copyFrom(sheet2.getCell(j, 1));
                                        mainsheet.getRange("E" + (newrow1 + 1)).setValue("NT");
                                        mainsheet.getRange("A" + (newrow1 + 1)).copyFrom(`${firstcell}:${lastcell}`);
                                        mainsheet.getRange("F" + (newrow1 + 1)).copyFrom(mainsheet.getCell(i, 5));
                                    }
                                } else {
                                    if (word2 == "unix" && word1 == word3) {
                                        if (value3 > 0) {
                                            let newrow1 = getLastRow1(mainsheet, mainsheet.getRange("A:A"));
                                            mainsheet.getRange("C" + (newrow1 + 1)).copyFrom(sheet2.getCell(j, 2));
                                            mainsheet.getRange("D" + (newrow1 + 1)).copyFrom(sheet2.getCell(j, 3));
                                            mainsheet.getRange("E" + (newrow1 + 1)).setValue("unix");
                                            mainsheet.getRange("A" + (newrow1 + 1)).copyFrom(`${firstcell}:${lastcell}`);
                                            mainsheet.getRange("F" + (newrow1 + 1)).copyFrom(mainsheet.getCell(i, 5));
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
                let clcd1 = mainsheet.getCell(i, 0).getAddress();
                let clcd2 = mainsheet.getCell(i + 1, 0).getAddress();
                let clcd3 = getLastRow1(mainsheet, mainsheet.getRange("A:A"));
                mainsheet.getRange(`${clcd1}`).copyFrom(`${clcd2}:L${clcd3}`);
                // change F by yours
                mainsheet.getRange(`A${clcd3}:F${clcd3}`).clear(ExcelScript.ClearApplyTo.contents);
            }
        }
    }
    let finalrow1 = getLastRow1(mainsheet, mainsheet.getRange("A:A"));
    // change F by yours
    // key: 0 -> sort in column A, if sort in another column, change 0
    // for descending, change true by false
    mainsheet.getRange("A2:F" + finalrow1).getSort().apply([{ key: 0, ascending: true }], false, false, ExcelScript.SortOrientation.rows);
}
// change mainsheet by yours
function getLastRow1(mainsheet: ExcelScript.Worksheet, rng: ExcelScript.Range) {
    let lastCell1 = mainsheet.getCell(rng.getEntireColumn().getCellCount() - 1, rng.getColumnIndex());
    if (lastCell1.getValue().toString().length <= 0) { return lastCell1.getExtendedRange(ExcelScript.KeyboardDirection.up).getRowIndex() + 1; }
}
// change sheet2 by yours
function getLastRow2(sheet2: ExcelScript.Worksheet, rng: ExcelScript.Range) {
    let lastCell2 = sheet2.getCell(rng.getEntireColumn().getCellCount() - 1, rng.getColumnIndex());
    if (lastCell2.getValue().toString().length <= 0) { return lastCell2.getExtendedRange(ExcelScript.KeyboardDirection.up).getRowIndex() + 1; }
}

Interesting Topics