Topic: Getting an overview of submissions with more than one topic (Read 7155 times)

Several authors have chosen more than one topic for their submissions.
Now we would like to get an overview of all of those submissions with more than one topic.

How can we retrieve that kind of information with ConfTool Pro?
How can we count the number of topics chosen per submission?

If you would like to get an overview of all submissions with more than one topic, please use the data export function of ConfTool.
You can find it on this page:
Overview => Data Import and Export => Export Data

Please scroll down to "Export Submission and Review Information, Reviewers and Session Details", select "Export Contributions" and additionally the option "List several authors, organizations etc. in separate lines instead of using "," or ";" as separator." (see image 1).

Finally, click the button "Create Export File" and save the file on your hard disk.
You will then get a detailed Excel list of all contributions including those with several topics.
The column "topics" lists all topics of a contribution that the author has selected during the submission. Several topics are listed in several lines in one cell (see image 2).

You can now count the number of topics by counting the number of lines in that cell. Please add a new column to count the number of topics right of the column "topics" of your Excel sheet (see image 2).

Then enter the following function in this new column:
=LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(10),""))+1

A2 refers to the cell that contains the topics that will be counted. Please replace all occurrences of A2 in the above code with the cell ID containing the topics of your sheet. If the calculation works and a result is displayed, please copy the formula of this field to all other fields of the column.

The formula uses LEN, SUBSTITUTE, and CHAR functions: First, the LEN function counts total characters in the cell A2. Next SUBSTITUTE removes all "new line" codes from the text in A2 by looking for CHAR(10) which represents the character code for the new line code. The second LEN returns the number of characters without new lines codes. Finally, the second count is subtracted from the first one (giving the number of new line codes found), and 1 is added to the final result, since the number of lines is the number of new line codes plus 1.

If you are using the German version of Excel, please use the following formula instead:
=LÄNGE(A2)-LÄNGE(WECHSELN(A2;ZEICHEN(10);""))+1