Are you in the loop? Keep up with the latest by making sure you're subscribed to Community Announcements. Just click Watch and select Articles.

Showing results for 
Search instead for 
Did you mean: 
Sign up Log in

Earn badges and make progress

You're on your way to the next level! Join the Kudos program to earn points and save your progress.

Deleted user Avatar
Deleted user

Level 1: Seed

25 / 150 points

Next: Root


1 badge earned


Participate in fun challenges

Challenges come and go, but your rewards stay with you. Do more to earn more!


Gift kudos to your peers

What goes around comes around! Share the love by gifting kudos to your peers.


Rise up in the ranks

Keep earning points to reach the top of the leaderboard. It resets every quarter so you always have a chance!


Format Table - Align Cell Text with Appropriate Column Header

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!




1 answer

1 accepted

1 vote
Answer accepted

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


I used the following SQL:

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'

Katerina Rudkovskaya _Stiltsoft_
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
Jan 26, 2023

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

Thu 5-1.png

SELECT 'Filter',
FORMATWIKI(SUM('Text' + "\\ ")) AS 'Text'
FROM (SELECT 'Text'->split("-")->0 AS 'Filter',
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:

Thu 5-2.png

Thu 5-3.png

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.

Thu 5-4.png

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.

Like # people like this

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.

Like # people like this

Suggest an answer

Log in or Sign up to answer
AUG Leaders

Atlassian Community Events