Timeline for Formula for Matching Header to Value
Current License: CC BY-SA 4.0
19 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Mar 7 at 4:29 | vote | accept | chunky-coal | ||
| Mar 7 at 4:29 | comment | added | chunky-coal | Thank you so much!! This worked as expected. Sorry for the delay. I have marked this answer as resolved and you are a hero. | |
| Mar 2 at 11:44 | history | edited | Mayukh Bhattacharya | CC BY-SA 4.0 |
added 495 characters in body
|
| Mar 2 at 10:43 | comment | added | Mayukh Bhattacharya | @chunky-coal I have updated the formulas. | |
| Mar 2 at 10:42 | history | edited | Mayukh Bhattacharya | CC BY-SA 4.0 |
added 386 characters in body
|
| Mar 2 at 10:34 | history | edited | Mayukh Bhattacharya | CC BY-SA 4.0 |
deleted 1188 characters in body
|
| Mar 1 at 18:00 | comment | added | chunky-coal | No problem! Thank you for all of your hard work and assistance thus far. | |
| Mar 1 at 16:44 | comment | added | Mayukh Bhattacharya | Ok I will update brb! | |
| Mar 1 at 16:42 | comment | added | chunky-coal | Hm… let me ask you this: let’s say that a team isn’t in the array. For example, canucks wasn’t in Sheet2. How could we make it say “No Team Found”? I’m going to see if I can do an error check because I think I know what the issue may be. | |
| Mar 1 at 16:38 | comment | added | Mayukh Bhattacharya |
I am not sure how it is not working on your end, I am using the same formula: =IFERROR(CONCAT(UNIQUE(REPT(Sheet2!$A$1:$C$1,N(IFNA(TEXTSPLIT(C2,,", ")=Sheet2!$A$2:$C$5,0))))),"No Sport Found") it gives me an output. Please check screenshot
|
|
| Mar 1 at 16:28 | comment | added | chunky-coal | No problem! Not necessarily. Crimson-tide, bulldogs are under the same header yet still show blank. | |
| Mar 1 at 16:23 | comment | added | Mayukh Bhattacharya |
Sorry I did a typo there should be No Sport Found --> =IFERROR(CONCAT(UNIQUE(REPT(Sheet2!$A$1:$C$1,N(IFNA(TEXTSPLIT(C2,,", ")=Sheet2!$A$2:$C$5,0))))),"No Sport Found") So 2 or more teams from different header is that so?
|
|
| Mar 1 at 16:22 | comment | added | chunky-coal | Goodmorning! That is populating “No Sport Hockey” in the cells that do not have a sport. Which works well— thank you! However, the other issue, cells that have two or more teams are still showing as blank. | |
| Mar 1 at 15:38 | comment | added | Mayukh Bhattacharya |
@chunky-coal hello can you try this =IFERROR(CONCAT(UNIQUE(REPT(Sheet2!$A$1:$C$1,N(IFNA(TEXTSPLIT(C2,,", ")=Sheet2!$A$2:$C$5,0))))),"No Sport Hockey")
|
|
| Mar 1 at 15:13 | comment | added | chunky-coal | Of course. I just made some edits and hopefully that will make it easier to understand. Thank you! | |
| Mar 1 at 10:59 | comment | added | Mayukh Bhattacharya | @chunky-coal can you show me few more examples with the expected output, it will definitely help me understand also I can see clearly for multiple values like crimson-tide, bulldogs it is returning correct output. Yes it will give you one output only because i have unique function here. For the other two bulldogs i am not able to understand if you share an excel or a screenshot it will help me more to understand to help you out | |
| Mar 1 at 3:03 | comment | added | chunky-coal | Thank you for the fast reply! The first and last formula didn’t work for me, however the CONCAT formula is showing some promise. It seems on multiple values (crimson-tide, bulldogs) that it’s not providing a Team Name. However a single value (warriors) is showing the name as expected. I’ve also noticed that for teams that share a word, for example, gonzaga-bulldogs and georgia-bulldogs, it will show both Football and Basketball because both teams share the “bulldogs” text. Even though the teams are unique. Any idea on how all of that may be alleviated? | |
| Mar 1 at 1:07 | history | edited | Mayukh Bhattacharya | CC BY-SA 4.0 |
added 149 characters in body
|
| Mar 1 at 0:52 | history | answered | Mayukh Bhattacharya | CC BY-SA 4.0 |