asked on

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.

Saqib Husain

What is the maximum number of repetitions expected?

Ted Penner

ASKER

It should be unlimited.

Saqib Husain

I do not think unlimited would be possible within a cell.

Ted Penner

ASKER

I would limit to 25.

Saqib Husain

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

Ted Penner

ASKER

Excellent except that you took out my two commas

Saqib Husain

Do you know how to put them in?

Ted Penner

ASKER

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

=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

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

Ted Penner

ASKER

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?

Professor J

@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

Ted Penner

ASKER

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.

Professor J

@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

Ted Penner

ASKER

A little splotchy on that last one. Here is how I experienced it http://screencast.com/t/cmJU7lNuN1x

Professor J

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

Ted Penner

ASKER

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

Professor J

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.

Ted Penner

ASKER

Ok, sorry yes.

The actual columns are D, F, and T.

Professor J

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

Ted Penner

ASKER

MUCH better!!

Almost home http://screencast.com/t/lrCw0Esk

ASKER CERTIFIED SOLUTION

Professor J

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ted Penner

ASKER

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.

Professor J

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.

Professor J

this was a long ride. glad it worked.

thanks for your constant feedback and well explained details of requirement with your recorded videos.

Ted Penner

ASKER

Really good stuff Professor!!