I use this formula in Google Sheets:

=SORT({SORT({'Discord-D'!B2:B, 'Discord-D'!A2:A, ARRAYFORMULA(IF('Discord-D'!G2:G = "", "", IF('Discord-D'!C2:C <> "", "Removed", "Processing"))), 'Discord-D'!H2:H, ARRAYFORMULA(IF('Discord-D'!C2:C <> "", 'Discord-D'!G2:G, IFERROR(REPLACE('Discord-D'!G2:G, LEN('Discord-D'!G2:G)-3, 4, "****")))), 'Discord-D'!I2:I}, ROW('Discord-D'!A2:A), FALSE); SORT({'Facebook-D'!B2:B, 'Facebook-D'!A2:A, ARRAYFORMULA(IF('Facebook-D'!E2:E = "", "", IF('Facebook-D'!C2:C <> "", "Removed", "Processing"))), 'Facebook-D'!F2:F, ARRAYFORMULA(IF('Facebook-D'!C2:C <> "", 'Facebook-D'!E2:E, IFERROR(REPLACE('Facebook-D'!E2:E, LEN('Facebook-D'!E2:E)-3, 4, "****")))), 'Facebook-D'!G2:G}, ROW('Facebook-D'!A2:A), FALSE); SORT({'Instagram-D'!B2:B, 'Instagram-D'!A2:A, ARRAYFORMULA(IF('Instagram-D'!E2:E = "", "", IF('Instagram-D'!C2:C <> "", "Removed", "Processing"))), 'Instagram-D'!F2:F, ARRAYFORMULA(IF('Instagram-D'!C2:C <> "", 'Instagram-D'!E2:E, IFERROR(IF(LEN(REGEXEXTRACT('Instagram-D'!E2:E, "com/(. )")) > 4, REPLACE('Instagram-D'!E2:E, LEN('Instagram-D'!E2:E)-3,4, "****"), REPLACE('Instagram-D'!E2:E, LEN('Instagram-D'!E2:E)-1,2, "**"))))), 'Instagram-D'!G2:G}, ROW('Instagram-D'!A2:A), FALSE); SORT({'TikTok-D'!B2:B, 'TikTok-D'!A2:A, ARRAYFORMULA(IF('TikTok-D'!E2:E = "", "", IF('TikTok-D'!C2:C <> "", "Removed", "Processing"))), 'TikTok-D'!F2:F, ARRAYFORMULA(IF('TikTok-D'!C2:C <> "", IFERROR(IF(LEN(REGEXEXTRACT('TikTok-D'!E2:E, "https://www.tiktok.com/@(.*?)/")) = 4, REPLACE('TikTok-D'!E2:E, LEN("https://www.tiktok.com/@" & REGEXEXTRACT('TikTok-D'!E2:E, "https://www.tiktok.com/@(.*?)/"))-1, 2, "**"), REPLACE('TikTok-D'!E2:E, LEN("https://www.tiktok.com/@" & REGEXEXTRACT('TikTok-D'!E2:E, "https://www.tiktok.com/@(.*?)/"))-3, 4, "****"))), IFERROR(IF(LEN(REGEXEXTRACT('TikTok-D'!E2:E, "https://www.tiktok.com/@(.*?)/")) = 4, REPLACE(REPLACE('TikTok-D'!E2:E, LEN("https://www.tiktok.com/@" & REGEXEXTRACT('TikTok-D'!E2:E, "https://www.tiktok.com/@(.*?)/"))-1, 2, "**"), LEN('TikTok-D'!E2:E)-3, 4, "****"), REPLACE(REPLACE('TikTok-D'!E2:E, LEN("https://www.tiktok.com/@" & REGEXEXTRACT('TikTok-D'!E2:E, "https://www.tiktok.com/@(.*?)/"))-3, 4, "****"), LEN('TikTok-D'!E2:E)-3, 4, "****"))))), 'TikTok-D'!H2:H}, ROW('TikTok-D'!A2:A), FALSE); SORT({'YouTube-D'!B2:B, 'YouTube-D'!A2:A, ARRAYFORMULA(IF('YouTube-D'!E2:E = "", "", IF('YouTube-D'!C2:C <> "", "Removed", "Processing"))), 'YouTube-D'!G2:G, ARRAYFORMULA(IF('YouTube-D'!C2:C <> "", 'YouTube-D'!E2:E, IFERROR(REPLACE('YouTube-D'!E2:E, LEN('YouTube-D'!E2:E)-3, 4, "****")))), ARRAYFORMULA(IFERROR(REPLACE('YouTube-D'!F2:F, LEN('YouTube-D'!F2:F)-3, 4, "****")))}, ROW('YouTube-D'!A2:A), FALSE)}, 1, FALSE)

The YouTube array contains an extra column compared to the rest. Initially, it would fail because not all the arrays had the same number of columns in them. I solved it by inserting a blank column in each of the sheets and referencing that one (the last column reference in each array except YouTube, so 'Discord-D'!I2:I, 'Facebook-D'!G2:G, 'Instagram-D'!G2:G and 'TikTok-D'!H2:H). Is there a better way I can achieve this (for example by creating a virtually blank column instead of actually needing to have one in reality)?

CodePudding user response：

there are various ways how to create a virtual column (or row). for example, to avoid array errors we can create a virtual array manually like:

={A1:A5, {"";"";"";"";""}}

to do it dynamically we can use divide by zero error and turn it into blanks:

=IFERROR(ROW(A2:A)/0, )

another way is within query where we can insert virtual column as:

=QUERY(A:C, "select A,B,C,' ' label ' '''")

but you can do it even with a single if:

=IF(A2:A,,) 

and a lot of people use a sequence with substitute:

=SUBSTITUTE(SEQUENCE(ROWS(A2:A), 1, 1, 0), 1, )

of course, 2nd, 4th and 5th fx need ARRAYFORMULA wrapping

another popular way is to reference an existing column that is for sure empty like X:

={A:C, X:X}

and in some cases, you can even reference a non-existent column.
for example, if your sheet has A-Z columns you can use:

={A:C, XX:XX}

and lambda lovers will appreciate:

=INDEX(LAMBDA(x, IFERROR(x/0))(A2:A))

or:

=INDEX(LAMBDA(x, x)(IF(A2:A,,)))