With Power Query
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Company ID", "Company Name", "Address", "Zip", " City", "State", "Company Email"}, "Attribute", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "First Name", each if Text.Contains([Attribute],"First") then [Value] else null),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Last Name", each if Text.Contains([Attribute], "Last") then [Value] else null),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "Title", each if Text.Contains([Attribute], "Title") then [Value] else null),
#"Filled Up" = Table.FillUp(#"Added Custom2",{"First Name", "Last Name", "Title"}),
#"Filtered Rows" = Table.SelectRows(#"Filled Up", each Text.Contains([Attribute], "ID")),
#"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Attribute"})
in
#"Removed Columns"
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.
Excel 2016 (Windows) 64 bit
|
A |
B |
C |
D |
E |
F |
G |
H |
I |
J |
K |
1 |
Company ID |
Company Name |
Address |
Zip |
City |
State |
Company Email |
Value |
First Name |
Last Name |
Title |
2 |
1 |
Acme |
12345 E Main |
84009 |
Ceder City |
OH |
[email protected] |
1253648 |
Bob |
Willis |
CEO / President |
3 |
1 |
Acme |
12345 E Main |
84009 |
Ceder City |
OH |
[email protected] |
452365 |
Travis |
Jones |
VP Sales |
4 |
1 |
Acme |
12345 E Main |
84009 |
Ceder City |
OH |
[email protected] |
5623652 |
Troy |
Yong |
Marketing Manager |
5 |
2 |
Baloon |
2418 E Wave |
95006 |
Melbourne |
WI |
[email protected] |
3654123 |
Jack |
Harrison |
President |
6 |
2 |
Baloon |
2418 E Wave |
95006 |
Melbourne |
WI |
[email protected] |
452136 |
Melanie |
Holmes |
Marketing |
7 |
2 |
Baloon |
2418 E Wave |
95006 |
Melbourne |
WI |
[email protected] |
5896523 |
Melissa |
Beckenberg |
Operations Manager |
8 |
3 |
Alta Vista |
34 E Code |
75632 |
S. Mountain |
MI |
[email protected] |
1253640 |
Mark |
Jones |
V.P. Sales |
9 |
3 |
Alta Vista |
34 E Code |
75632 |
S. Mountain |
MI |
[email protected] |
125632 |
Terry |
Rodgers |
Controller |
10 |
3 |
Alta Vista |
34 E Code |
75632 |
S. Mountain |
MI |
[email protected] |
7652365 |
Joni |
Warner |
Sales Director |
11 |
4 |
Zantas |
348 W Maven |
68523 |
Los Alamos |
AZ |
[email protected] |
1258300 |
Mathew |
Ash |
Sales Director |
12 |
4 |
Zantas |
348 W Maven |
68523 |
Los Alamos |
AZ |
[email protected] |
125836 |
Beverly |
Rodrigues |
Sales Director |
13 |
4 |
Zantas |
348 W Maven |
68523 |
Los Alamos |
AZ |
[email protected] |
8523654 |
Carrie |
Smith |
Operations Manager |
14 |
5 |
TechFarm |
2349 NE 49th St. |
43520 |
Madison |
AK |
[email protected] |
1254125 |
John |
Mellencamp |
CEO / President |
15 |
5 |
TechFarm |
2349 NE 49th St. |
43520 |
Madison |
AK |
[email protected] |
786250 |
Bertha |
Maher |
VP Marketing |
16 |
5 |
TechFarm |
2349 NE 49th St. |
43520 |
Madison |
AK |
[email protected] |
1253642 |
Shannon |
Webb |
Marketing Manager |
Bookmarks