Merge columns

[Rachel_Dor_75]

We interview students and each student goes through 2 interviews by 2 different interviewers. Each interviewer has their own column with their interview time. See an example.

If we take the interviewer Ross as an example, how can I get to the point where I have only one column of the interviewer's name and his interview hours? I want to get to this view-

[Kiersten_Kollin]

Hi! I think we have similar use cases and I just asked this in the community and was able to receive some amazing help from @TheTimeSavingCo. Check out the thread here using rollup fields. 

[Rachel_Dor_75]

I have to admit that I just can't understand the whole issue of rollup fields...
I read the explanations and still can't figure out how to do it 🙄

[TheTimeSavingCo]

Hi Rachel, it seems like you could just filter the "Interviewer" field by the value "Ross" to get to the screenshot you want, but you already know about filtering as evidenced by your first screenshot, so I'm definitely missing something I think. 

Do you eventually want to have a single record that represents "Ross" with all of their hours added together?  Once I better understand your eventual end goal I can see what I can do to assist

(And hi Kiersten!  Glad I could help!!)

[Meitar23]

Hi Adam,

(This is Rachel, I connected with my firm account 😊).

Thanks a lot for trying to help. really appreciate it! 

The first screenshot is a shot of the base itself. You are right, I eventually want to reach a situation where I have one column with the name of the interviewer and another column with the hours. (As you wrote: to have a single record that represents "Ross" with all of their hours added together). Like the second screenshot I created for the example. 

Just need to take into account that besides "Ross"  there are 80 other interviewers, and if I want to have a single record  for each one of them with all of their hours added together, and I will use a formula, does that mean I have to use it 80 times?

[TheTimeSavingCo]

Ahh thanks for the details!

This is possible, but how we do this changes depending on how your data gets created in your original table

A possible suggestion would be:
1. Create a new table called "People" or something
2. Change "Interviewer 1" and "Interviewer 2" fields to be linked fields to the "People" table
3. In "People", create a rollup field from "Interviewer 1" on the field "Duration 1" with the formula `SUM(values)`
4. In "People", create a rollup field from "Interviewer 2" on the field "Duration 2" with the formula `SUM(values)`
5. Create a formula field that will sum up the values from the fields created in step 3 and 4

Looking at the duration field though, I'm not entirely sure I understand the workflow as they seem to list interviews that last for 12 hours?

[Rachel_Dor_75]

Unfortunately, this solution does not help me 😕
I found a partial solution how to show only Ross: https://prnt.sc/4wIQ2VeOzdAb
I opened a new column called "Interviewer" and inserted the following formula into it:
IF(
OR(
{Interviewer 1} = "Ross",
{Interviewer 2} = "Ross"
),
"Ross"
)

The problem is that I have about 90 interviewers.... How do I do this?

[VatanB]

Hey Rachel,

Looking at your structure, I'd suggest you make a few changes.  See the below base

https://airtable.com/shrdlMxBVlNwXm0HV

As you can see on the Interviews table, we can now use groups to get the required result.

I'm sure there are other ways to achieve the same thing but to me, this is the simplest method I can think of doing this without having to resort to a lot of formula's and complex table structures.

I hope this helps!

All the very best

Vatan

[Steve_Haysom]

Hi Rachel

From a database point of view, I'm thinking you should start with 3 tables: students, interviews and interviewers. So what you need to do is create interview records for each interview in the interviews table. 

In the interview table, you would have a student field (linked to a student record), a date and hour field, and an interviewer field (linked to an interviewer).

Then when looking at each interviewer record, you would have all the interviews for each interviewer. With this structure set up, you could then even incorporate a third interview if you wanted to without making any changes to the table structure.