I am trying to accomplish this on numbers but when I do this exact function with the if statement it returns a false result
https://www.youtube.com/watch?v=TMZEUlFGp1U
What is it that I need to do to fix this? I have tried using lookup instead of the if statement but that only results in the last text rather than all text fields
MacBook, macOS 10.13
The woman in the video is making extensive use of Excel's array functions that do an array of calculations within one cell. Numbers does not have that. And Number's TEXTJOIN function has a problem/bug where it does not consider null strings to be "empty" so it will include them in the list and you'll get a list that has multiple commas and maybe commas at the end. Maybe they've fixed that in the most recent version but I am doubtful (I can't upgrade to it to find out).
Anyway, here is a possible solution:
Formula in cell I2 =IF(XLOOKUP($H2,B,B,"",0)≠"",B$1,"")
Fill across to K2 then down with all three columns to row 6 (only those rows with an app name)
J2 =SUBSTITUTE(SUBSTITUTE(TEXTJOIN(", ",TRUE,I2:K2),REGEX("(, )+"),", "),REGEX(", $"),"")
Fill down to row 6
The monstrosity in column J is to get rid of extraneous commas from Numbers' TEXTJOIN function. If it wasn't for that problem, only the TEXTJOIN part of it would be necessary.
Hide columns I-K
The woman in the video is making extensive use of Excel's array functions that do an array of calculations within one cell. Numbers does not have that. And Number's TEXTJOIN function has a problem/bug where it does not consider null strings to be "empty" so it will include them in the list and you'll get a list that has multiple commas and maybe commas at the end. Maybe they've fixed that in the most recent version but I am doubtful (I can't upgrade to it to find out).
Anyway, here is a possible solution:
Formula in cell I2 =IF(XLOOKUP($H2,B,B,"",0)≠"",B$1,"")
Fill across to K2 then down with all three columns to row 6 (only those rows with an app name)
J2 =SUBSTITUTE(SUBSTITUTE(TEXTJOIN(", ",TRUE,I2:K2),REGEX("(, )+"),", "),REGEX(", $"),"")
Fill down to row 6
The monstrosity in column J is to get rid of extraneous commas from Numbers' TEXTJOIN function. If it wasn't for that problem, only the TEXTJOIN part of it would be necessary.
Hide columns I-K
Asking the actual question can often speed things along. Here is an example. Note that having different things going on in one table is an Excel way of thinking. Numbers allows more than one table per sheet so you can split things up into separate tables. My example uses two tables.
Last row (row 10) of Table 2 is a footer row.
Table 2::C2 =B2&":"&A2
Fill down
Table 2::C10 ="~"&TEXTJOIN("~",TRUE,C)&"~"
Table 1::B2 =IFERROR(TEXTJOIN(",",TRUE,REGEX.EXTRACT(Table 2::C$10,"(?<=~)[\w\s]+(?=:"&A2&")",0,capture-group-occurrence)),"<Nobody>")
Fill down to the last row with a city in it.
Sorry, had to go before I could finish my post with the explanation.
Table 1::B2 =IFERROR(TEXTJOIN(",",TRUE,REGEX.EXTRACT(Table 2::C$10,"(?<=~)[\w\s]+(?=:"&A2&")",0)),"<Nobody>")
The formula in column B is using REGEX.EXTRACT on the string in Table 1::C10 to find a string that is a ~ followed by a letters and white space characters (i.e., the person's name) followed by a : and the city. It returns the name. The 0 means to return all matches as an array. TEXTJOIN joins all the results. IFERROR is for when REGEX.EXTRACT finds no matches.
Include the comma as one of the possible characters that make up a name. Below it accepts "word" characters, "white space" characters, commas, and hyphens (for hyphenated names)
B2 =IFERROR(TEXTJOIN(",",TRUE,REGEX.EXTRACT(Table 2::C$10,"(?<=~)[\w\s,-]+(?=:"&A2&")",0)),"<Nobody>")
I have no way to deal with duplicates. Can you handle that as a data entry issue (i.e., don't enter the same name twice for the same city)? I'd say the same thing about entering two or more names in the same cell using commas vs one name per cell because it would be better not to do that, but the formula can handle it.
Make a new column D or F with this formula in every row (i.e., next to each city)
=IF(COUNTIF(A,E)=0,"X","")
This will count how many times that city appears in column A (which should be 1 or 0). If it does not appear, it makes an X.
@bandunit let me explain my intentions in direct example. What I would like to do is make B10 search in column Q for A10's text, then provide me with the results from W88 and W89. But since Troy is on there twice I want it to only show him once. It goes without saying I would like it to search column W as well and find my all results. I will be adding more Garland RSVPs and I will be applying this to all other Cities as well
This is great. we are super close. However, most of the time I have multiple RSVPs on the same city but it removed my multiples on garland. Just showed troy rather than troy and bob. Do I need to put each name in a different cell rather than a bunch in one cell? (I would prefer not to do that but I can if I must).
EDIT:: I made a cell for each RSVP so no need to figure it out from just one cell. Now my only other question at this point is I am getting duplicates if they are registered with multiple properties. so troy for example is popping up twice under garland rather than just once.
Thank you for being so helpful. I was able to build this spreadsheet out and it has been such a useful tool. One other thing I'd like to try and do:
Is there a way to check Column A and have it compare with Column E and report any cities that are missing?
This way when I enter a new one I don't have to manually check to see if it's already on my report. I can have another table or cell tell me whether it's on there or not.
Complex lookup, jointext function on Numbers
