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

%3CLINGO-SUB%20id%3D%22lingo-sub-2658293%22%20slang%3D%22en-US%22%3EConverting%20the%20data%20from%20Rows%20to%20columns.%20Or%20perhaps%20a%20Pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2658293%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20data%20in%20one%20column%2C%20which%20is%20repeated%20in%20several%20rows.%26nbsp%3B%20Those%20rows%20have%20data%20that%20I%20need%20to%20restructure%20in%20columns.%26nbsp%3B%20Maybe%20the%20sample%20below%20makes%20more%20sense%20what%20I%20need%20to%20do.%26nbsp%3B%20I%20need%20this%20Range%20marked%20as%20DATA%201%20to%20be%20converted%20to%20the%20Range%20marke%20as%20DATA%202.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22AbuAliG_0-1629243999904.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F303925i0B837EDE0D23EFD9%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22AbuAliG_0-1629243999904.png%22%20alt%3D%22AbuAliG_0-1629243999904.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eneed%20the%20above%20Range%20converted%20to%20this%20Range%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22AbuAliG_1-1629244066957.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F303926iD2D29BAF0B3BDEE6%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22AbuAliG_1-1629244066957.png%22%20alt%3D%22AbuAliG_1-1629244066957.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eany%20and%20all%20the%20help%20is%20extremely%20appreciated%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2658293%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2658352%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20the%20data%20from%20Rows%20to%20columns.%20Or%20perhaps%20a%20Pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2658352%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1130821%22%20target%3D%22_blank%22%3E%40AbuAliG%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDelete%20all%20blank%20rows.%20Delete%20all%20total%20rows.%20Delete%20all%20duplicate%20header%20rows.%3C%2FP%3E%3CP%3EYou%20can%20build%20a%20pivot%20table%20from%20what%20is%20left.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2658422%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20the%20data%20from%20Rows%20to%20columns.%20Or%20perhaps%20a%20Pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2658422%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%2C%20Detlef.%20Much%20appreciated.%20Any%20suggestions%20on%20how%20to%20put%20the%20right%20values%20in%20the%20pivot%20table%20to%20get%20the%20desired%20result%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2658793%22%20slang%3D%22en-US%22%3ERe%3A%20Converting%20the%20data%20from%20Rows%20to%20columns.%20Or%20perhaps%20a%20Pivot%20table%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2658793%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1130821%22%20target%3D%22_blank%22%3E%40AbuAliG%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EName%20and%26nbsp%3BPayroll%20ID%20in%20rows%20area.%3C%2FP%3E%3CP%3EClock%20in%20date%20in%20columns%20area.%3C%2FP%3E%3CP%3ETotal%20Paid%20in%20values%20area.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
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.

 


AbuAliG_0-1629243999904.png

need the above Range converted to this Range below:

 

AbuAliG_1-1629244066957.png

 

any and all the help is extremely appreciated

8 Replies

@AbuAliG 

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?

@AbuAliG 

Name and Payroll ID in rows area.

Clock in date in columns area.

Total Paid in values area.

 

@AbuAliG 

That could be Power Query if you consider such option

image.png

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.

@AbuAliG 

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 (microsoft.com)

Power Query for Excel Help - Excel (microsoft.com)

which explain basics. Next I'd take some course or go through the book ( like this one https://www.excelguru.ca/blog/2021/08/02/master-your-data-is-now-available/  which recently appeared).

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