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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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?
frugalmuleAuthor Commented:
It should be unlimited.
Saqib Husain, SyedEngineerCommented:
I do not think unlimited would be possible within a cell.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

frugalmuleAuthor Commented:
I would limit to 25.
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))
frugalmuleAuthor Commented:
Excellent except that you took out my two commas
Saqib Husain, SyedEngineerCommented:
Do you know how to put them in?
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.
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

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

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?
ProfessorJimJamMicrosoft Excel ExpertCommented:
@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

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.
ProfessorJimJamMicrosoft Excel ExpertCommented:
@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

frugalmuleAuthor Commented:
A little splotchy on that last one.  Here is how I experienced it http://screencast.com/t/cmJU7lNuN1x
ProfessorJimJamMicrosoft Excel ExpertCommented:
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

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
ProfessorJimJamMicrosoft Excel ExpertCommented:
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.
frugalmuleAuthor Commented:
Ok, sorry yes.

The actual columns are D, F, and T.
ProfessorJimJamMicrosoft Excel ExpertCommented:
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

frugalmuleAuthor Commented:
MUCH better!!

Almost home http://screencast.com/t/lrCw0Esk
ProfessorJimJamMicrosoft Excel ExpertCommented:
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

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.
ProfessorJimJamMicrosoft Excel ExpertCommented:
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.
ProfessorJimJamMicrosoft Excel ExpertCommented:
this was a long ride.  glad it worked.

thanks for your constant feedback and well explained details of requirement with your recorded videos.
frugalmuleAuthor Commented:
Really good stuff Professor!!
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.