TOFILE

TOFILE(table, headers, delimiter, [transformdates])
a
Required. The table or "Table[#Data]".
header
Required. The header (or Table[#Headers], In some cases, the row above headers can be used).
delimiter
Required. The delimiter.
transformdates
Optional, Transform column with dates based on header.

TOFILE, a function putting a delmiter between the headers and each cell with regards to hiding columns.

jaeiow

Board Regular
Joined
Jun 12, 2022
Messages
119
Office Version
  1. 365
Platform
  1. Windows
If there such a need for a flat file generating lambda. Able to make csv/tsv (tilde)/pipe/tab delimited, files, etc. In my case a need arises to concatenate the header with the cell. This was a more monumental task until I found out that the old "&" (thee ole Concatenate function) can handle arrays. Joining the result and you get a flat line for your data exchange needs.
  • hiding columns, blanking out headers
    • erases header and data from output
    • except last column, in case you need to append something.
  • column is competely empty
    • also erases header and data form output
  • sample output
    Excel Formula:
    =FILTER(TOFILE(Table2[#Data],OFFSET(Table2[#Headers],-1,0),"~",1), Table2[Email]="r")
    • Nome~c~email~r~ID~3~Date~06/27/2021
      Nome~c~email~r~ID~6~Date~06/27/2024
      Nome~c~email~r~ID~~Date~06/27/2027
Here is that function, TOFILE. Requires FORMATDATE only if [xtransformdate] parameter is set to TRUE, see below, or replace with just a & ""

Excel Formula:
=LAMBDA(a,header,delimiter,[transformdates],
LET(
    transformxdate, IF(
        OR(ISOMITTED(transformdates), NOT(transformdates)),
        a & "",
        FORMATDATE(
            a & "",
            XLOOKUP("*Date", header, COLUMN(header), 0, 2),
            "MM/DD/YYYY"
        )
    ),
    cleancolumns, IF(ISERROR(transformxdate), "", transformxdate),
    cellwidth, BYCOL(header, LAMBDA(l, LARGE(CELL("width", l), 1))),
    filteronlyvisible, FILTER(cleancolumns, cellwidth <> 0),
    columnisnotempty, NOT(BYCOL(filteronlyvisible, LAMBDA(x, AND(x = "")))),
    headersvisible, FILTER(FILTER(header, cellwidth <> 0), columnisnotempty),
    columnsoutput, FILTER(filteronlyvisible, columnisnotempty),
    concatwithcolheader, headersvisible & delimiter & columnsoutput,
    omitblankheaderexceptlast, IFNA(
        IF(ISBLANK(DROP(headersvisible, , -1)), "", concatwithcolheader),
        IF(
            ISBLANK(CHOOSECOLS(headersvisible, -1)),
            columnsoutput,
            concatwithcolheader
        )
    ),
    BYROW(omitblankheaderexceptlast, LAMBDA(join, TEXTJOIN(delimiter, , join)))
)
)

FORMATDATE (credits ADATE, reduced to a smaller function, It could improve yet with newer functions).

Excel Formula:
=LAMBDA(a,cl,df,
LET(
    c, MAX(0, cl),
    l, COLUMNS(a),
    sl, SEQUENCE(, l),
    IF(sl = c, TEXT(a, df), a)
)
)

I'm thinking of ways to improve this, as it is for my specific use-case at a software where I work. This is a simple way to send rows of data to the system where the columns map again, but we don't have to worry about copy-pasting headers, finding the rows, and then copy-pasting the rows.
We could:
  1. Allow output of standard tables by stacking the headers instead of concatenating with each cell. This is simple with an optional parameter.
  2. Stacking tables. You can already see Stacking The Beatles in Excel: An Exercise in 3D stacking for using multiple tables as a source.
  3. If two rows of headers are used for input, handle those
 
Upvote 0

jaeiow

Board Regular
Joined
Jun 12, 2022
Messages
119
Office Version
  1. 365
Platform
  1. Windows
thanks working like charm
Thank you for the feedback. I hope I don't make it too much longer, because lengthy code just isn't well supported for sharing to other users in Excel's name manager (there seems to be a limit of 2088 characters, but what I did was integrate @Rnkhch two utilities, XCOUNT, and XTYPE.

lambda function, TOFILE
[CT]: new argument, count and types. 1=: add totals rows, that adds column number, count, and type. 0=: dont add totals row

At 2051 characters long. adding new features to this is probably not useful for sharing.
Excel Formula:
=LAMBDA(a, h, d, [XD], [AV], [AN], [CT], [N],
LET(
t_date, IF(
OR(ISOMITTED(XD), NOT(XD)),
a & "",
FORMATDATE(
FORMATDATE(
a & "",
XLOOKUP(
"*Date",
TOCOL(h),
LET(c, COLUMNS(h), MOD(SEQUENCE(ROWS(h) * c) - 1, c) + 1),
0,
2
),
"MM/DD/YYYY"
),
XLOOKUP(
"*Date",
TOCOL(h),
LET(c, COLUMNS(h), MOD(SEQUENCE(ROWS(h) * c) - 1, c)),
0,
2,
-1
),
"MM/DD/YYYY"
)
),
c_columns, IF(ISERROR(t_date), "", t_date),
c_width, BYCOL(h, LAMBDA(c, LARGE(CELL("width", c), 1))),
c_visible, FILTER(c_columns, c_width <> 0),
c_notempty, NOT(BYCOL(c_visible, LAMBDA(c, AND(c = "")))),
x_count, FILTER(
FILTER(
CBYCOL(a, LAMBDA(x, VSTACK(XCOUNT(x, {1, 2}, 1, , , ), UNIQUE(XTYPE(x, 5), , )))),
c_width <> 0
),
c_notempty
),
l_sequence, FILTER(
FILTER(SUBSTITUTE(ADDRESS(1, SEQUENCE(, COLUMNS(h)), 4), "1", ""), c_width <> 0),
c_notempty
),
h_visible, FILTER(
FILTER(BYCOL(h, LAMBDA(x, TEXTJOIN("_", , x))), c_width <> 0),
c_notempty
),
o_columns, FILTER(c_visible, c_notempty),
c_or_s, IF(
OR(ISOMITTED(AV), NOT(AV)),
VSTACK(
IF(
OR(ISOMITTED(CT), NOT(CT)),
h_visible & d & o_columns,
VSTACK(
h_visible & d & x_count,
h_visible & d & l_sequence,
h_visible & d & o_columns
)
)
),
VSTACK(h_visible, l_sequence, x_count, o_columns)
),
nl_omitbl, ARCHCLS(
IFNA(
IF(DROP(h_visible, , -1) = "", "", c_or_s),
IF(TAKE(h_visible, , -1) = "", TEXTAFTER(c_or_s, d, , , , c_or_s), c_or_s)
),
N
),
j_by_row, BYROW(
nl_omitbl,
LAMBDA(j_row, TEXTJOIN(d, IF(OR(ISOMITTED(AV), NOT(AV)), TRUE, FALSE), j_row))
),
IF(
AND(AN = 1, OR(ISOMITTED(AV), NOT(AV))),
LET(
t_array, TEXTAFTER(j_by_row, d, -1, , , " "),
u_choices, SORT(ROWSUNQ(t_array, -1, 0), 1, 1),
o, CHOOSECOLS(u_choices, 2),
c_array, REDUCE(
SEQUENCE(SUM(o)),
SEQUENCE(ROWS(o)),
LAMBDA(v, i,
IF(v <= SUM(INDEX(o, SEQUENCE(i))), INDEX(CHOOSECOLS(u_choices, 1), i), v)
)
),
su_array, DROP(REDUCE("", o, LAMBDA(u, n, VSTACK(u, SEQUENCE(n)))), 1),
s_array, SORTBY(TEXTBEFORE(j_by_row, d, -1), t_array, 1),
a_dot, IFERROR("." & TEXTAFTER(c_array, ".", -1), ""),
ATEXTJOIN(
HSTACK(
s_array,
TEXTBEFORE(c_array, ".", -1, , , c_array) &
IF(
NOT(c_array = " "),
IF(su_array > 1, " (" & su_array & ")" & a_dot, a_dot),
""
)
),
d,
1
)
),
j_by_row
)
)
)
 

jaeiow

Board Regular
Joined
Jun 12, 2022
Messages
119
Office Version
  1. 365
Platform
  1. Windows
Was optimized for reduced length.
Excel Formula:
=LAMBDA(a,h,d,[XD],[AV],[AN],[CT],[N],
 LET(
 c_columns,IF(ISERROR(a),"",a&""),
 t_date,IF(OR(ISOMITTED(XD),NOT(XD)),
  c_columns,
  LET(c,COLUMNS(h),
  th,TOCOL(h),
  mc,MOD(SEQUENCE(ROWS(h)*c)-1,c),
 FORMATDATE(
 FORMATDATE(
 c_columns,
 XLOOKUP("*Date",th,mc+1,0,2),
 "MM/DD/YYYY"),
 XLOOKUP("*Date",th,mc,0,2,-1),
 "MM/DD/YYYY"))),
 c_width,BYCOL(h,LAMBDA(c,LARGE(CELL("width",c),1)))<>0,
 c_visible,FILTER(t_date,c_width),
 c_notempty,NOT(BYCOL(c_visible,LAMBDA(c,AND(c="")))),
 x_count,FILTER(FILTER(CBYCOL(a,
 LAMBDA(x,VSTACK("ct:"&XCOUNT(x,{1,2},1,,,),UNIQUE(XTYPE(x,5),,)))),
  c_width),c_notempty),
 l_sequence,FILTER(FILTER("hl:"&SUBSTITUTE(ADDRESS(1,SEQUENCE(,COLUMNS(h)),4),"1",""),
  c_width),c_notempty),
 h_visible,FILTER(FILTER(BYCOL(h,LAMBDA(x,TEXTJOIN("_",,x))),c_width),c_notempty),
 o_columns,FILTER(c_visible,c_notempty),
 o_stack,VSTACK(x_count,l_sequence,o_columns),
 c_or_s,IF(OR(ISOMITTED(AV),NOT(AV)),
  VSTACK(IF(
 OR(ISOMITTED(CT),NOT(CT)),
 h_visible&d&o_columns,
 h_visible&d&o_stack)),
  VSTACK(h_visible,o_stack)),
 nl_omitbl,ARCHCLS(IFNA(IF(DROP(h_visible,,-1)="","",c_or_s),
  IF(TAKE(h_visible,,-1)="",
 TEXTAFTER(c_or_s,d,,,,c_or_s),
 c_or_s)),
  N),
 j_by_row,BYROW(nl_omitbl,
  LAMBDA(j_row,
  TEXTJOIN(d,IF(OR(ISOMITTED(AV),NOT(AV)),TRUE,FALSE),j_row))),
 IF(AND(TAKE(h_visible,,-1)="",AN=1,OR(ISOMITTED(AV),NOT(AV))),
  LET(t_array,TEXTAFTER(j_by_row,d,-1,,," "),
  u_choices,SORT(ROWSUNQ(t_array,-1,0),1,1),
  o,CHOOSECOLS(u_choices,2),
  c_array,REDUCE(SEQUENCE(SUM(o)),SEQUENCE(ROWS(o)),LAMBDA(v,i,
 IF(v<=SUM(INDEX(o,SEQUENCE(i))),
  INDEX(CHOOSECOLS(u_choices,1),i),v))),
  su_array,DROP(REDUCE("",o,LAMBDA(u,n,VSTACK(u,SEQUENCE(n)))),1),
  s_array,SORTBY(TEXTBEFORE(j_by_row,d,-1),t_array,1),
  a_dot,IFERROR("."&TEXTAFTER(c_array,".",-1),""),
  ATEXTJOIN(HSTACK(s_array,
 TEXTBEFORE(c_array,".",-1,,,c_array)&
  IF(NOT(c_array=" "),
  IF(su_array>1,"("&su_array&")"&a_dot,a_dot),"")),
 d,1)),
  j_by_row))
)
TOFILE(a,h,d,[XD],[AV],[AN],[CT],[N])
a
: array
h: header
d: delimiter
[XD]: transform dates?, 0 or omitted, NO, 1, YES. Req. FORMATDATE
[AV]: concatenate or vstack headers with cells? 0 or omitted, Concat, 1, Vstack
[AN]: number the notes, and sort the output? 0 or omitted, NO, 1, YES (not to be combined with AV = 1). Req. ROWSUNQ and ATEXTJOIN
[CT]: count and types totals rows, that adds column letter, XCOUNT, and XTYPE. 0 or omitted, don't add totals row
[N]: new sequence to feed ARCHCLS. If omitted, retain original column order.
 
Last edited:

jaeiow

Board Regular
Joined
Jun 12, 2022
Messages
119
Office Version
  1. 365
Platform
  1. Windows
To unjoin this list, or it could be a list stacked together, generated by multiple TOFILEs, and get a dynamic table from the columns and data, using the even and odd column indices:
Nome~c~email~r~ID~3~Date~06/27/2021
Nome~c~email~r~ID~6~Date~06/27/2024
Nome~c~email~r~ID~~Date~06/27/2027

Result:

NomeemailIDDate
cr
3​
44374​
cr
6​
45470​
cr
46565​

Lambda:
VBA Code:
BACKTOTABLE = LAMBDA(rng, d,
    LET(
        listing, ATEXTSPLIT(rng, d, 1),
        listmax, COLUMNS(listing) / 2,
        header, UNIQUE(TOROW(CHOOSECOLS(listing, SEQUENCE(listmax, 1, 1, 2))), 1),
        stack, RBYROW(
            listing,
            LAMBDA(x,
                VSTACK(
                    CHOOSECOLS(x, SEQUENCE(listmax, 1, 1, 2)),
                    CHOOSECOLS(x, SEQUENCE(listmax, 1, 2, 2))
                )
            )
        ),
        k, SEQUENCE(ROWS(stack)),
        titleRows, SCAN(1, k, LAMBDA(a, v, IF(ISEVEN(CHOOSEROWS(k, v)), a, v))),
        raw, DROP(
            REDUCE(
                "",
                k,
                LAMBDA(a, v,
                    LET(
                        order, IFERROR(
                            XMATCH(header, CHOOSEROWS(stack, CHOOSEROWS(titleRows, v))),
                            0
                        ),
                        VSTACK(a, INDEX(stack, v, order))
                    )
                )
                )
            ),
            1
        ),
        return, CHOOSEROWS(raw, UNIQUE(EVEN(k))),
        VSTACK(header, return)
    )
);
 
Last edited:

jaeiow

Board Regular
Joined
Jun 12, 2022
Messages
119
Office Version
  1. 365
Platform
  1. Windows
Actually, lets make the error value 99 to feed into index for an #REF error, to trap the error where column names do not occur.
Excel Formula:
TOTABLE = LAMBDA(rng, d,
    LET(
        listing, ATEXTSPLIT(rng, d, 1),
        listmax, COLUMNS(listing) / 2,
        header, UNIQUE(TOROW(CHOOSECOLS(listing, SEQUENCE(listmax, 1, 1, 2))), 1),
        stack, RBYROW(
            listing,
            LAMBDA(x,
                VSTACK(
                    CHOOSECOLS(x, SEQUENCE(listmax, 1, 1, 2)),
                    CHOOSECOLS(x, SEQUENCE(listmax, 1, 2, 2))
                )
            )
        ),
        k, SEQUENCE(ROWS(stack)),
        titleRows, SCAN(1, k, LAMBDA(a, v, IF(ISEVEN(CHOOSEROWS(k, v)), a, v))),
        raw, DROP(
            REDUCE(
                "",
                k,
                LAMBDA(a, v,
                    LET(
                        order, IFERROR(
                            XMATCH(header, CHOOSEROWS(stack, CHOOSEROWS(titleRows, v))),
                            99
                        ),
                        IFERROR(VSTACK(a, INDEX(stack, v, order)),"")
                    )
                )
            ),
            1
        ),
        return, CHOOSEROWS(raw, UNIQUE(EVEN(k))),
        VSTACK(header, return)
    )
);
 

jaeiow

Board Regular
Joined
Jun 12, 2022
Messages
119
Office Version
  1. 365
Platform
  1. Windows
Since the BACKTOTABLE function is not performant, but is dynamic (its going by row, when you might have 100 rows of the same structure)., I'm going to guess that the compressed version is faster, but it makes the raw section less readable.
Excel Formula:
BACKTOTABLE=LAMBDA(rng,d,LET(listing,ATEXTSPLIT(rng,d,1),
 listmax,COLUMNS(listing) / 2,
 header,UNIQUE(TOROW(CHOOSECOLS(listing,SEQUENCE(listmax,1,1,2))),
1),
 stack,RBYROW(listing,LAMBDA(x,VSTACK(CHOOSECOLS(x,SEQUENCE(listmax,1,1,2)),
 CHOOSECOLS(x,SEQUENCE(listmax,1,2,2))))),
 k,SEQUENCE(ROWS(stack)),
 raw,DROP(REDUCE("",k,LAMBDA(a,v,IFERROR(VSTACK(a,INDEX(stack,v,IFERROR(XMATCH(header,CHOOSEROWS(stack,CHOOSEROWS(SCAN(1,k,LAMBDA(a,v,IF(ISEVEN(CHOOSEROWS(k,v)),
 a,v))),v))),999))),""))),1),
 VSTACK(header,CHOOSEROWS(raw,UNIQUE(EVEN(k)))))
   )
 

jaeiow

Board Regular
Joined
Jun 12, 2022
Messages
119
Office Version
  1. 365
Platform
  1. Windows
Here is a solution for the one performance issue, although this is now lacking in other places.

Excel Formula:
BACKTOTABLE=LAMBDA(rng,d,
LET(
listing, ATEXTSPLIT(rng, d, 1),
listmax, COLUMNS(listing) / 2,
header, UNIQUE(TOROW(CHOOSECOLS(listing, SEQUENCE(listmax, 1, 1, 2))), 1),
subs, RBYROW(listing, LAMBDA(x, CHOOSECOLS(x, SEQUENCE(listmax, 1, 2, 2)))),
data, ATEXTJOIN(RBYROW(listing, LAMBDA(x, CHOOSECOLS(x, SEQUENCE(listmax, 1, 1, 2)))), , 1),
stack, ATEXTSPLIT(
  DROP(REDUCE("", UNIQUE(data), LAMBDA(a, v,LET(f, (data = v),j, COUNTA(FILTER(data, f)),i, SEQUENCE(j + 1) - 1,DROP(VSTACK(a,  IFNA(EXPAND(v, j + 2, , NA()), INDEX(FILTER(ATEXTJOIN(subs, , 1), f), i))),   -1)))
),1),",",1),
k, SEQUENCE(ROWS(stack)),
titleRows, SCAN(1, k, LAMBDA(a, v, IF(AND(ISNA(XMATCH(CHOOSEROWS(stack, v), header))), a, v))),
raw, DROP(REDUCE("",k,LAMBDA(a, v, LET(order, IFERROR(XMATCH(header, CHOOSEROWS(stack, CHOOSEROWS(titleRows, v))), 999),IFERROR(VSTACK(a, INDEX(stack, v, order)), ""))
)),1),
return, FILTER(raw, (CHOOSECOLS(raw, 1) <> 0) * (CHOOSECOLS(raw, 1) <> INDEX(header, 1))),
VSTACK(header, return)
)
   )
 

jaeiow

Board Regular
Joined
Jun 12, 2022
Messages
119
Office Version
  1. 365
Platform
  1. Windows
Here it gets faster identifying rows with stacked sequences
Excel Formula:
BACKTOTABLE = LAMBDA(rng, d,LET(listing, ATEXTSPLIT(rng, d, 1),listmax, COLUMNS(listing) / 2,header, UNIQUE(TOROW(CHOOSECOLS(listing, SEQUENCE(listmax, 1, 1, 2))), 1),headers, RBYROW(listing,LAMBDA(x, TEXTJOIN(",", FALSE, CHOOSECOLS(x, SEQUENCE(listmax, 1, 1, 2))))),subs, RBYROW(listing,LAMBDA(x, TEXTJOIN(",", FALSE, CHOOSECOLS(x, SEQUENCE(listmax, 1, 2, 2))))),
pullunq, UNIQUE(headers),plflt, BYROW(pullunq, LAMBDA(x, COUNTA(FILTER(headers, headers = x)))),pulls, RBYROW(plflt, LAMBDA(x, SEQUENCE(x + 1))),rptrows, RWSRPT(HSTACK(FILTER(SEQUENCE(ROWS(pulls)), pulls = 1), plflt + 1)),stack, ATEXTSPLIT(DROP(REDUCE("",pullunq,LAMBDA(a, v,LET(f, (headers = v),
j, COUNTA(FILTER(headers, f)),
i, SEQUENCE(j + 1) - 1,
DROP(VSTACK(a,
        IFNA(EXPAND(v, j + 2, , NA()), INDEX(FILTER(subs, f), i))),
    -1)))),1),",",1),y, SEQUENCE(ROWS(stack)),titleRows, CHOOSEROWS(y, rptrows),raw, DROP(REDUCE("",y,LAMBDA(a, v,IFERROR(VSTACK(a,
INDEX(stack,
    v,
    IFERROR(XMATCH(header, CHOOSEROWS(stack, CHOOSEROWS(titleRows, v))),
        999))),""))),1),th, TOCOL(header),msch, MOD(SEQUENCE(COLUMNS(header)), COLUMNS(header)),VSTACK(header,FORMATDATE(FORMATDATE(CHOOSEROWS(raw, FILTER(y, y <> rptrows)),XLOOKUP("Date", th, msch, 0, 2),"mmm-d-yy"),XLOOKUP("*Date", th, msch, 0, 2, -1),"mmm-yy"))))
 

Forum statistics

Threads
1,186,326
Messages
5,957,211
Members
438,293
Latest member
ginkycart

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top