Concatenating Rows of Data

[Deano478]

Hi All,

I'm currently facing what might be a simple issue that I just cant seem to solve. I have data like so:

Column 1	Column 2	Column 3	Column 4
Salary	Test	Bonuses	Holiday
	Rate @2%		Test Rate @4%

And what I need to do is produce an output like this I've attempted a few different approaches all of which gave me no luck:

Column 1	Column 2	Column 3	Column 4
Salary	Test Rate @ 2 %	Bonuses	Holiday Test Rate @4%

I know this is possible but I can't seem to figure it out.

Cheers for any help in advance

[DataNath · Accepted solution]

Hi @Deano478, I'm guessing this is repeating and doesn't just happen for 2 rows? If so, here's how I'd go about this:

1) Assign a RecordID based on one of alternating columns being blank/not

2) Transpose our data, grouping on this RecordID

3) Re-Cross Tab our data, once again grouped by this ID and concatenating our values

4) Simple Dynamic Rename to tidy up underscores in field names

Start:

End:

[Deano478]

@DataNath thats worked like a charm I knew I was just over complicating things many thanks to you 

[Deano478]

Hey @DataNath  sorry about this I just came across one issue for certain columns I have data like so is it possible to adopt the current solution to work on them too? The data looks like so:

Column 1	Column 2	Column 3	Column 4	Column 5	Column 6	Column 7	Column 8	Column 9	Column 10
Salary	Test	Bonuses	Holiday	PILON	EE	ER	Advance	VHI	DB
	Rate @2%		Test Rate @4%		PRSA	PRSA			Cheque
				P023	D001	D002	D003	D004	D005

I need to again get it like so:

Column 1	Column 2	Column 3	Column 4	Column 5	Column 6	Column 7	Column 8	Column 9	Column 10
Salary	Test Rate @2%	Bonuses	Holiday Test Rate @4%	PILON P023	EE PRSA D001	ER PRSA D002	Advance D003	VHI D004	DB Cheque D005

I gave it a good stab trying to adapt your solution but Tripped over myself again.

My apologies for asking this follow up question only now

[DataNath]

Hey @Deano478, what does your result look like? I've just ran this through my workflow and get the expected output:

The one thing it may be is that your fields aren't null/empty but have a space. If that's the case you could just use a Formula tool and trim whitespace from the first column before the Multi-Row.