Need guidance on how to merge row wise cells into one cell.

[anonymous008_G]

Hi All,

I have input data where the top 3 rows are headings of the table. I want to merge them into one cell so I can put them as headers. Can someone please help determine the best way to transform this data?

Below is my input data and expected output format for reference.

Input Data

[Null]

[Null]

[Null]

Security

1 Yr

2 Yrs*

3 Yrs*

4 Yrs*

5 Yrs*

10 Yrs*

1 Yr

1 Yr

1 Yr

1 Yr

1 Yr

1 Yr

1 Yr

1 Yr

1 Yr

1 Yr

[Null]

[Null]

[Null]

Category

To

To

To

To

To

To

To

To

To

To

To

To

To

To

To

To

[Null]

[Null]

Currency

Description

12/31/2023

12/31/2023

12/31/2023

12/31/2023

12/31/2023

12/31/2023

12/31/2014

12/31/2015

12/31/2016

12/31/2017

12/31/2018

12/31/2019

12/31/2020

12/31/2021

12/31/2022

12/31/2023

Expected Output

[Null]

[Null]

Currency

Security Category Description

1 Yr to 12/31/2023

2 Yrs* to 12/31/2023

3 Yrs* to 12/31/2023

4 Yrs* to 12/31/2023

5 Yrs* to 12/31/2023

10 Yrs* to 12/31/2023

1 Yr to 12/31/2014

1 Yr to 12/31/2015

1 Yr to 12/31/2016

1 Yr to 12/31/2017

1 Yr to 12/31/20188

1 Yr to 12/31/2019

1 Yr to 12/31/2020

1 Yr to 12/31/2021

1 Yr to 12/31/2022

1 Yr to 12/31/2023

[aatalai · Accepted solution]

I would start by transposing the data so the data you want to join is in a row. Use the record ID tool, then the summarise tool, grouping by record ID just generating and the concatenating the other fields, then crosstab tool for names form the newly concatenated field and values from record ID and then go from there let me know how you get on 

[anonymous008_G · Accepted solution]

Thanks for your response. I tried this but couldn't proceed after summaries tool. can you please share sample workflow so that i will follow the steps in real data.

[anonymous008_G · Accepted solution]

I have done this but different way. thanks for your response. 

[CoffeeBean1]

I'm curious how you did this, if you could share your solution, I have exact same question, can't figure out simplest way to do it that will also be dynamic enough for any future input. 

[CoffeeBean1]

Hi, can you please share your solution, I have same exact problem but can't quite come up with the best dynamic approach.