Formatting data to use in pivot table

O

omard.

New Member
Joined
Mar 15, 2006
Messages
16
Hello All,

I have this issue have been trying to solve for a while now and cant seem to find any solution. I'm hoping someone here can help me.

Here is the issues. I have data that comes to me in this format and it have a few more columns and rows.

Dept Users
Finance I cn=User0,ou=users,o=vault|cn=User1,ou=users,o=vault
Finance I Discretionary
Finance II cn=User2,ou=users,o=vault
Finance II Discretionary
Finance III cn=User3,ou=users,o=vault
Finance III Discretionary
Finance IV cn=User4,ou=users,o=vault
Finance IV Discretionary
Finance V cn=User5,ou=users,o=vault|cn=User6,ou=users,o=vault|cn=User7,ou=users,o=vault|cn=User8,ou=users,o=vault

I first do a text to column to get all the users out of one cell and that is where i get stumped.

What i need it to look is like this.

Dept Users
Finance I cn=User0,ou=users,o=vault
Finance I cn=User1,ou=users,o=vault
Finance I Discretionary
Finance II cn=User2,ou=users,o=vault
Finance II Discretionary
Finance III cn=User3,ou=users,o=vault
Finance III Discretionary
Finance IV cn=User4,ou=users,o=vault
Finance IV Discretionary
Finance V cn=User5,ou=users,o=vault
Finance V cn=User6,ou=users,o=vault
Finance V cn=User7,ou=users,o=vault
Finance V cn=User8,ou=users,o=vault

The user information gets place vertically instead of horizontally and all the information in the cells leading up to the users gets duplicated down.

Any help would be so appreciated.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
A little more info might help here.

Can you say which database exported the file this way? Also what is the file extension of the file producing this format -- .csv, etc ?
 
Upvote 0
The information comes out of an Identity Management system with very poor reporting. A query is run and that output is copy and pasted in a .csv file. I really hope this helps
 
Upvote 0
with PowerQuery (Get&Transform)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Dept Users[/td][td][/td][td=bgcolor:#70AD47]Dept[/td][td=bgcolor:#70AD47]Users[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Finance I cn=User0,ou=users,o=vault|cn=User1,ou=users,o=vault[/td][td][/td][td=bgcolor:#E2EFDA]Finance I[/td][td=bgcolor:#E2EFDA]cn=User0,ou=users,o=vault[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Finance I Discretionary[/td][td][/td][td]Finance I[/td][td]cn=User1,ou=users,o=vault[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Finance II cn=User2,ou=users,o=vault[/td][td][/td][td=bgcolor:#E2EFDA]Finance I[/td][td=bgcolor:#E2EFDA]Discretionary[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Finance II Discretionary[/td][td][/td][td]Finance II[/td][td]cn=User2,ou=users,o=vault[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Finance III cn=User3,ou=users,o=vault[/td][td][/td][td=bgcolor:#E2EFDA]Finance II[/td][td=bgcolor:#E2EFDA]Discretionary[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Finance III Discretionary[/td][td][/td][td]Finance III[/td][td]cn=User3,ou=users,o=vault[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Finance IV cn=User4,ou=users,o=vault[/td][td][/td][td=bgcolor:#E2EFDA]Finance III[/td][td=bgcolor:#E2EFDA]Discretionary[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Finance IV Discretionary[/td][td][/td][td]Finance IV[/td][td]cn=User4,ou=users,o=vault[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Finance V cn=User5,ou=users,o=vault|cn=User6,ou=users,o=vault|cn=User7,ou=users,o=vault|cn=User8,ou=users,o=vault[/td][td][/td][td=bgcolor:#E2EFDA]Finance IV[/td][td=bgcolor:#E2EFDA]Discretionary[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]Finance V[/td][td]cn=User5,ou=users,o=vault[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td=bgcolor:#E2EFDA]Finance V[/td][td=bgcolor:#E2EFDA]cn=User6,ou=users,o=vault[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td]Finance V[/td][td]cn=User7,ou=users,o=vault[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td=bgcolor:#E2EFDA]Finance V[/td][td=bgcolor:#E2EFDA]cn=User8,ou=users,o=vault[/td][/tr]
[/table]
Code: 
[SIZE=1]
let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Dept Users", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Dept Users.1", "Dept Users.2", "Dept Users.3"}),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Split Column by Delimiter", {{"Dept Users.3", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Dept Users.3"),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Delimiter1",{"Dept Users.1", "Dept Users.2"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Dept"),
    #"Renamed Columns" = Table.RenameColumns(#"Merged Columns",{{"Dept Users.3", "Users"}})
