Home› Ready Room

Google sheets help

[RotP] Amadeus[RotP] Amadeus ✭✭
in Ready Room
Hi,

as the title says, I could do with some assistance with Google sheets. Not sure if this the right forum or section, but I'm sure some of you have good knowledge of Google sheets.

Using the lockdown time for something useful, I thought I can make a spreadsheet for the crew selection for factions events (which I used to do by hand previously). I'm a total beginner, but I'm happy to invest some time to learn new skills.

I'm facing a problem at the moment which I have not managed to solve myself. I have a crew list, and the 6 standard skills (see picture below). I managed to retrieve the highest skill, but would like to have what skill it is in the next column (E g SCI or DIP). A bit trickier would be if 2 or more skills are the highest (should be very rare though), then I would like to have something like SCI/DIP in the next column.

For 6 skills I could possibly work around somehow (with if clauses), but I also want to do combined skills (which are 30 columns).

Anyone can point me in the right direction?

Thanks.

ub40v8zmtojn.png


Comments

  • MiT SanoaMiT Sanoa ✭✭✭✭✭
    edited April 2020
    For the first part I have a solution:

    i6yob5w2vqqn.jpeg

    For the field P3 the function is:
    =INDEX($J$2:$O$2,0,MATCH(MAX(J3:O3),J3:O3,0))

    Two equal stats are pretty academic, no crew having this comes to mind.
    Wir, die Mirror Tribbles [MiT] haben freie Plätze zu vergeben. Kein Zwang und kein Stress, dafür aber Spaß, Discord und eine nette, hilfsbereite Gemeinschaft, incl. voll ausgebauter Starbase und täglich 700 ISM.
  • Dirk GundersonDirk Gunderson ✭✭✭✭✭
    [Mirror] Sanoa wrote: »
    Two equal stats are pretty academic, no crew having this comes to mind.

    It’s not common but it is possible:

    ezp5fqtavslx.jpeg

    Whether it’s possible when fully leveled is another matter. :)
  • MiT SanoaMiT Sanoa ✭✭✭✭✭
    Dirk Gunderson wrote: »
    [Mirror] Sanoa wrote: »
    Two equal stats are pretty academic, no crew having this comes to mind.

    It’s not common but it is possible:
    ezp5fqtavslx.jpeg

    Whether it’s possible when fully leveled is another matter. :)

    Haha! Like it!
    Wir, die Mirror Tribbles [MiT] haben freie Plätze zu vergeben. Kein Zwang und kein Stress, dafür aber Spaß, Discord und eine nette, hilfsbereite Gemeinschaft, incl. voll ausgebauter Starbase und täglich 700 ISM.
  • [RotP] Amadeus[RotP] Amadeus ✭✭
    [Mirror] Sanoa wrote: »
    For the first part I have a solution:

    i6yob5w2vqqn.jpeg

    For the field P3 the function is:
    =INDEX($J$2:$O$2,0,MATCH(MAX(J3:O3),J3:O3,0))

    Two equal stats are pretty academic, no crew having this comes to mind.

    Thanks [Mirror] Sanoa!

    INDEX+MATCH was my first try. It did give me the relevant skill (only the first one found though). Unfortunately you can't get it to work in a "arrayformular", so not really any use for me. I want to have the "selection" automatised.

    Anyone else have a idea? Seems like a real simple problem, but quite challenging.
  • [RotP] Amadeus[RotP] Amadeus ✭✭
    I managed to get all skills if the max one occurs more than once with:

    =TRANSPOSE(TRANSPOSE(JOIN(",",FILTER($B$1:$G$1,B2:G2=H2))))

    But I didn't figure out how to connect this with "arrayformular".

    I made a test sheet if anyone wants to have a look:

    https://docs.google.com/spreadsheets/d/1RjR00dV7Oy2fGfAb3t7XCLh0ALgKZvIfXHgfecBjz88/edit?usp=sharing

    Any help would be appreciated. Or if someone knows a better place to ask, let me know.
  • [RotP] Amadeus[RotP] Amadeus ✭✭
    If anyone is interested, the solution for the problem was:

    =ARRAYFORMULA(RegexReplace(Trim(Transpose(Query(Transpose(IF(B2:G="",,IF(B2:G=H2:H,B1:G1,))),,5000))),"\s",", ")) - see test sheet
  • guest_1300185139768320guest_1300185139768320 ✭
    Hi, I am new to google sheets and I could really use some help.

    Recently I have this issue that when I type in any box, I get a drop down menu in a different language to select from. I have looked online but have not found a remedy. Can someone please help me?

    Much thanks!!

    4h48miq95umu.png

  • [RotP] Amadeus[RotP] Amadeus ✭✭
    guest_1300185139768320 wrote: »
    Hi, I am new to google sheets and I could really use some help.

    Recently I have this issue that when I type in any box, I get a drop down menu in a different language to select from. I have looked online but have not found a remedy. Can someone please help me?

    Much thanks!!

    4h48miq95umu.png

    Sorry, but not the proficient myself. I suggest to ask in the Google sheets support forum (https://support.google.com/docs/community?hl=en), the community is really helpful.
  • guest_1309694469835776guest_1309694469835776 ✭
    edited December 2020
    Apps Script question:

    I all of a sudden get the bellow error message.
    "TypeError: Cannot read property 'Range' of undefined"
    Can anyone help me fix it?

    //* Sync Data Valadation on tabs
    function sync(e) {
    var sheetName = ; // name of sheets
    var cell = ; // corresponding cells to sync
    var r = e.range
    var ss = e.source;
    var value = (e.value === undefined ? '' : e.value);
    var i = sheetName.indexOf(r.getSheet().getSheetName());
    if (i > -1 && r.getA1Notation() == cell) {
    for (var k = 0; k < sheetName.length; k++) {
    if (k != i) {
    ss.getSheetByName(sheetName[k]).getRange(cell[k]).setValue(value);
    }
    }
    }
    }
Sign In or Register to comment.