google-apps-scriptgoogle-sheetsgoogle-sheets-formulaexcel-indirect

How To Add The Ordinal Count To Values Set In Horizontal Non-Contiguous Ranges In Google Sheets With A Formula Or A Script? EDIT ADDRESS FUNCTION

Problem:

I'm trying to add each ordinal reference to a set of repeating values in each cells just above each value.

The values are organized in horizontal and non-contiguous order.

The illustration example I show below is simple for testing purposes, but the end use should be for hundreds of values/ranges, so it would be optimal to use a script or a simplified version of the formula I found.

Illustration Example:

Ordinals from Non-Contiguous Ranges

Other Related Question and Solution:

I found that question and answers that address the same question but for vertical and contiguous values using the following formula as solution:

=COUNTIF(A$1:A1,A1)

=COUNTIF(A$1:A1,A1)&MID("thstndrdth",MIN(9,2*RIGHT(COUNTIF(A$1:A1,A1))*(MOD(COUNTIF(A$1:A1,A1)-11,100)>2)+1),2)

Calculate ordinal number of replicates

My Formula So Far:

=TRANSPOSE(INDIRECT($P$21&(SUM(Q21))&":"&$P$21&(SUM(Q21,I22)-1)))

=TRANSPOSE(INDIRECT($P$21&(SUM(Q21,I22))&":"&$P$21&(SUM(Q21,I22,I26)-1)))

=TRANSPOSE(INDIRECT($P$21&(SUM(Q21,I22,I26))&":"&$P$21&(SUM(Q21,I22,I26,I30)-1)))

I use the above formula and need to copy-paste it in the cell immediately above the 1st cell of each horizontal range.

I need to reference each cell in the SUM Functions part because the spreadsheet will act as a template, with new data sets that will be different each time.

Therefore the cells need to return output in some dynamic way (can't hardcode them).

The formula problem is it requires an ever growing number of cells reference as we get to new ranges. It becomes difficult for hundreds of horizontal ranges, because of the growing inline cells to add to the SUM Functions. It is also prone to errors. And possibly it can break if rows or columns are added afterwards.

Trials:

I originally didn't think of using the INDIRECT Function (I never needed before). But I don't know any other Google Sheets function able to achieve the end results in a simpler way.

Questions:

What way to avoid the SUM Function method for the same result would you suggest, for a formula solution?

For a formula, what simpler function-s than the INDIRECT and/or SUM would be more efficient?

I also thought of using a script for doing that, but I can't put the whole idea into a manageable script concept process. What would you suggest if a script would be more appropriate?

Many thanks for your help!

The Sample Sheet:

Sample Sheet

EDIT:

I just found about the ADDRESS Function from this answer by Player0 (to help greatly simplify the INDIRECT function row and column references):

Google Sheets: INDIRECT() with a Range

ADDRESS Function Example

References:

Excel INDIRECT Function

Google Sheets ADDRESS Function

Solution

  • I was able to create a script to show the ordinal number of the replicates but is only respective to one range. EDIT: I have modified it to also accept multiple row ranges. See updated answer below:

    Script:

    function showOrdinal(range) {
  range = "A4:E12";
  var values = SpreadsheetApp.getActiveSheet().getRange(range).getValues();
  var output = [];
  var order, subTotal;
  values.forEach((x, i) => {
    if(x.flat().filter(String).length) {
      subTotal = values.slice(0, i + 1).flat().filter(String);
      order = x.filter(String);
      if (order[0] != '-') {
        var row = order.map(x => {
          return getNumberWithOrdinal(subTotal.filter(e => e == x).length);
        })
        row = [...row, ...Array(x.length - row.length)];
        output.push(row);
        if(output.length > 2)
          output.splice(output.length - 2, 1);
      }
      order = [...order, ...Array(x.length - order.length)];
      output.push(order)
    }
    else
      output.push(x);
  });

  // console.log(output);

  SpreadsheetApp.getActiveSheet().getRange(3, 21, output.length, output[0].length).setValues(output);
  // return output;


}

// get ordinal number
// https://stackoverflow.com/a/31615643/14606045
function getNumberWithOrdinal(n) {
  var s = ["th", "st", "nd", "rd"],
      v = n % 100;
  return n + (s[(v - 20) % 10] || s[v] || s[0]);
}

    Output:

    output

    Note: