Converting the data from Rows to columns. Or perhaps a Pivot table

Occasional Contributor

I have data in one column, which is repeated in several rows.  Those rows have data that I need to restructure in columns.  Maybe the sample below makes more sense what I need to do.  I need this Range marked as DATA 1 to be converted to the Range marke as DATA 2.



need the above Range converted to this Range below:




any and all the help is extremely appreciated

8 Replies


Delete all blank rows. Delete all total rows. Delete all duplicate header rows.

You can build a pivot table from what is left.


@Detlef Lewin

Thank you, Detlef. Much appreciated. Any suggestions on how to put the right values in the pivot table to get the desired result?


Name and Payroll ID in rows area.

Clock in date in columns area.

Total Paid in values area.



That could be Power Query if you consider such option


Thank you very much, Detlef.
Hi Sergei, this would be awesome!! How do I go about doing it? Any suggestions / directions would be great, and I can further research it on different videos please.
@Sergei Baklan

I've tried looking up Power Query. Maybe if you can narrow / pinpoint the direction, I can have a better success. Thank you much in advance.


If to work with Power Query in any case you need to invest some time and, most probably, money, to become more or less familiar with the tool.

You may start from support pages

About Power Query in Excel - Excel (

Power Query for Excel Help - Excel (

which explain basics. Next I'd take some course or go through the book ( like this one  which recently appeared).

Less systematic approach is to google for concrete questions - lot of resources in the net.