How can I concatenate every even numbered column with the previous column?

[Ashman2304]

Hi all,

Hope someone can help with my question! I have a data set that currently looks something like this:

2000-01-01	F2	2000-01-02	F3	2000-01-03	F5
Name 1	Surname 1	Name 4	Surname 4	Name 7	Surname 7
Name 2	Surname 2	Name 5	Surname 5	Name 8	Surname 8
Name 3	Surname 3	Name 6	Surname 6	Name 9	Surname 9

And I am aiming to concatenate Name and Surname into the dated columns, as below:

2000-01-01	2000-01-02	2000-01-03
[Name 1] + " " + [Surname 1]	[Name 4] + " " + [Surname 4]	[Name 7] + " " + [Surname 7]
[Name 2] + " " + [Surname 2]	[Name 5] + " " + [Surname 5]	[Name 8] + " " + [Surname 8]
[Name 3] + " " + [Surname 3]	[Name 6] + " " + [Surname 6]	[Name 9] + " " + [Surname 9]

Does anyone know how I could do this? Unfortunately, the dates change with each file so I can't manually concatenate based on field name. I was wondering if I could use a method that looks for all even numbered columns and concatenates to the previous column?

Any help would be great!

[ShankerV]

.

[FinnCharlton · Accepted solution]

Hi @Ashman2304 , here is a way you can do it:

[ShankerV]

Kindly ignore, as I missed to see the heading will change dynamically

[OllieClarke]

Hi @Ashman2304 here's a way to do it:

The multi-row looks for the hyphen in the date, so if your other column names have a hyphen then you could be more precise with the following formula (still grouped by recordID)

IF Regex_MAtch([Name],'\d{4}-\d{2}-\d{2}') THEN [Name] ELSE [row-1:name]
ENDIF

This uses RegEx to look for the date structure ####-##-##

Hope that helps,

Ollie

[Ashman2304]

---

@FinnCharlton wrote:

Hi @Ashman2304 , here is a way you can do it:

---

Thank you mate, looks so easy when you do it like this!