Report Generated Data to a Usable Table

D

dhubz

New Member
Joined
Sep 10, 2014
Messages
48
Office Version
  1. 2016
Platform
  1. Windows
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. [TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Name:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]John Doe[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Employee ID:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1234[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]DOB:[/TD]
[TD]1/1/1900[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Job:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Worker[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]DL #:[/TD]
[TD][/TD]
[TD][/TD]
[TD]xxxxx-xxxxx-x2398[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]DL Class:[/TD]
[TD][/TD]
[TD]G[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.
[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name:[/TD]
[TD]Employee ID:[/TD]
[TD]Job:[/TD]
[TD]DOB[/TD]
[TD]DL #:[/TD]
[TD]DL Class:[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]John Doe[/TD]
[TD]1234[/TD]
[TD]Worker[/TD]
[TD]1/1/1900[/TD]
[TD]xxxxx-xxxxx-x2398[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
i tried using INDIRECT references


Book1
ABCDEFGH
1Name:John Doe
2Employee ID:1234
3DOB:1/01/1900
4Job:Worker
5DL #:xxxxx-xxxxx-x2398
6DL Class:G
7
8Name:John Doe1
9Employee ID:12134
10DOB:11/01/1900
11Job:Worker1
12DL #:xxxxx-xxxxx-x23981
13DL Class:G1
Sheet16




Book1
ABCDEF
1Name:Employee ID:Job:DOBDL #:DL Class:
2John Doe1234Worker1/01/1900xxxxx-xxxxx-x2398G
3John Doe112134Worker111/01/1900xxxxx-xxxxx-x23981G1
Sheet17
Cell Formulas
RangeFormula
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
 
Upvote 0
you can try PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td=bgcolor:#5B9BD5]Column2[/td][td=bgcolor:#5B9BD5]Column3[/td][td=bgcolor:#5B9BD5]Column4[/td][td=bgcolor:#5B9BD5]Column5[/td][td=bgcolor:#5B9BD5]Column6[/td][td=bgcolor:#5B9BD5]Column7[/td][td=bgcolor:#5B9BD5]Column8[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Column2[/td][td=bgcolor:#70AD47]Column3[/td][td=bgcolor:#70AD47]Column4[/td][td=bgcolor:#70AD47]Column5[/td][td=bgcolor:#70AD47]Column6[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Name:[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]John Doe[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]John Doe[/td][td=bgcolor:#E2EFDA]1234[/td][td=bgcolor:#E2EFDA]
00/01/1900​
[/td][td=bgcolor:#E2EFDA]Worker[/td][td=bgcolor:#E2EFDA]xxxxx-xxxxx-x2398[/td][td=bgcolor:#E2EFDA]G[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Employee ID:[/td][td][/td][td][/td][td][/td][td]
1234​
[/td][td][/td][td][/td][td][/td][td]John Bull[/td][td]5678[/td][td]
10/01/1900​
[/td][td]Worker[/td][td]xxxxx-xxxxx-x2399[/td][td]D[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]DOB:[/td][td=bgcolor:#DDEBF7]
01/01/1900​
[/td][td][/td][td=bgcolor:#E2EFDA]Ann Knight[/td][td=bgcolor:#E2EFDA]9999[/td][td=bgcolor:#E2EFDA]
01/12/2000​
[/td][td=bgcolor:#E2EFDA]Manager[/td][td=bgcolor:#E2EFDA]aaaa-xxxxx-w9191[/td][td=bgcolor:#E2EFDA]H[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]Job:[/td][td][/td][td][/td][td][/td][td]Worker[/td][td][/td][td][/td][td]Bo Derek[/td][td]8889[/td][td]
03/04/2001​
[/td][td]Manager[/td][td]bbb-xxxxx-w9191[/td][td]G[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]DL #:[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]xxxxx-xxxxx-x2398[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]Yum Yum[/td][td=bgcolor:#E2EFDA]987632[/td][td=bgcolor:#E2EFDA]
22/12/2012​
[/td][td=bgcolor:#E2EFDA]Worker[/td][td=bgcolor:#E2EFDA]bbb-xxxxx-w0001[/td][td=bgcolor:#E2EFDA]G[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]DL Class:[/td][td][/td][td]G[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]Kim Min[/td][td]121212[/td][td]
22/12/2012​
[/td][td]Worker[/td][td]bbb-xxxxx-w0501[/td][td]G[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td=bgcolor:#E2EFDA]Bunga Bunga[/td][td=bgcolor:#E2EFDA]121212[/td][td=bgcolor:#E2EFDA]
22/12/2012​
[/td][td=bgcolor:#E2EFDA]Worker[/td][td=bgcolor:#E2EFDA]zxz-xxxxx-w0501[/td][td=bgcolor:#E2EFDA]G[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name:[/td][td][/td][td][/td][td][/td][td][/td][td]John Bull[/td][td][/td][td][/td][td][/td][td]Yongo Bongo[/td][td]222333444[/td][td]
22/12/2012​
[/td][td]Worker[/td][td]zxz-xxxxx-w0501[/td][td]G[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Employee ID:[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
5678​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]DOB:[/td][td]
11/01/1900​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Job:[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Worker[/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]DL #:[/td][td][/td][td][/td][td]xxxxx-xxxxx-x2399[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]DL Class:[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]D[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Name:[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Ann Knight[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Employee ID:[/td][td][/td][td][/td][td][/td][td]
9999​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]DOB:[/td][td=bgcolor:#DDEBF7]
01/12/2000​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]Job:[/td][td][/td][td][/td][td][/td][td]Manager[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]DL #:[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]aaaa-xxxxx-w9191[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]DL Class:[/td][td][/td][td]H[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name:[/td][td][/td][td][/td][td][/td][td][/td][td]Bo Derek[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Employee ID:[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
8889​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]DOB:[/td][td]
03/04/2001​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Job:[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Manager[/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]DL #:[/td][td][/td][td][/td][td]bbb-xxxxx-w9191[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]DL Class:[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]G[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Name:[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Yum Yum[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Employee ID:[/td][td][/td][td][/td][td][/td][td]
987632​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]DOB:[/td][td=bgcolor:#DDEBF7]
22/12/2012​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]Job:[/td][td][/td][td][/td][td][/td][td]Worker[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]DL #:[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]bbb-xxxxx-w0001[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]DL Class:[/td][td][/td][td]G[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name:[/td][td][/td][td][/td][td][/td][td][/td][td]Kim Min[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Employee ID:[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
121212​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]DOB:[/td][td]
41265​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Job:[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Worker[/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]DL #:[/td][td][/td][td][/td][td]bbb-xxxxx-w0501[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]DL Class:[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]G[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Name:[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Bunga Bunga[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]Employee ID:[/td][td][/td][td][/td][td][/td][td]
121212​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]DOB:[/td][td=bgcolor:#DDEBF7]
41265​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]Job:[/td][td][/td][td][/td][td][/td][td]Worker[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]DL #:[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]zxz-xxxxx-w0501[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]DL Class:[/td][td][/td][td]G[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Name:[/td][td][/td][td][/td][td][/td][td][/td][td]Yongo Bongo[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Employee ID:[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]
222333444​
[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]DOB:[/td][td]
41265​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Job:[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]Worker[/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td]DL #:[/td][td][/td][td][/td][td]zxz-xxxxx-w0501[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]DL Class:[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7]G[/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td=bgcolor:#DDEBF7][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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
 
Upvote 0
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]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Name:[/td][td=bgcolor:#70AD47]Employee ID:[/td][td=bgcolor:#70AD47]DOB:[/td][td=bgcolor:#70AD47]Job:[/td][td=bgcolor:#70AD47]DL #:[/td][td=bgcolor:#70AD47]DL Class:[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]John Doe[/td][td=bgcolor:#E2EFDA]
1234​
[/td][td=bgcolor:#E2EFDA]
00/01/1900​
[/td][td=bgcolor:#E2EFDA]Worker[/td][td=bgcolor:#E2EFDA]xxxxx-xxxxx-x2398[/td][td=bgcolor:#E2EFDA]G[/td][/tr]

[tr=bgcolor:#FFFFFF][td]John Bull[/td][td]
5678​
[/td][td]
10/01/1900​
[/td][td]Worker[/td][td]xxxxx-xxxxx-x2399[/td][td]D[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Ann Knight[/td][td=bgcolor:#E2EFDA]
9999​
[/td][td=bgcolor:#E2EFDA]
01/12/2000​
[/td][td=bgcolor:#E2EFDA]Manager[/td][td=bgcolor:#E2EFDA]aaaa-xxxxx-w9191[/td][td=bgcolor:#E2EFDA]H[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Bo Derek[/td][td]
8889​
[/td][td]
03/04/2001​
[/td][td]Manager[/td][td]bbb-xxxxx-w9191[/td][td]G[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Yum Yum[/td][td=bgcolor:#E2EFDA]
987632​
[/td][td=bgcolor:#E2EFDA]
22/12/2012​
[/td][td=bgcolor:#E2EFDA]Worker[/td][td=bgcolor:#E2EFDA]bbb-xxxxx-w0001[/td][td=bgcolor:#E2EFDA]G[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Kim Min[/td][td]
121212​
[/td][td]
22/12/2012​
[/td][td]Worker[/td][td]bbb-xxxxx-w0501[/td][td]G[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Bunga Bunga[/td][td=bgcolor:#E2EFDA]
121212​
[/td][td=bgcolor:#E2EFDA]
22/12/2012​
[/td][td=bgcolor:#E2EFDA]Worker[/td][td=bgcolor:#E2EFDA]zxz-xxxxx-w0501[/td][td=bgcolor:#E2EFDA]G[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Yongo Bongo[/td][td]
222333444​
[/td][td]
22/12/2012​
[/td][td]Worker[/td][td]zxz-xxxxx-w0501[/td][td]G[/td][/tr]
[/table]
 
Upvote 0
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.
 
Upvote 0
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]

[TABLE="class: head"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Name:[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Employee ID:[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]DOB:[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Job:[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]DL #:[/COLOR][/TD]
[TD="bgcolor: #70AD47"][COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]DL Class:[/COLOR][/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E2EFDA"]John Doe[/TD]
[TD="bgcolor: #E2EFDA"]
1234​
[/TD]
[TD="bgcolor: #E2EFDA"]
00/01/1900​
[/TD]
[TD="bgcolor: #E2EFDA"]Worker[/TD]
[TD="bgcolor: #E2EFDA"]xxxxx-xxxxx-x2398[/TD]
[TD="bgcolor: #E2EFDA"]G[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]John Bull[/TD]
[TD]
5678​
[/TD]
[TD]
10/01/1900​
[/TD]
[TD]Worker[/TD]
[TD]xxxxx-xxxxx-x2399[/TD]
[TD]D[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E2EFDA"]Ann Knight[/TD]
[TD="bgcolor: #E2EFDA"]
9999​
[/TD]
[TD="bgcolor: #E2EFDA"]
01/12/2000​
[/TD]
[TD="bgcolor: #E2EFDA"]Manager[/TD]
[TD="bgcolor: #E2EFDA"]aaaa-xxxxx-w9191[/TD]
[TD="bgcolor: #E2EFDA"]H[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Bo Derek[/TD]
[TD]
8889​
[/TD]
[TD]
03/04/2001​
[/TD]
[TD]Manager[/TD]
[TD]bbb-xxxxx-w9191[/TD]
[TD]G[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E2EFDA"]Yum Yum[/TD]
[TD="bgcolor: #E2EFDA"]
987632​
[/TD]
[TD="bgcolor: #E2EFDA"]
22/12/2012​
[/TD]
[TD="bgcolor: #E2EFDA"]Worker[/TD]
[TD="bgcolor: #E2EFDA"]bbb-xxxxx-w0001[/TD]
[TD="bgcolor: #E2EFDA"]G[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Kim Min[/TD]
[TD]
121212​
[/TD]
[TD]
22/12/2012​
[/TD]
[TD]Worker[/TD]
[TD]bbb-xxxxx-w0501[/TD]
[TD]G[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD="bgcolor: #E2EFDA"]Bunga Bunga[/TD]
[TD="bgcolor: #E2EFDA"]
121212​
[/TD]
[TD="bgcolor: #E2EFDA"]
22/12/2012​
[/TD]
[TD="bgcolor: #E2EFDA"]Worker[/TD]
[TD="bgcolor: #E2EFDA"]zxz-xxxxx-w0501[/TD]
[TD="bgcolor: #E2EFDA"]G[/TD]
[/TR]
[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] "]
[TD]Yongo Bongo[/TD]
[TD]
222333444​
[/TD]
[TD]
22/12/2012​
[/TD]
[TD]Worker[/TD]
[TD]zxz-xxxxx-w0501[/TD]
[TD]G[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
List Generator with index match within date range
Replies
2
Views
666
Peter_SSs
Peter_SSs
J
  • Question Question
Index Match Formula to generate a report duplicates? Rosters/Vacancies
Replies
9
Views
848
Jay88
J
K
  • Solved
Creating Single Column list with this data range
Replies
1
Views
376
keef2
K
V
  • Question Question
Returning Randomly Selected Value from Table based on Criteria
Replies
4
Views
804
Viau1337
V
B
  • Question Question
Compare lists and generate new list with what is different from the first list
2 3
Replies
21
Views
2K
sleek12
sleek12

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top