Format Table - Align Cell Text with Appropriate Column Header

[Emily Berg]

Hello Community!  I am tagging @Katerina Kovriga _Stiltsoft_ as she has answered several of my previous questions.

I have a table that I would like to sort the values into the appropriate column header match.  See attached input and desired output tables.  Any advise is appreciated!

Emily 

[Emily Berg · Accepted solution]

Sorry for the submission, I was able to solve the problem myself using the StiltSoft Advanced Use Case information.

I used the following SQL:

SELECT *,
CASE WHEN (MID(T1.'Text',1,6)) = "BPFAAA" THEN T1.'Text' END AS 'BPFAAA',
CASE WHEN (MID(T1.'Text',1,6)) = "BPFBBB" THEN T1.'Text' END AS 'BPFBBB',
CASE WHEN (MID(T1.'Text',1,6)) = "BPFCCC" THEN T1.'Text' END AS 'BPFCCC'
FROM T1

[Katerina Rudkovskaya _Stiltsoft_]

Hi @Emily Berg,

Glad that you’ve found an answer yourself. Let me also suggest an option if not for this case, then for the future ones – how to avoid using filters (here I mean “BPFAAA”, “BPFBBB”, etc.) in the query. It may come in handy if there are many different variants and if they are changing dynamically (for example, coming from the Jira Issues macro).



SELECT 'Filter',
FORMATWIKI(SUM('Text' + "\\ ")) AS 'Text'
FROM (SELECT 'Text'->split("-")->0 AS 'Filter',
'Text'
FROM T*)
GROUP BY 'Filter'
Here I automatically split the first part of the ‘Text’ field (you do smth similar in your query) and group my data by these first parts.

Then I also need to transpose my result table:

And you may do the same thing using the Table Spreadsheet macro:

=IFERROR(IF(SEARCH(B$1,$A2),$A2, " "), " ")
Copy/paste this formula to all the table cells.



Unfortunately, the Table Spreadsheet macro doesn’t support the Jira Issues macro as a source table. We have such feature in our roadmap, and once it is implemented, I’ll get back here to notify you.
But if your case is based on the manually created table or csv/xlsx file, then you may also use this option.

[Alexey Mikhaylov _Stiltsoft_]

Hello @Emily Berg ,

Let me inform you we've released a new macro - Spreadsheet from Table - which allows transferring your regular table or a table outputting a macro's content (such as, for example, Page Properties Report or Jira macro) into Table Spreadsheet with all its tools. It is available starting from Table Filter and Charts for Confluence version 10.0.0.