Modify transpose formula to list data vertically inside the cell

Column C of the App Totals tab in the Google spreadsheet below contains a formula, also noted below, that allows data to be transformed into separate columns.

This is a great start, but I would prefer to have a vertical list, but to remain on the same row, all inside the relevant cell.

Here is the online spreadsheet:
https://docs.google.com/spreadsheets/d/17LeToNWtIC5uqo8QnXsDHYof7cisAakKct2ZBtK6QfU/edit#gid=1254353415

Here is the formula used in C2:
=transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&"on "&TEXT(Apptracker!S:S,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))

Open in new window


Assistance in making this happen is greatly appreciated.
frugalmuleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
What is the maximum number of repetitions expected?
0
frugalmuleAuthor Commented:
It should be unlimited.
0
Saqib Husain, SyedEngineerCommented:
I do not think unlimited would be possible within a cell.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

frugalmuleAuthor Commented:
I would limit to 25.
0
Saqib Husain, SyedEngineerCommented:
Then try

=INDEX(FILTER(Apptracker!D:D&" #"&Apptracker!F:F&" on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),1)
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&" #"&Apptracker!F:F&" on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),2))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&" #"&Apptracker!F:F&" on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),3))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&" #"&Apptracker!F:F&" on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),4))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&" #"&Apptracker!F:F&" on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),5))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&" #"&Apptracker!F:F&" on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),6))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&" #"&Apptracker!F:F&" on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),7))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&" #"&Apptracker!F:F&" on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),8))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&" #"&Apptracker!F:F&" on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),9))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&" #"&Apptracker!F:F&" on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),10))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&" #"&Apptracker!F:F&" on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),11))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&" #"&Apptracker!F:F&" on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),12))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&" #"&Apptracker!F:F&" on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),13))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&" #"&Apptracker!F:F&" on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),14))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&" #"&Apptracker!F:F&" on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),15))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&" #"&Apptracker!F:F&" on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),16))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&" #"&Apptracker!F:F&" on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),17))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&" #"&Apptracker!F:F&" on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),18))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&" #"&Apptracker!F:F&" on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),19))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&" #"&Apptracker!F:F&" on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),20))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&" #"&Apptracker!F:F&" on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),21))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&" #"&Apptracker!F:F&" on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),22))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&" #"&Apptracker!F:F&" on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),23))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&" #"&Apptracker!F:F&" on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),24))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&" #"&Apptracker!F:F&" on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),25))
0
frugalmuleAuthor Commented:
Excellent except that you took out my two commas
0
Saqib Husain, SyedEngineerCommented:
Do you know how to put them in?
0
frugalmuleAuthor Commented:
I don't know how to put the commas in but there are still some other issues also.  Here is a video http://screencast.com/t/3sATR43eAjb.
0
Saqib Husain, SyedEngineerCommented:
=INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),1)
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),2))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),3))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),4))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),5))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),6))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),7))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),8))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),9))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),10))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),11))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),12))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),13))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),14))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),15))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),16))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),17))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),18))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),19))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),20))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),21))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),22))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),23))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),24))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2),25))

Open in new window

0
Saqib Husain, SyedEngineerCommented:
This should take care of the blank lines
=INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2,Apptracker!D:D<>""),1)
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2,Apptracker!D:D<>""),2))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2,Apptracker!D:D<>""),3))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2,Apptracker!D:D<>""),4))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2,Apptracker!D:D<>""),5))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2,Apptracker!D:D<>""),6))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2,Apptracker!D:D<>""),7))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2,Apptracker!D:D<>""),8))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2,Apptracker!D:D<>""),9))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2,Apptracker!D:D<>""),10))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2,Apptracker!D:D<>""),11))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2,Apptracker!D:D<>""),12))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2,Apptracker!D:D<>""),13))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2,Apptracker!D:D<>""),14))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2,Apptracker!D:D<>""),15))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2,Apptracker!D:D<>""),16))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2,Apptracker!D:D<>""),17))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2,Apptracker!D:D<>""),18))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2,Apptracker!D:D<>""),19))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2,Apptracker!D:D<>""),20))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2,Apptracker!D:D<>""),21))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2,Apptracker!D:D<>""),22))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2,Apptracker!D:D<>""),23))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2,Apptracker!D:D<>""),24))
&IFERROR(CHAR(10)&INDEX(FILTER(Apptracker!D:D&", #"&Apptracker!F:F&", on "&TEXT(Apptracker!S:S,"DDD M/D/YYYY"),Apptracker!H:H=B2,Apptracker!D:D<>""),25))

