Suggestions on importing public holiday data

Ron Mc

Our business runs in multiple states, and public holidays vary by state.

I can import a list of people from HR

People

Name Jurisdiction

John NSW

Mary Vic

I can import a list of public holidays by jurisdiction

Holidays

Holiday Jurisdiction Date

Holiday 1 NSW 1/1/25

Holiday 2 NSW 1/3/25

Holiday 3 Vic 1/4/25

Holiday 4 Vic 1/5/35

I want to be able to import the above to sheets and then automatically create a resource plan that looks like this

Name Task Name Start Date End Date

John Holiday 1 1/1/25 1/1/25

John Holiday 2 1/3/25 1/3/25

Mary Holiday 3 1/4/25 1/4/25

Mary Holiday 4 1/5/25 1/25/25

I cannot get out of the HR system resources and the public holidays that they are entitled to.

Any suggestions would be appreciated.

SSFeatures

@Andrée Starå Is there a way to do this without using something like the API or Smartsheet Pivot?

My initial thought is to do a JOIN/COLLECT but the result would be something like this, which definitely isn't ideal:

Name Holidays Start Dates End Dates
John | Holiday1,Holiday2 | 1/1/25,1/3/25 | 1/1/25,1/3/25