Converting employee records to a table (I think I need VBA code)

[INN]

Hello all

I have excel file like this one (Please click in the link below) , these are records for each employee (names are fake). For example Cell A11 has the employee number which 81710 and then the mployee Name then under that cell, from Cell A12 to Cell A22 these are info belong to the same emplyee. I want to create table like this (see below) for all employee in the file

The Excel file

Any help would be very much appreciated. Thank you

 

[Hans Troost]

As far as I know do the following:
1. Go to home tab
2. Select a random cell in your “list”
3. In the “styles” group click on format as table. Follow the prompts and make your choice (e.g. “my table has headers”). Format as table does more than formatting: is “creates a table” - which is in fact a “ listobject”.

I hope this helps.

 

[INN]

Hi thanks for your help. That did not help. Here is an example of 2 records in that sheet

And there are like hundard of them, there is a space between these records so if I select one cell of a record and then try to format as a table that would format that record only as a table but that would help, I need to separate Name and Employee#, B14, B20, and B22 into columns for all employees. Thanks once again

 

[Hans Troost]

My apologies Christine(?): 1st time I could not open the excel in the link you provided (error messages on my iPad). Now I understand your problem and my suggestion was indeed not helpful.
I will further think about it and possibly try something. Do you have VBA knowledge/experience? Possibly that will be a way to the solution.

 

[INN]

Thank you. I know a bit about VBA but not very good at it. I can run a VBA code. Thanks

 

[Asbestos_Jen]

I also had trouble opening the link; I think the sheet is just too big, as it kept going unresponsive. When I got in, I copied the first 5 records and got the snippet below. One thing I noticed from those records, is that they aren't all the same length and don't all have the same "B" numbers. I'm trying to work it out with formulas in Office 365 (web) and those are the stumbling blocks.

Book1
	A
1	Year End Reporting Run ID: 0000029747 PRODUCTION
2
3	Form data
4
5
6
7
8	T4
9
10
11	81710 Hylton , Andre David
12	01 895788222RP0002 ON
13	B14 58751.87
14	B16 3296.93
15	B18 975.27
16	B19 1145.91
17	B20 5392.07
18	B22 7282.41
19	B24 58751.87
20	B26 58751.87
21	B27 3296.93
22	B44 1262.83
23
24	41195 Wong , Sophy
25	01 895788222RP0002 ON
26	B14 118552.64
27	B16 3867.50
28	B18 1049.12
29	B19 1232.68
30	B20 13123.48
31	B22 22559.30
32	B24 63200.00
33	B26 68500.00
34	B27 3867.50
35	B44 1662.76
36
37	81713 Ramlochan , Clyde
38	01 895788222RP0002 ON
39	B14 143427.18
40	B16 3867.50
41	B18 1049.12
42	B19 1232.78
43	B20 14272.08
44	B22 30871.29
45	B24 63200.00
46	B26 68500.00
47	B27 3867.50
48	B44 2461.51
49	F85 29.04
50
51	41197 Slegers , Karen
52	01 895788222RP0002 ON
53	B14 6635.85
54	B16 394.83
55	B18 110.16
56	B19 129.44
57	B20 690.13
58	B24 6635.85
59	B26 6635.85
60	B27 394.83
61	B44 95.87
62
63	81714 Martyn , Sarah
64	01 895788222RP0002 ON
65	B14 129868.03
66	B16 3867.50
67	B18 1049.12
68	B19 1232.72
69	B20 14640.73
70	B22 25394.54
71	B24 63200.00
72	B26 68500.00
73	B27 3867.50
74	B44 2231.26
75	F85 29.04
Sheet1

 

[alansidman]

Pictures are of little value as they cannot be manipulated. Please reload your sample using MrE's XL2BB add in

 

[alansidman]

using a combination of Power Query and then exporting the cleaned up file to a Pivot Table, your file is attached at the following location.

Christine.xlsx | Powered by Box

app.box.com