Open in new window

0
frugalmuleAuthor Commented:
If there is no actual #, then it should not show anything.  Also the current year is not the same as what is reported in the Apptracker tab?
0
ProfessorJimJamCommented:
@Frugalmule,

you can achieve what you are looking for with my simple formula below.


this formula takes care of all the concerns you had about dates as well.
i have already put it in your spreadsheet.

=if(ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&"on "&TEXT(Apptracker!S:S,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&Apptracker!S:S,Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&"on "&TEXT(Apptracker!S:S,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))))

Open in new window

0
frugalmuleAuthor Commented:
PrefessorJimJam,

Best so far by a long shot!  All issues perfectly resolved with the exception of the Job ID column F.  If there is no number represented at all in that cell, then none of the contents of the cell or the # sign should be displayed.
0
ProfessorJimJamCommented:
@Frugalmule,

fixed the # sign too with the modification of formula.   i have built the following formula to take care of that sign issue as well.  it is also put in the spreadsheet.


good luck

=IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},FILTER(Apptracker!F:F,Apptracker!H:H=B2)))>0, if(ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&"on "&TEXT(Apptracker!S:S,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&Apptracker!S:S,Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&"on "&TEXT(Apptracker!S:S,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D)))))),if(ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"on "&TEXT(Apptracker!S:S,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&Apptracker!S:S,Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"on "&TEXT(Apptracker!S:S,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D)))))))

Open in new window

0
frugalmuleAuthor Commented:
A little splotchy on that last one.  Here is how I experienced it http://screencast.com/t/cmJU7lNuN1x
0
ProfessorJimJamCommented:
Fragalmule,

cannot say it was easy

here is modified formula

=if(not(and(not(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},FILTER(Apptracker!F:F,Apptracker!H:H=B2)))>0),ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&"on "&TEXT(Apptracker!S:S,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)))),IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},FILTER(Apptracker!F:F,Apptracker!H:H=B2)))>0, if(ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&"on "&TEXT(Apptracker!S:S,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&Apptracker!S:S,Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&"on "&TEXT(Apptracker!S:S,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D)))))),if(ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"on "&TEXT(Apptracker!S:S,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&Apptracker!S:S,Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"on "&TEXT(Apptracker!S:S,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))))),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(if(ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"on "&TEXT(Apptracker!S:S,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&Apptracker!S:S,Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"on "&TEXT(Apptracker!S:S,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D)))))),",",CHAR(2),1),",",""),CHAR(2),","))

Open in new window

0
frugalmuleAuthor Commented:
I'm not sure it's pulling data from Apptracker D, F, and T the way it should still.  Here is the way I experienced it.

http://screencast.com/t/1oDDIskv8
0
ProfessorJimJamCommented:
Fragalmule,

the reason you are getting incorrect result, because the spreadsheet you made available and the one you are working on are completely different spreadsheets

here is the link you made available https://docs.google.com/spreadsheets/d/17LeToNWtIC5uqo8QnXsDHYof7cisAakKct2ZBtK6QfU/edit#gid=1254353415

in this one the "applied on" column is in Column S, where as in your recorded video it shows in column T

