Vlookup isn't going to work...

[Mrs. T]

How can I create formulas to fill in the blue area of my attached spreadsheet.

Data is in sheet 2 - in rows. Each employee has a row for each entry.

I need sheet 2 to take each type (regular, holiday, overtime) and convert it into columns.

I want a column for each type of pay with the employees listed in column A.

I'm not familiar with macros...so I have to find a formula to enter into the blue areas...

 

[alansidman]

With Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Employee number"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ([TIME] <> null)),
    #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"[TIME]), "TIME", "TOTAL")
in
    #"Pivoted Column"

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

Book3
	A	B	C	D	E	F	G	H	I
1	Employee number	TIME	TOTAL			Employee number	REGULAR	OVERTIME	HOLIDAY
2	123	REGULAR	40			123	40	2	8
3		OVERTIME	2			321
4		HOLIDAY	8			456	32	5	8
5	456	REGULAR	32			654	8		8
6		OVERTIME	5			789	30		8
7		HOLIDAY	8			987	32		8
8	789	REGULAR	30
9		OVERTIME
10		HOLIDAY	8
11	987	REGULAR	32
12
13		HOLIDAY	8
14	654	REGULAR	8
15		HOLIDAY	8
16	321	REGULAR
Sheet1

 

[Mrs. T]

I cannot download this to my work computer. Is there another way to do this?

 

[alansidman]

read the third link provided as to how to copy and paste the code. You already have Power Query in O365. No download is required.