Merging 3 rows with same ID into 1

[bk3171]

Hello,

I am trying to create variance report workflow. Data for each employee is given in 3 different rows. There are hundreds of employees and even more components in this report. Need to connect it into one row. Could you give any advise how to achieve it? Example below:

Example input file:

Employee ID	001 Brut de base Previous Month	001 Brut de base Current Month	001 Brut de base Variance	001 Brut de base Precentage	040 ADJUSTMENT GROSS Previous Month	040 ADJUSTMENT GROSS Current Month	040 ADJUSTMENT GROSS Variance	040 ADJUSTMENT GROSS Precentage
07728103	7,384.62	7,688.25	303.63	4.11
07728103
07728103					26.31	26.97	0.66	2.51
15664302					26.31	26.31	0	0
15664302	5,692.31	5,863.79	171.48	3.01
15664302

Output should be:

Employee ID	001 Brut de base Previous Month	001 Brut de base Current Month	001 Brut de base Variance	001 Brut de base Precentage	040 ADJUSTMENT GROSS Previous Month	040 ADJUSTMENT GROSS Current Month	040 ADJUSTMENT GROSS Variance	040 ADJUSTMENT GROSS Precentage
07728103	7,384.62	7,688.25	303.63	4.11	26.31	26.97	0.66	2.51
15664302	5,692.31	5,863.79	171.48	3.01	5,692.31	5,863.79	171.48	3.01

[PhilipMannering · Accepted solution]

It looks like you're consolidating employees by just taking any non-empty cell? You could take the max of each column grouped by employee Id in the Summarize Tool. See example attached,

[PangHC]

we can use transpose and crosstab to do so. it allow any number of columns.

however, the sort of header may vary. hence use field info + recordID to use as temp header.

then rename via dynamic rename.

 

[jdminton]

@bk3171 definitely use Philip's answer if that works for your scenario. I was thinking there is a possibility that one or more column may contain multiple rows of data versus just being empty. If that's the case, try this workflow.

 

[bk3171]

Thank you Philip! Works perfect