-4

Now I'm using a formula to extract the data in company sheet. The sheet and formula I can get the data by the name of A1:A100 and get the data that contain "B1"; but now i also need to get the note of cell that contains B1. Is there any script that can can achieve this? By the way, there are many duplicate names and they all have data including B1; I want to extract all the datas with the same name; The current formula can only extract the data of the top one. Plz help me and I really need this on work. Thank u so much! sample

There are two question: 1. When there are duplicate values, it will arrange the extracted values into the same column, which will scramble the order. Can these duplicate values be arranged in the same row so that the order will not be wrong 2. What if the values not in "J" and "K" columns but in many columns? Here is a sample sheet, plz help me solve this. Thank u so much!

Here is the formula:

=BYROW (A1:A100, LAMBDA(each,IFNA( FILTER (INDEX(H2:AQ270, MATCH(each,H2:H270,0)), REGEXMATCH(INDEX(H2:AQ270, MATCH(each,H2:H270,0)),SUBSTITUTE(B1,".","\."))))))

Here is the sample sheet: https://docs.google.com/spreadsheets/d/1Sigx9Kmx5Ny41zC_Mq38k1Zxpnw-qNumbEUqqEoeL-o/edit#gid=0

Tedinoz
  • 5,911
  • 3
  • 25
  • 35
Danel Lau
  • 25
  • 5
  • 1
    Please edit your question to provide an example of a successful outcome based on your same data. – Tedinoz Mar 21 '23 at 00:47
  • Does this answer your question? [Is there a script can replace this formula and also get the note of cell](https://stackoverflow.com/questions/75780351/is-there-a-script-can-replace-this-formula-and-also-get-the-note-of-cell) – Twilight Mar 21 '23 at 02:32
  • nope. Tanaike has helped written a script but there are something wrong so he asked me to write a new question so that he can help me resolve it. – Danel Lau Mar 21 '23 at 04:16
  • I think that I could understand your 2nd question. But, because of my poor English skill, I cannot imagine your 1st question of `1. When there are duplicate values, it will arrange the extracted values into the same column, which will scramble the order. Can these duplicate values be arranged in the same row so that the order will not be wrong`. In order to correctly understand your 1st question, can you include your expected result in your sample image? Or, from your sample image, do you want to put all values in column "D"? First, I would like to correctly understand your 2 question. – Tanaike Mar 21 '23 at 05:04
  • Thanks for ur reply Tanaike! Sorry my English grammar is also not good. I will explain it to u: here's the example: https://i.imgur.com/huEh1Ft.png William is also in J7 and J8；but after using the script, the results of William is in D2 and D3. This made other result all wrong; u can see the result of Jeffrey is wrong. Have u understand my question? Thank u so much for helping me. – Danel Lau Mar 21 '23 at 05:21
  • Thank you for replying. I apologize for my poor English skill, again. Now, I noticed your reply. I apologize for this. Unfortunately, from your reply, I couldn't still understand your 1st question. In order to correctly understand your 1st question, can you include your expected result in your sample image? Or, from your sample image, do you want to put all values in column "D"? First, I would like to correctly understand your 2 questions. – Tanaike Mar 21 '23 at 12:23
  • Its ok Tanaike. If the name's not duplicated, it's ok to put value in column D; but if there's two or three same names it will get other values wrong, It's the wrong result: https://i.imgur.com/huEh1Ft.png; but this's right result: https://i.imgur.com/GGbh4bc.png can u understand my question? – Danel Lau Mar 21 '23 at 16:32
  • Thank you for replying. From your reply, I proposed a modified script as an answer. Please confirm it. – Tanaike Mar 22 '23 at 00:21

1 Answers1

0

From your following reply,

If the name's not duplicated, it's ok to put value in column D; but if there's two or three same names it will get other values wrong, It's the wrong result: https://i.stack.imgur.com/DqDum.png ; but this's right result: https://i.stack.imgur.com/v4HNi.png

how about the following modification?

Modified script:

function myFunction() {
  const sheetName = "Sheet1"; // Please set your sheet name.

  // Retrieve sheet and data range.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const range = sheet.getDataRange();

  // Retrieve notes.
  const notes = range.getNotes();

  // Retrieve values.
  const [[, v], ...values] = range.getValues();

  // Create an object for searching the values of column "A".
  const r = new RegExp(`^${v}`);
  const obj = values.reduce((o, [, , , , , , , h, , ...j], i) => {
    if (h) {
      j.forEach((e, l) => {
        if (e.toString() && r.test(e)) {
          const temp = { v: e, n: notes[i + 1][9 + l] };
          o[h] = o[h] ? [...o[h], temp] : [temp];
        }
      });
    }
    return o;
  }, {});

  // Create new values and notes.
  let newValues = values.map(([a]) => obj[a] ? obj[a].map(e => e.v) : []);
  let newNotes = values.map(([a]) => obj[a] ? obj[a].map(e => e.n) : []);
  const maxLen = Math.max(...newValues.map(r => r.length));
  newValues = newValues.map(r => [...r, ...Array(maxLen - r.length).fill(null)]);
  newNotes = newNotes.map(r => [...r, ...Array(maxLen - r.length).fill(null)]);

  // Put the new values and notes.
  sheet.getRange("D2:F" + sheet.getLastRow()).clearContent().clearNote();
  sheet.getRange(2, 4, newValues.length, newValues[0].length).setValues(newValues).setNotes(newNotes);
}

Note:

  • This sample script is for your provided Spreadsheet. When you change the Spreadsheet, this script might not be able to be used. Please be careful about this.
Tanaike
  • 181,128
  • 11
  • 97
  • 165