Report Generated Data to a Usable Table

dhubz · Nov 14, 2018

Hi Everyone,

I have a report generated from another program. It's giving me all of the information I need, it just not in a usable table form. I will try to explain. This is what a single employee record looks like.

	A	B	C	D	E	F	G	H
1	Name:					John Doe
2		Employee ID:				1234
3							DOB:	1/1/1900
4			Job:				Worker
5		DL #:			xxxxx-xxxxx-x2398
6		DL Class:		G
7

Each new record is the same template, there would be another employee starting at row 8, with the same cells and references within each employee record. Example the actual name is in F1, the next name +7rows in F8, the next +7rows in F15. Below is what I am trying to accomplish.


1	Name:	Employee ID:	Job:	DOB	DL #:	DL Class:
2	John Doe	1234	Worker	1/1/1900	xxxxx-xxxxx-x2398	G
3
4

I've been able to extract a list by using the following formula, but it is slow and causes the excel to crash if I drag down to far. On Sheet2! I used the following to extract the list of names, but it will only return unique values, which is ok for the names, I will have a master list. I am sure if I use this for other columns and there is blank it will skip it and cause my data to be wrong. I am guessing a macro would be best, but not sure where to start.

On Sheet2! cell A2 is "Name:", in A3 is the following code with (CTRL+SHFT+ENTER) for the array.

Code:

=INDEX(Sheet1!F:F,MATCH(0,IF($A$2=Sheet1!A:A,COUNTIF($A$2:$A2,Sheet1!F:F),""),0))

Thanks

fhqwgads · Nov 14, 2018

i tried using INDIRECT references

Book1

A

B

C

D

E

F

G

H

1

Name:

John Doe

2

Employee ID:

1234

3

DOB:

1/01/1900

4

Job:

Worker

5

DL #:

xxxxx-xxxxx-x2398

6

DL Class:

G

7

8

Name:

John Doe1

9

Employee ID:

12134

10

DOB:

11/01/1900

11

Job:

Worker1

12

DL #:

xxxxx-xxxxx-x23981

13

DL Class:

G1

Sheet16

Book1

A

B

C

D

E

F

1

Name:

Employee ID:

Job:

DOB

DL #:

DL Class:

2

3

John Doe1

12134

Worker1

11/01/1900

xxxxx-xxxxx-x23981

G1

Sheet17

Cell Formulas
Range	Formula
A2	=INDIRECT("Sheet16!"&"R"&(ROW()-2)*7+1&"C"&6,FALSE)
B2	=INDIRECT("Sheet16!"&"R"&(ROW()-2)*7+2&"C"&6,FALSE)
C2	=INDIRECT("Sheet16!"&"R"&(ROW()-2)*7+4&"C"&7,FALSE)
D2	=INDIRECT("Sheet16!"&"R"&(ROW()-2)*7+3&"C"&8,FALSE)
E2	=INDIRECT("Sheet16!"&"R"&(ROW()-2)*7+5&"C"&5,FALSE)
F2	=INDIRECT("Sheet16!"&"R"&(ROW()-2)*7+6&"C"&4,FALSE)

hope that works for you

sandy666 · Nov 14, 2018

you can try PowerQuery

Column1

Column2

Column3

Column4

Column5

Column6

Column7

Column8

Column1

Column2

Column3

Column4

Column5

Column6

Name:

John Doe

1234

00/01/1900

Worker

xxxxx-xxxxx-x2398

G

Employee ID:

1234

John Bull

5678

10/01/1900

Worker

xxxxx-xxxxx-x2399

D

DOB:

01/01/1900

Ann Knight

9999

01/12/2000

Manager

aaaa-xxxxx-w9191

H

Job:

Worker

Bo Derek

8889

03/04/2001

Manager

bbb-xxxxx-w9191

G

DL #:

xxxxx-xxxxx-x2398

Yum Yum

987632

22/12/2012

Worker

bbb-xxxxx-w0001

G

DL Class:

G

Kim Min

121212

22/12/2012

Worker

bbb-xxxxx-w0501

G

Bunga Bunga

121212

22/12/2012

Worker

zxz-xxxxx-w0501

G

Name:

John Bull

Yongo Bongo

222333444

22/12/2012

Worker

zxz-xxxxx-w0501

G

Employee ID:

5678

DOB:

11/01/1900

Job:

Worker

DL #:

xxxxx-xxxxx-x2399

