# 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:

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))))
``````

Assistance in making this happen is greatly appreciated.
###### 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.

EngineerCommented:
What is the maximum number of repetitions expected?
Author Commented:
It should be unlimited.
EngineerCommented:
I do not think unlimited would be possible within a cell.
Author Commented:
I would limit to 25.
EngineerCommented:
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))
Author Commented:
Excellent except that you took out my two commas
EngineerCommented:
Do you know how to put them in?
Author 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.
EngineerCommented:
``````=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))
``````
EngineerCommented:
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))
``````
Author 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?
Microsoft 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.

``````=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))))))
``````
Author 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.
Microsoft 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)))))))
``````
Author Commented:
A little splotchy on that last one.  Here is how I experienced it http://screencast.com/t/cmJU7lNuN1x
Microsoft 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),","))
``````
Author 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
Microsoft 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

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.
Author Commented:
Ok, sorry yes.

The actual columns are D, F, and T.
Microsoft 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),","))
``````
Author Commented:
MUCH better!!

Almost home http://screencast.com/t/lrCw0Esk
Microsoft 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),",")))
``````

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.

Author 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.
Microsoft Excel ExpertCommented:
the formula does not change any format of spreadsheet. perhaps you are copying it with its cell format.

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.
Microsoft 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.
Author Commented:
Really good stuff Professor!!