Getting excel to return multiple results from one cell

[Sparhan]

Hi,

I have some data that looks like this:

5/9/23	Apple	Banana	Banana and Grape
6/9/23	Banana	Grape	Grape
7/9/23	Orange	Apple x2	Watermelon
8/9/23	Grape	Banana	Mango
9/9/23	Mango	Mango	Apple x3

So, I am currently using this formula: =UNIQUE(TOCOL(IF(ISNUMBER(SEARCH(" "&"Apple"&" "," "&B1:D5&" ")),A1:A5,1/0),2)) it finds the search word and it returns the 3 dates where Apple appears. Is it possible to get it to also look at the 'x2' or 'x3' within the cell and give the date that many times. So for example if searching for 'Apple' in this table it would return:

5/9/23
7/9/23
7/9/23
9/9/23
9/9/23
9/9/23

Thanks in advance!

 

[ExcelToDAX]

How about:

=LET(FruitNames,B1:D5,dateCount,CHOOSE({1,2},UNIQUE(TOCOL(IF(ISNUMBER(SEARCH(" "&"Apple"&" "," "&FruitNames&" ")),A1:A5,1/0),2)),VALUE(IFNA(SUBSTITUTE(TEXTAFTER(LET(removeBlanks,IFERROR(TOCOL(IF(SEARCH(" "&"Apple"&" "," "&FruitNames&" ")=1,FruitNames,"")),""),FILTER(removeBlanks,removeBlanks<>""))," "),"x",""),1))),LAMBDA(values,num_repeat,XLOOKUP(SEQUENCE(SUM(num_repeat)),VSTACK(1,SCAN(1,num_repeat,LAMBDA(a,b,a+b))),VSTACK(values,""),,-1))(INDEX(dateCount,,1),INDEX(dateCount,,2)))

 

[Sparhan]

Hi @ExcelToDAX, thanks for this. I've tried to implement this with my data, but am having an issue as some of the data doesn't always display as: "Apple x2", sometimes there is other information in the cell as well. It may look like this too: "Apple (P) x2", or like this: "Apple (C) x2", or even "Apple 1 x2"

Lots of your formula doesn't make sense to me! but I can see that it is currently finding all the text after the searched word and then removing any x's using substitute. However it's then getting stuck on some of these other ones as it is left with "(P) 2" and obviously cant process that as a number.

The number we need does always appear on the end of the cell though so is there a way to get it to grab and use this number instead of removing everything to leave just the number? I have this formula that grabs the number on the right of a specified cell: =TEXTJOIN("",TRUE,RIGHT(A1,1)) but wouldn't know where or how to include this into your formula to do the rest of the stuff it does.

Thanks,
Sparhan

 

[Sparhan]

Hi, i've now managed to implement the bit I asked for (to grab the number at the end) using this formula: =LET(FruitNames,B1:D5,Dates,A1:A5,dateCount,CHOOSE({1,2},UNIQUE(TOCOL(IF(ISNUMBER(SEARCH(" "&"Apple"&" "," "&FruitNames&" ")),Dates,1/0),2)),VALUE(RIGHT(LET(removeBlanks,IFERROR(TOCOL(IF(SEARCH(" "&"Apple"&" "," "&FruitNames&" ")=1,FruitNames,"")),""),FILTER(removeBlanks,removeBlanks<>"")),1))),LAMBDA(values,num_repeat,XLOOKUP(SEQUENCE(SUM(num_repeat)),VSTACK(1,SCAN(1,num_repeat,LAMBDA(a,b,a+b))),VSTACK(values,""),,-1))(INDEX(dateCount,,1),INDEX(dateCount,,2)))

But I have come up against another problem. I also have some data where "Apple" in various forms might appear on the same row more than once. like this:

05/09/2023​	Apple x1	Banana	Banana and Grape
06/09/2023​	Banana	Grape	Grape
07/09/2023​	Orange	Apple x2	Apple x2
08/09/2023​	Grape	Apple x2	Mango
09/09/2023​	Mango	Mango	Apple x3

Running this currently returns:

5/9/23
7/9/23
7/9/23
8/9/23
8/9/23
9/9/23
9/9/23
#N/A
#N/A
#N/A

I believe this is because the first part of the 'datecount' is filtering out non-unique dates like it should, leaving the 4 dates as results in the array, but the second part is not doing this and is leaving 5 seperate results in the array. I can't just add the unique fuction to that side of the 'datecount' formula though as it would filter all extra x2's away regardless of what row they were on, leaving only 3 results in the array.

I hope that makes sense, quite hard to explain. I would like it to return:

5/9/23
7/9/23
7/9/23
8/9/23
8/9/23
9/9/23
9/9/23
9/9/23

Hope you can help!

 

[Rick Rothstein]

This should work (you will have to format the cells with the date format of your choice)...

=LET(fruit,"apple",dates,A1:A5,data,B1:D5,g,IFERROR(TRIM(REPT(dates&" ",MID(TEXTAFTER(data," ",-1),2,99))),dates),0+TEXTSPLIT(TEXTJOIN(" ",,IF(ISNUMBER(SEARCH(fruit,data)),g,"")),," "))

 

[Sparhan]

=LET(fruit,"apple",dates,A1:A5,data,B1:D5,g,IFERROR(TRIM(REPT(dates&" ",MID(TEXTAFTER(data," ",-1),2,99))),dates),0+TEXTSPLIT(TEXTJOIN(" ",,IF(ISNUMBER(SEARCH(fruit,data)),g,"")),," "))

Hi Rick, thanks for this. It does remove the N/A issue, however it is finding both results from the 7/9/23 row. So currently it returns 4 lots of 7/9/23, but I want it to ignore the 2nd "Apple x2" on 7/9/23 and just return 2 lots of 7/9/23. That's what the UNIQUE function was attempting to do in the original formula, giving me only one result per row based on the date.

Hope that makes sense?

Thanks,
Sparhan

 

[Sparhan]

Hi Rick, thanks for this. It does remove the N/A issue, however it is finding both results from the 7/9/23 row. So currently it returns 4 lots of 7/9/23, but I want it to ignore the 2nd "Apple x2" on 7/9/23 and just return 2 lots of 7/9/23. That's what the UNIQUE function was attempting to do in the original formula, giving me only one result per row based on the date.

Hope that makes sense?

Thanks,
Sparhan

Hi,

I managed to work out where to put the tocol/unique. This now works and filters to just one result per date:

=LET(fruit,"Apple",dates,$A$1:$A$5,data,$B$1:$D$5,g,IFERROR(TRIM(REPT(dates&" ",MID(TEXTAFTER(data," ",-1),2,99))),dates)0+TEXTSPLIT(TEXTJOIN(" ",,UNIQUE(TOCOL(IF(ISNUMBER(SEARCH(fruit,data)),g,1/0),2))),," "))

Thanks for your help!