in
    #"Renamed Columns"[/SIZE]
 
Last edited:
Upvote 0
or if there are two columns it will be much simpler:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Dept[/td][td=bgcolor:#5B9BD5]Users[/td][td][/td][td=bgcolor:#70AD47]Dept[/td][td=bgcolor:#70AD47]Users[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Finance I[/td][td=bgcolor:#DDEBF7]cn=User0,ou=users,o=vault|cn=User1,ou=users,o=vault[/td][td][/td][td=bgcolor:#E2EFDA]Finance I[/td][td=bgcolor:#E2EFDA]cn=User0,ou=users,o=vault[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Finance I[/td][td]Discretionary[/td][td][/td][td]Finance I[/td][td]cn=User1,ou=users,o=vault[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Finance II[/td][td=bgcolor:#DDEBF7]cn=User2,ou=users,o=vault[/td][td][/td][td=bgcolor:#E2EFDA]Finance I[/td][td=bgcolor:#E2EFDA]Discretionary[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Finance II[/td][td]Discretionary[/td][td][/td][td]Finance II[/td][td]cn=User2,ou=users,o=vault[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Finance III[/td][td=bgcolor:#DDEBF7]cn=User3,ou=users,o=vault[/td][td][/td][td=bgcolor:#E2EFDA]Finance II[/td][td=bgcolor:#E2EFDA]Discretionary[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Finance III[/td][td]Discretionary[/td][td][/td][td]Finance III[/td][td]cn=User3,ou=users,o=vault[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Finance IV[/td][td=bgcolor:#DDEBF7]cn=User4,ou=users,o=vault[/td][td][/td][td=bgcolor:#E2EFDA]Finance III[/td][td=bgcolor:#E2EFDA]Discretionary[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Finance IV[/td][td]Discretionary[/td][td][/td][td]Finance IV[/td][td]cn=User4,ou=users,o=vault[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Finance V[/td][td=bgcolor:#DDEBF7]cn=User5,ou=users,o=vault|cn=User6,ou=users,o=vault|cn=User7,ou=users,o=vault|cn=User8,ou=users,o=vault[/td][td][/td][td=bgcolor:#E2EFDA]Finance IV[/td][td=bgcolor:#E2EFDA]Discretionary[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]Finance V[/td][td]cn=User5,ou=users,o=vault[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Finance V[/td][td=bgcolor:#E2EFDA]cn=User6,ou=users,o=vault[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]Finance V[/td][td]cn=User7,ou=users,o=vault[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]Finance V[/td][td=bgcolor:#E2EFDA]cn=User8,ou=users,o=vault[/td][/tr]
[/table]

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Users", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Users")
in
    #"Split Column by Delimiter"[/SIZE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

sparky2205
  • Solved
Lookup to populate names with groups
Replies
5
Views
424
Skovgaard
S
J
  • Solved
Sorting a Lot of data into multiple sheets (up to 200k lines)
Replies
4
Views
343
JMWh1t3
J
C
  • Solved
Get sumproduct of a range defined dynamically
Replies
3
Views
262
Fluff
Fluff
S
  • Question Question
Summarizing Data from Table
Replies
0
Views
373
SpreadsheetSwaff
S
S
  • Question Question
Getting First In and Last out from a temp data
Replies
1
Views
458
MARK858
MARK858

Forum statistics

Threads
1,224,820
Messages
6,181,159
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