Converting .csv into usable data

[muhleebbin]

Can someone assist in helping me turn a .csv export into something usable?

Currently the export has the following column headers:

Date​

Job​

Time In​

Time Out​

Cost Code​

Hours​

Earn Code​

Phase​

Status​

Within the Date column, there are names of our employees. For example row 2 is the employees name (rest of row is blank) and then rows 3-7 are date values and subsequent entries in the rest of the columns. The number of rows does vary employee by employee because they could be doing work for multiple jobs in a day. I'm trying to add a column a on sheet2 so that it brings in the employee name and then the rest of the values to create a pivot table.

Ultimately what i'm trying to do is find a specific Cost Code (PTO) under each employee, so if there's another way to look up under the rows of each employee the cost code PTO without doing the first ask, that would be most helpful!

 

[Toadstool]

Hi Muhleebbin,

If I'm understanding correctly your data looks like this:

Muhleellbbin.xlsx
	A	B	C	D	E
1	Date	Job	Time In	Time Out	Cost Code
2	John Doe
3	01-Jan-20	A1	9:00	17:00	CC-A1
4	02-Jan-20	X2	9:00	19:24	CC-X2
5	Jane Smith
6	03-Jan-20	Z7	9:00	21:48	CC-Z7
7	04-Jan-20	W9	9:00	21:45	CC-W9
8	05-Jan-20	A8	9:00	21:42	CC-A8
9	06-Jan-20	XX	9:00	21:39	CC-XX
10	Bert Jones
11	02-Feb-20	K1	9:00	21:48	CC-K1
12
CSV

...but you want it like this:

Muhleellbbin.xlsx
	A	B	C	D	E	F
1	Name	Date	Job	Time In	Time Out	Cost Code
2	John Doe	01-Jan-20	A1	9:00	17:00	CC-A1
3	John Doe	02-Jan-20	X2	9:00	19:24	CC-X2
4	Jane Smith	03-Jan-20	Z7	9:00	21:48	CC-Z7
5	Jane Smith	04-Jan-20	W9	9:00	21:45	CC-W9
6	Jane Smith	05-Jan-20	A8	9:00	21:42	CC-A8
7	Jane Smith	06-Jan-20	XX	9:00	21:39	CC-XX
8	Bert Jones	02-Feb-20	K1	9:00	21:48	CC-K1
9
10
Reformatted

Cell Formulas
Range	Formula
A2:A10	A2	=IF(ROW()-ROW($A$1)>COUNT(CSV!$A$2:$A$9999),"",INDEX(CSV!$A$2:$A$9999,AGGREGATE(14,6,ROW(CSV!$A$2:$A$9999)-ROW(CSV!$A$1)/((ISTEXT(CSV!$A$2:$A$9999)*(ROW(CSV!$A$2:$A$9999)<=AGGREGATE(15,6,ROW(CSV!$A$2:$A$9999)-ROW(CSV!$A$1)/(ISNUMBER(CSV!$A$2:$A$9999)),ROW()-ROW($C$1))))),1)))
B2:F10	B2	=IF(ROW()-ROW($A$1)>COUNT(CSV!$A$2:$A$9999),"",INDEX(CSV!A$2:A$9999,AGGREGATE(15,6,ROW(CSV!$A$2:$A$9999)-ROW(CSV!$A$1)/(ISNUMBER(CSV!$A$2:$A$9999)),ROW()-ROW($C$1))))

 

[muhleebbin]

Thank you very much!

You absolutely understood correctly and the solution worked perfectly!

 

[Toadstool]

You're welcome!
Glad I could help.

 

[sandy666]

just for fum with Power Query (Get&Transform)

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Name = Table.FillDown(Table.AddColumn(Source, "Name", each try Text.Select([Date],{"a".."z","A".."Z"}) otherwise null),{"Name"}),
    Type = Table.TransformColumnTypes(Table.SelectRows(Name, each ([Job] <> null)),{{"Date", type date}, {"Time In", type time}, {"Time Out", type time}}),
    TSC = Table.SelectColumns(Type,{"Name", "Date", "Job", "Time In", "Time Out", "Cost Code"})
in
    TSC

Date	Job	Time In	Time Out	Cost Code		Name	Date	Job	Time In	Time Out	Cost Code
John Doe						JohnDoe	01/01/2020	A1	09:00:00	17:00:00	CC-A1
01/01/2020	A1	0.375	0.70833333	CC-A1		JohnDoe	01/02/2020	X2	09:00:00	19:24:00	CC-X2
01/02/2020	X2	0.375	0.80833333	CC-X2		JaneSmith	01/03/2020	Z7	09:00:00	21:48:00	CC-Z7
Jane Smith						JaneSmith	01/04/2020	W9	09:00:00	21:45:07	CC-W9
01/03/2020	Z7	0.375	0.90833333	CC-Z7		JaneSmith	01/05/2020	A8	09:00:00	21:42:14	CC-A8
01/04/2020	W9	0.375	0.90633333	CC-W9		JaneSmith	01/06/2020	XX	09:00:00	21:39:22	CC-XX
01/05/2020	A8	0.375	0.90433333	CC-A8		BertJones	02/02/2020	K1	09:00:00	21:48:00	CC-K1
01/06/2020	XX	0.375	0.90233333	CC-XX
Bert Jones
02/02/2020	K1	0.375	0.90833333	CC-K1