Forum Discussion
Excel Array to shorten a very long Function
I need to condense a block (rows and columns) of cells that contain notes into one Notes field. Some cells contain dates and some contain text. I want to maintain the rows and columns of data but contain them in a single cell. Oh, and I need the results to be on one sheet while the source blocks come from 1000 sheets that each contain account information for a different customer. I'm sure there is a more elegant way to do this with Arrays but I can't quite figure it out. Here is my clunky way that works but runs into a problem with max characters in a formula. Can anyone do this in a more elegant array (without using VB Script)?
=IF(LEN($B3)=0,
0,
TEXTJOIN(
" ",
FALSE,
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(21,3))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(21,3)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(21,3))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(21,4))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(21,4)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(21,4))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(21,5))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(21,5)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(21,5))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(21,6))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(21,6)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(21,6))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(21,7))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(21,7)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(21,7))),
CHAR(10),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(22,3))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(22,3)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(22,3))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(22,4))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(22,4)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(22,4))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(22,5))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(22,5)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(22,5))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(22,6))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(22,6)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(22,6))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(22,7))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(22,7)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(22,7))),
CHAR(10),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(23,3))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(23,3)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(23,3))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(23,4))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(23,4)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(23,4))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(23,5))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(23,5)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(23,5))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(23,6))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(23,6)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(23,6))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(23,7))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(23,7)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(23,7))),
CHAR(10),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(24,3))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(24,3)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(24,3))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(24,4))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(24,4)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(24,4))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(24,5))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(24,5)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(24,5))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(24,6))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(24,6)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(24,6))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(24,7))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(24,7)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(24,7))),
CHAR(10),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(25,3))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(25,3)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(25,3))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(25,4))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(25,4)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(25,4))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(25,5))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(25,5)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(25,5))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(25,6))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(25,6)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(25,6))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(25,7))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(25,7)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(25,7))),
CHAR(10),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(26,3))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(26,3)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(26,3))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(26,4))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(26,4)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(26,4))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(26,5))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(26,5)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(26,5))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(26,6))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(26,6)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(26,6))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(26,7))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(26,7)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(26,7))),
CHAR(10),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(27,3))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(27,3)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(27,3))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(27,4))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(27,4)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(27,4))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(27,5))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(27,5)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(27,5))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(27,6))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(27,6)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(27,6))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(27,7))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(27,7)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(27,7))),
CHAR(10),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(28,3))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(28,3)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(28,3))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(28,4))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(28,4)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(28,4))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(28,5))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(28,5)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(28,5))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(28,6))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(28,6)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(28,6))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(28,7))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(28,7)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(28,7))),
CHAR(10),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(29,3))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(29,3)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(29,3))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(29,4))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(29,4)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(29,4))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(29,5))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(29,5)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(29,5))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(29,6))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(29,6)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(29,6))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(29,7))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(29,7)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(29,7))),
CHAR(10),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(30,3))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(30,3)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(30,3))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(30,4))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(30,4)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(30,4))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(30,5))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(30,5)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(30,5))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(30,6))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(30,6)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(30,6))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(30,7))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(30,7)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(30,7))),
CHAR(10),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(31,3))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(31,3)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(31,3))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(31,4))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(31,4)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(31,4))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(31,5))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(31,5)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(31,5))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(31,6))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(31,6)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(31,6))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(31,7))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(31,7)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(31,7))),
CHAR(10),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(32,3))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(32,3)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(32,3))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(32,4))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(32,4)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(32,4))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(32,5))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(32,5)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(32,5))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(32,6))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(32,6)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(32,6))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(32,7))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(32,7)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(32,7))),
CHAR(10),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(33,3))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(33,3)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(33,3))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(33,4))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(33,4)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(33,4))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(33,5))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(33,5)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(33,5))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(33,6))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(33,6)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(33,6))),
IF(ISNUMBER(INDIRECT("'"&$B3&"'!"&ADDRESS(33,7))),TEXT(INDIRECT("'"&$B3&"'!"&ADDRESS(33,7)),"dd/mm/yyyy"),INDIRECT("'"&$B3&"'!"&ADDRESS(33,7))),
)
)
Hi Michael,
I guess zero appears for empty cells, you may add one more check if the cell is empty or not
=IF(LEN($B3)=0,0, TEXTJOIN( " ", FALSE, IF(ISNUMBER(INDIRECT("'" & $B3 & "'!C21:G33")), TEXT(INDIRECT("'" & $B3 & "'!C21:G33"),"dd/mm/yyyy"), IF(ISBLANK(INDIRECT("'" & $B3 & "'!C21:G33")),"", INDIRECT("'" & $B3 & "'!C21:G33")) ) & SUBSTITUTE( {"","","","","lf"}, "lf",CHAR(10)) ) )My guess TRUE/FALSE parameter for TEXTJOIN won't work in array formula
4 Replies
Hi Michael,
Array formula could be like
=IF(LEN($B3)=0,0, TEXTJOIN( " ", FALSE, IF(ISNUMBER(INDIRECT("'" & $B3 & "'!C21:G33")), TEXT(INDIRECT("'" & $B3 & "'!C21:G33"),"dd/mm/yyyy"), INDIRECT("'" & $B3 & "'!C21:G33") ) & SUBSTITUTE( {"","","","","lf"}, "lf",CHAR(10)) ) )Sergei, You ROCK!!!
I do have one issue that maybe you can help me clean up. The formula you suggested adds zeros to the text as shown in the sample output below. I believe they are coming from the substitute line of code but I can't seem to make them go away. Any ideas?
Thanks again for working out the array function! I really appreciate your help
sample output from your formula:
0 0 0 0 0
13/05/2016 Sent email to Jeremey 0 0 0
0 Jeremy sent me email back and we are sendng samples to him on Monday 0 0 0
0 Follow up on Wed. 0 0 0
0 0 0 0 0
23/05/2016 left message for Jeremy 0 0 0
0 c/b if message not returned 0 0 0
0 0 0 0 0
21/06/2016 Sent follow up email to Jeremy 0 0 0
0 0 0 0 0
11/07/2016 Sent another follow up to jeremy 0 0 0
0 0 0 0 0
15/07/2016 Jeremey is on Paternity leave. Vheck back Aug 0 0 0
0 0 0 0 0
11/08/2016 Left Jeremy a message 0 0 0
0 0 0 0 0
19/09/2016 Sent jeremy a follow up email 0 0 0
16/10/2016 Ron called - no response 0 0 0
23/11/2016 Nail down Jeremy!!! 0 0 0
01/12/2016 Try again n new year 0 0 0
0 0 0 0 0
17/01/2017 Left a vm for Jeremy. Spoke to reception. He didn’t take my call 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0
0 0 0 0 0Hi Michael,
I guess zero appears for empty cells, you may add one more check if the cell is empty or not
=IF(LEN($B3)=0,0, TEXTJOIN( " ", FALSE, IF(ISNUMBER(INDIRECT("'" & $B3 & "'!C21:G33")), TEXT(INDIRECT("'" & $B3 & "'!C21:G33"),"dd/mm/yyyy"), IF(ISBLANK(INDIRECT("'" & $B3 & "'!C21:G33")),"", INDIRECT("'" & $B3 & "'!C21:G33")) ) & SUBSTITUTE( {"","","","","lf"}, "lf",CHAR(10)) ) )My guess TRUE/FALSE parameter for TEXTJOIN won't work in array formula