the formula is column sensitive, if your columns are different than the one in the link above the formula will not work.
please tell me which column in your actual spreadsheet is Company name and which column is Job title and which column is  Job ID and which column is Applied On. so that i modify the formula as per the actual spreadsheet.
0
frugalmuleAuthor Commented:
Ok, sorry yes.

The actual columns are D, F, and T.
0
ProfessorJimJamCommented:
can you try now with this formula

=if(not(and(not(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},FILTER(Apptracker!F:F,Apptracker!H:H=B2)))>0),ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&"on "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)))),IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},FILTER(Apptracker!F:F,Apptracker!H:H=B2)))>0, if(ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&"on "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&Apptracker!T:T,Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&"on "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D)))))),if(ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"on "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&Apptracker!T:T,Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"on "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))))),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(if(ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"on "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&Apptracker!T:T,Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"on "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D)))))),",",CHAR(2),1),",",""),CHAR(2),","))

Open in new window

0
frugalmuleAuthor Commented:
MUCH better!!

Almost home http://screencast.com/t/lrCw0Esk
0
ProfessorJimJamCommented:
Ok here is the last edit

and i have put it also in the spreadsheet.

=if(right(if(not(and(not(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},FILTER(Apptracker!F:F,Apptracker!H:H=B2)))>0),ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)))),IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},FILTER(Apptracker!F:F,Apptracker!H:H=B2)))>0, if(ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&Apptracker!T:T,Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D)))))),if(ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&Apptracker!T:T,Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))))),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(if(ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&Apptracker!T:T,Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D)))))),",",CHAR(2),1),",",""),CHAR(2),",")),2)=", ",LEFT(if(not(and(not(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},FILTER(Apptracker!F:F,Apptracker!H:H=B2)))>0),ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)))),IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},FILTER(Apptracker!F:F,Apptracker!H:H=B2)))>0, if(ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&Apptracker!T:T,Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D)))))),if(ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&Apptracker!T:T,Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))))),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(if(ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&Apptracker!T:T,Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D)))))),",",CHAR(2),1),",",""),CHAR(2),",")),(len(if(not(and(not(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},FILTER(Apptracker!F:F,Apptracker!H:H=B2)))>0),ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)))),IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},FILTER(Apptracker!F:F,Apptracker!H:H=B2)))>0, if(ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&Apptracker!T:T,Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D)))))),if(ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&Apptracker!T:T,Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))))),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(if(ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&Apptracker!T:T,Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D)))))),",",CHAR(2),1),",",""),CHAR(2),",")))-2)),if(not(and(not(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},FILTER(Apptracker!F:F,Apptracker!H:H=B2)))>0),ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)))),IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},FILTER(Apptracker!F:F,Apptracker!H:H=B2)))>0, if(ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&Apptracker!T:T,Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&"#"&Apptracker!F:F&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D)))))),if(ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&Apptracker!T:T,Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))))),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(if(ISNUMBER(find(1899,join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),1)),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&Apptracker!T:T,Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D))))),join(Char(10)&",",transpose(FILTER(Apptracker!D:D&", "&TEXT(Apptracker!T:T,"ddd MM/DD/YYYY"),Apptracker!H:H=B2,NOT(ISBLANK(Apptracker!D:D)))))),",",CHAR(2),1),",",""),CHAR(2),",")))

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
frugalmuleAuthor Commented:
Better again but it is still changing the point size to 8  instead of 10 and it does not keep the top vs center vertical alignment.
0
ProfessorJimJamCommented:
the formula does not change any format of spreadsheet. perhaps you are copying it with its cell format.

please see the formula in the spreadsheet https://docs.google.com/spreadsheets/d/17LeToNWtIC5uqo8QnXsDHYof7cisAakKct2ZBtK6QfU/edit#gid=1254353415

there is no change on the formatting.

not sure what is causing the format to shrink to 8, but i am sure it is not the formula itself.
0
ProfessorJimJamCommented:
this was a long ride.  glad it worked.

thanks for your constant feedback and well explained details of requirement with your recorded videos.
0
frugalmuleAuthor Commented:
Really good stuff Professor!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.