DL Class:

D

Name:

Ann Knight

Employee ID:

9999

DOB:

01/12/2000

Job:

Manager

DL #:

aaaa-xxxxx-w9191

DL Class:

H

Name:

Bo Derek

Employee ID:

8889

DOB:

03/04/2001

Job:

Manager

DL #:

bbb-xxxxx-w9191

DL Class:

G

Name:

Yum Yum

Employee ID:

987632

DOB:

22/12/2012

Job:

Worker

DL #:

bbb-xxxxx-w0001

DL Class:

G

Name:

Kim Min

Employee ID:

121212

DOB:

41265

Job:

Worker

DL #:

bbb-xxxxx-w0501

DL Class:

G

Name:

Bunga Bunga

Employee ID:

121212

DOB:

41265

Job:

Worker

DL #:

zxz-xxxxx-w0501

DL Class:

G

Name:

Yongo Bongo

Employee ID:

222333444

DOB:

41265

Job:

Worker

DL #:

zxz-xxxxx-w0501

DL Class:

G

Code:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column8", type date}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1] <> null then [Column1] else if [Column2] <> null then [Column2] else if [Column3] <> null then [Column3] else if [Column7] <> null then [Column7] else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [Column4] <> null then [Column4] else if [Column5] <> null then [Column5] else if [Column6] <> null then [Column6] else if [Column8] <> null then [Column8] else if [Column7] <> null then [Column7] else null),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column1",{"Custom", "Custom.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Custom] <> null)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Custom"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.Column([Count],"Custom.1")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Extracted Values",{"Custom.1"}),
DelimiterCount = Table.AddColumn(#"Removed Other Columns1", "CountDelimiters", each List.Count(Text.Split([Custom.1],","))-1),
MaxCount = List.Max(DelimiterCount[CountDelimiters])+1,
SplitResultDV = Table.SplitColumn(#"Removed Other Columns1", "Custom.1" , Splitter.SplitTextByDelimiter(","), MaxCount ),
    #"Transposed Table" = Table.Transpose(SplitResultDV),
    #"Changed Type1" = Table.TransformColumnTypes(#"Transposed Table",{{"Column3", type date}})

in
    #"Changed Type1"[/SIZE]

Result table (green) can be loaded wherever you want
After add new record use Ctrl+Alt+F5

sandy666 · Nov 14, 2018

I forgot about headers

Code:

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column8", type date}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1] <> null then [Column1] else if [Column2] <> null then [Column2] else if [Column3] <> null then [Column3] else if [Column7] <> null then [Column7] else null),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [Column4] <> null then [Column4] else if [Column5] <> null then [Column5] else if [Column6] <> null then [Column6] else if [Column8] <> null then [Column8] else if [Column7] <> null then [Column7] else null),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column1",{"Custom", "Custom.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Custom] <> null)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Custom"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.Column([Count],"Custom.1")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    DelimiterCount = Table.AddColumn(#"Extracted Values", "CountDelimiters", each List.Count(Text.Split([Custom.1],","))-1),
MaxCount = List.Max(DelimiterCount[CountDelimiters])+1,
SplitResultDV = Table.SplitColumn(#"Extracted Values", "Custom.1" , Splitter.SplitTextByDelimiter(","), MaxCount ),
    #"Transposed Table" = Table.Transpose(SplitResultDV),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name:", type text}, {"Employee ID:", Int64.Type}, {"DOB:", type date}, {"Job:", type text}, {"DL #:", type text}, {"DL Class:", type text}})
in
    #"Changed Type1"[/SIZE]

Name:	Employee ID:	DOB:	Job:	DL #:	DL Class:
John Doe	1234	00/01/1900	Worker	xxxxx-xxxxx-x2398	G
John Bull	5678	10/01/1900	Worker	xxxxx-xxxxx-x2399	D
Ann Knight	9999	01/12/2000	Manager	aaaa-xxxxx-w9191	H
Bo Derek	8889	03/04/2001	Manager	bbb-xxxxx-w9191	G
Yum Yum	987632	22/12/2012	Worker	bbb-xxxxx-w0001	G
Kim Min	121212	22/12/2012	Worker	bbb-xxxxx-w0501	G
Bunga Bunga	121212	22/12/2012	Worker	zxz-xxxxx-w0501	G
Yongo Bongo	222333444	22/12/2012	Worker	zxz-xxxxx-w0501	G

dhubz · Nov 15, 2018

This works great! I'm not going to lie, I really don't understand it, but I can make it work. Thanks, I will definitely look more into the indirect function.

dhubz · Nov 15, 2018

sandy666 said:
I forgot about headers

Code:

[SIZE=1]let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column8", type date}}), #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Custom", each if [Column1] <> null then [Column1] else if [Column2] <> null then [Column2] else if [Column3] <> null then [Column3] else if [Column7] <> null then [Column7] else null), #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [Column4] <> null then [Column4] else if [Column5] <> null then [Column5] else if [Column6] <> null then [Column6] else if [Column8] <> null then [Column8] else if [Column7] <> null then [Column7] else null), #"Removed Other Columns" = Table.SelectColumns(#"Added Conditional Column1",{"Custom", "Custom.1"}), #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each ([Custom] <> null)), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Custom"}, {{"Count", each _, type table}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.Column([Count],"Custom.1")), #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Count"}), #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"Custom.1", each Text.Combine(List.Transform(_, Text.From), ","), type text}), DelimiterCount = Table.AddColumn(#"Extracted Values", "CountDelimiters", each List.Count(Text.Split([Custom.1],","))-1), MaxCount = List.Max(DelimiterCount[CountDelimiters])+1, SplitResultDV = Table.SplitColumn(#"Extracted Values", "Custom.1" , Splitter.SplitTextByDelimiter(","), MaxCount ), #"Transposed Table" = Table.Transpose(SplitResultDV), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name:", type text}, {"Employee ID:", Int64.Type}, {"DOB:", type date}, {"Job:", type text}, {"DL #:", type text}, {"DL Class:", type text}}) in #"Changed Type1"[/SIZE]

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Name:[/COLOR] [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Employee ID:[/COLOR] [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]DOB:[/COLOR] [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Job:[/COLOR] [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]DL #:[/COLOR] [COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]DL Class:[/COLOR]
John Doe
1234
00/01/1900
Worker xxxxx-xxxxx-x2398 G
John Bull
5678
10/01/1900
Worker xxxxx-xxxxx-x2399 D
Ann Knight
9999
01/12/2000
Manager aaaa-xxxxx-w9191 H
Bo Derek
8889
03/04/2001
Manager bbb-xxxxx-w9191 G
Yum Yum
987632
22/12/2012
Worker bbb-xxxxx-w0001 G
Kim Min
121212
22/12/2012
Worker bbb-xxxxx-w0501 G
Bunga Bunga
121212
22/12/2012
Worker zxz-xxxxx-w0501 G
Yongo Bongo
222333444
22/12/2012
Worker zxz-xxxxx-w0501 G

<tbody>
</tbody>

Thanks for this, I don't have the power query add-in, but I am definitely going to install. I watch a couple videos on, looks great. Thanks again.

sandy666 · Nov 15, 2018

You are welcome

Have a nice day

Report Generated Data to a Usable Table

dhubz

New Member

Excel Facts

fhqwgads

Board Regular

sandy666

Banned - Rules violations

sandy666

Banned - Rules violations

dhubz

New Member

dhubz

New Member

sandy666

Banned - Rules violations

Similar threads

Forum statistics

Share this page

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Name:[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Employee ID:[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]DOB:[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Job:[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]DL #:[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]DL Class:[/COLOR]
John Doe	1234	00/01/1900	Worker	xxxxx-xxxxx-x2398	G
John Bull	5678	10/01/1900	Worker	xxxxx-xxxxx-x2399	D
Ann Knight	9999	01/12/2000	Manager	aaaa-xxxxx-w9191	H
Bo Derek	8889	03/04/2001	Manager	bbb-xxxxx-w9191	G
Yum Yum	987632	22/12/2012	Worker	bbb-xxxxx-w0001	G
Kim Min	121212	22/12/2012	Worker	bbb-xxxxx-w0501	G
Bunga Bunga	121212	22/12/2012	Worker	zxz-xxxxx-w0501	G
Yongo Bongo	222333444	22/12/2012	Worker	zxz-xxxxx-w0501	G

Report Generated Data to a Usable Table

dhubz

New Member

Excel Facts

fhqwgads

Board Regular

sandy666

Banned - Rules violations

sandy666

Banned - Rules violations

dhubz

New Member

dhubz

New Member

sandy666

Banned - Rules violations

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock