Represent attendance data with lookup function

N

Nagaraj475

New member
Joined
Apr 6, 2014
Messages
29
Reaction score
0
Points
0
Location
India
Excel Version(s)
Excel2010
Hello all,

The attendance data I have is listed vertically. I need it to be listed horizontally as specified in Sheet 2. Since the names are same for multiple dates, I'm unable to get help of Vlookup or Index function or paste Transpose. Got some success using the AND function but it was giving only one data for all the dates.

AttendanceFileHelp.pngAttendanceFileHelp.png View attachment AttendanceFileHelp.xlsx

Thank you for the help.
 
Convert your attendance date into a table, and then use this power query:

Code: 
let    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Sl.", Int64.Type}, {"Employee ID", Int64.Type}, {"Name", type text}, {"Date", type date}, {"Attendance Code", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Sl.", "Employee ID"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns", {{"Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns", {{"Date", type text}}, "en-US")[Date]), "Date", "Attendance Code")
in
    #"Pivoted Column"
 
Last edited:
Thank you, Bernie for your time and help. I'm not familiar with power query. I will try to see how it works. Thanks again.
 
You could also make a pivot table with the Employee Name and Date columns. Set Employee Name to rows and Date to columns. Copy and paste this table into a different sheet. Then use INDEX and MATCH to pull over the correct Attendance Code for each employee on each date. I have reattached your workbook for reference.
 

Attachments

  • AttendanceFileHelp.xlsx
    29.4 KB · Views: 11
You must log in or register to reply here.
Back
Top