Here is the Mcode for anyone interested. Because the file is very large, I will only keep it up for about a week. After
that, you can create your own pivot table from the power query arranged data.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> null)),
    #"Removed Top Rows" = Table.Skip(#"Filtered Rows",3),
    #"Added Custom" = Table.AddColumn(#"Removed Top Rows", "Custom", each if Text.Contains([Column1]," ON") then 1 else null),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Custom] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Custom"}),
    #"Cleaned Text" = Table.TransformColumns(#"Removed Columns",{{"Column1", Text.Clean, type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Cleaned Text",{{"Column1", Text.Trim, type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Trimmed Text", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
    #"Filtered Rows2" = Table.SelectRows(#"Split Column by Delimiter", each true),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows2", "TEST", each try Type.Is(Text.InferNumberType(Text.From([Column1.1])), type number) otherwise false),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Employee", each if [TEST]= true then [Column1.1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom2",{"Employee"}),
    #"Added Custom3" = Table.AddColumn(#"Filled Down", "Name", each if [TEST]=true then [Column1.2] else null),
    #"Filled Down1" = Table.FillDown(#"Added Custom3",{"Name"}),
    #"Filtered Rows3" = Table.SelectRows(#"Filled Down1", each ([TEST] = false)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows3",{"TEST"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Column1.2", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1.1", "Category"}, {"Column1.2", "Value"}})
in
    #"Renamed Columns"

 

[INN]

using a combination of Power Query and then exporting the cleaned up file to a Pivot Table, your file is attached at the following location.

Christine.xlsx | Powered by Box

app.box.com

Here is the Mcode for anyone interested. Because the file is very large, I will only keep it up for about a week. After
that, you can create your own pivot table from the power query arranged data.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> null)),
    #"Removed Top Rows" = Table.Skip(#"Filtered Rows",3),
    #"Added Custom" = Table.AddColumn(#"Removed Top Rows", "Custom", each if Text.Contains([Column1]," ON") then 1 else null),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Custom] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Custom"}),
    #"Cleaned Text" = Table.TransformColumns(#"Removed Columns",{{"Column1", Text.Clean, type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Cleaned Text",{{"Column1", Text.Trim, type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Trimmed Text", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
    #"Filtered Rows2" = Table.SelectRows(#"Split Column by Delimiter", each true),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows2", "TEST", each try Type.Is(Text.InferNumberType(Text.From([Column1.1])), type number) otherwise false),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Employee", each if [TEST]= true then [Column1.1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom2",{"Employee"}),
    #"Added Custom3" = Table.AddColumn(#"Filled Down", "Name", each if [TEST]=true then [Column1.2] else null),
    #"Filled Down1" = Table.FillDown(#"Added Custom3",{"Name"}),
    #"Filtered Rows3" = Table.SelectRows(#"Filled Down1", each ([TEST] = false)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows3",{"TEST"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Column1.2", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1.1", "Category"}, {"Column1.2", "Value"}})
in
    #"Renamed Columns"

Thank you so much for all your help. I appreciate all of you. Thanks again

 

[Hans Troost]

using a combination of Power Query and then exporting the cleaned up file to a Pivot Table, your file is attached at the following location.

Christine.xlsx | Powered by Box

app.box.com

Here is the Mcode for anyone interested. Because the file is very large, I will only keep it up for about a week. After
that, you can create your own pivot table from the power query arranged data.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> null)),
    #"Removed Top Rows" = Table.Skip(#"Filtered Rows",3),
    #"Added Custom" = Table.AddColumn(#"Removed Top Rows", "Custom", each if Text.Contains([Column1]," ON") then 1 else null),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom", each ([Custom] = null)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows1",{"Custom"}),
    #"Cleaned Text" = Table.TransformColumns(#"Removed Columns",{{"Column1", Text.Clean, type text}}),
    #"Trimmed Text" = Table.TransformColumns(#"Cleaned Text",{{"Column1", Text.Trim, type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Trimmed Text", "Column1", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, false), {"Column1.1", "Column1.2"}),
    #"Filtered Rows2" = Table.SelectRows(#"Split Column by Delimiter", each true),
    #"Added Custom1" = Table.AddColumn(#"Filtered Rows2", "TEST", each try Type.Is(Text.InferNumberType(Text.From([Column1.1])), type number) otherwise false),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Employee", each if [TEST]= true then [Column1.1] else null),
    #"Filled Down" = Table.FillDown(#"Added Custom2",{"Employee"}),
    #"Added Custom3" = Table.AddColumn(#"Filled Down", "Name", each if [TEST]=true then [Column1.2] else null),
    #"Filled Down1" = Table.FillDown(#"Added Custom3",{"Name"}),
    #"Filtered Rows3" = Table.SelectRows(#"Filled Down1", each ([TEST] = false)),
    #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows3",{"TEST"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns1",{{"Column1.2", type number}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1.1", "Category"}, {"Column1.2", "Value"}})
in
    #"Renamed Columns"

I was thinking about a VBA-program, but this is a far more elegant solution!!