Manipulating a Flat File (Moving Columns under other Columns)

GiveMeYourBeans

New Member
Joined
Feb 10, 2021
Messages
6
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
I want to thank everyone in advance. This has been driving me crazy and I'm hoping that there is a solution.

Below is a very small sample of a flat file I'm hoping to manipulate. As you can see there are 2 users, creating 2 rows. Each "user" can have multiple locations and each location corresponds to a billing address. For example Location 1 Name corresponds with Remit 1 Name.

Last NameFirst NameLocation 1 NameLocation 1 Address 1Remit 1 NameRemit 1 Address 1Location 2 NameLocation 2 Address 1Remit 2 NameRemit 2 Address 1
DoeJaneApple Tree Road123 RoadBilling Group435 Greentree Road
SmithJohnApple Tree Road123 RoadBilling Group435 Greentree RoadHollywood Sports325 Pennsylvania AveJoes Billing435 Greentree Road

My question being is it possible to manipulate the flat file so that "Location Name 2" will appear below "Location Name 1" and so on. To make matters more complicated is it possible to have "Remit 2 Name" below Remit 1 Name"?

I'm specifically talking about the data in the columns, not the column headers themselves.

My hope is that it can be manipulated to look like this:

Last NameFirst NameLocation 1 NameLocation 1 Address 1Remit 1 NameRemit 1 Address 1
DoeJaneApple Tree Road123 RoadBilling Group435 Greentree Road
SmithJohnApple Tree Road123 RoadBilling Group435 Greentree Road
SmithJohnHollywood Sports325 Pennsylvania AveJoes Billing435 Greentree Road

The issue is that for this project a flat file is required because of some of the other data that is excluded from this example. This becomes a very messy file for the simple fact that a spreadsheet could have 1000 "Users" with them having anywhere from 1-20 locations & remit combinations. Any help would be GREATLY appreciated!
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,097
Office Version
  1. 365
Platform
  1. Windows
A way using Power Query.

Book1
ABCDEFGHIJ
1Last NameFirst NameLocation 1 NameLocation 1 Address 1Remit 1 NameRemit 1 Address 1Location 2 NameLocation 2 Address 1Remit 2 NameRemit 2 Address 1
2DoeJaneApple Tree Road123 RoadBilling Group435 Greentree Road
3SmithJohnApple Tree Road123 RoadBilling Group435 Greentree RoadHollywood Sports325 Pennsylvania AveJoes Billing435 Greentree Road
4
5Last NameFirst NameLocation NameLocation Address Remit NameRemit Address
6DoeJaneApple Tree Road123 RoadBilling Group435 Greentree Road
7SmithJohnApple Tree Road123 RoadBilling Group435 Greentree Road
8SmithJohnHollywood Sports325 Pennsylvania AveJoes Billing435 Greentree Road
Sheet4


Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    List = Table.AddColumn(Source, "Custom", each List.Split(List.RemoveItems(List.Skip(Record.FieldValues(_),2),{null}),4)),
    RC = Table.RemoveColumns(List,{"Location 1 Name", "Location 1 Address 1", "Remit 1 Name", "Remit 1 Address 1", "Location 2 Name", "Location 2 Address 1", "Remit 2 Name", "Remit 2 Address 1"}),
    Expand = Table.ExpandListColumn(RC, "Custom"),
    Extract = Table.TransformColumns(Expand, {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    Split = Table.SplitColumn(Extract, "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4"}),
    CH = List.Transform(List.Range(Table.ColumnNames(Source),2,4), each Text.Replace(Text.Select(_,{"A".."z"," "}),"  "," ")),
    HH = Table.RenameColumns(Split,List.Zip({List.Range(Table.ColumnNames(Split),2,4),CH}))
in
    HH
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Manipulating a Flat File (Moving Columns under other Columns)
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 

GiveMeYourBeans

New Member
Joined
Feb 10, 2021
Messages
6
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
A way using Power Query.

Book1
ABCDEFGHIJ
1Last NameFirst NameLocation 1 NameLocation 1 Address 1Remit 1 NameRemit 1 Address 1Location 2 NameLocation 2 Address 1Remit 2 NameRemit 2 Address 1
2DoeJaneApple Tree Road123 RoadBilling Group435 Greentree Road
3SmithJohnApple Tree Road123 RoadBilling Group435 Greentree RoadHollywood Sports325 Pennsylvania AveJoes Billing435 Greentree Road
4
5Last NameFirst NameLocation NameLocation Address Remit NameRemit Address
6DoeJaneApple Tree Road123 RoadBilling Group435 Greentree Road
7SmithJohnApple Tree Road123 RoadBilling Group435 Greentree Road
8SmithJohnHollywood Sports325 Pennsylvania AveJoes Billing435 Greentree Road
Sheet4


Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    List = Table.AddColumn(Source, "Custom", each List.Split(List.RemoveItems(List.Skip(Record.FieldValues(_),2),{null}),4)),
    RC = Table.RemoveColumns(List,{"Location 1 Name", "Location 1 Address 1", "Remit 1 Name", "Remit 1 Address 1", "Location 2 Name", "Location 2 Address 1", "Remit 2 Name", "Remit 2 Address 1"}),
    Expand = Table.ExpandListColumn(RC, "Custom"),
    Extract = Table.TransformColumns(Expand, {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    Split = Table.SplitColumn(Extract, "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4"}),
    CH = List.Transform(List.Range(Table.ColumnNames(Source),2,4), each Text.Replace(Text.Select(_,{"A".."z"," "}),"  "," ")),
    HH = Table.RenameColumns(Split,List.Zip({List.Range(Table.ColumnNames(Split),2,4),CH}))
in
    HH

Wow! This is exactly what I'm looking for. Is it possible for any additional explanation? I'm trying to follow how you did that.
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,097
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

This code is a bit cleaner and probably easier to follow. This one doesn't have a bunch of nested formulas on one line like the last one.

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Last Name", "First Name"}, "Attribute", "Value"),
    RemoveNum = Table.TransformColumns(Unpivot,{{"Attribute", each Text.Replace(Text.Select(_,{"A".."z"," "}),"  "," ")}}),
    Index = Table.AddIndexColumn(RemoveNum, "Index", 0, 1, Int64.Type),
    IntDiv = Table.AddColumn(Index, "Integer-Division", each Number.IntegerDivide([Index], 4), Int64.Type),
    RemoveIndex = Table.RemoveColumns(IntDiv,{"Index"}),
    Pivot = Table.Pivot(RemoveIndex, List.Distinct(RemoveIndex[Attribute]), "Attribute", "Value"),
    RemoveInt = Table.RemoveColumns(Pivot,{"Integer-Division"})
in
    RemoveInt
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,097
Office Version
  1. 365
Platform
  1. Windows
As for an explanation of the first PQ code.

Power Query:
List = Table.AddColumn(Source, "Custom", each List.Split(List.RemoveItems(List.Skip(Record.FieldValues(_),2),{null}),4))
Record.FieldValues makes a list of all the values on each row. List.Skip skips the last name and first name on that list. I am removing the nulls and then split the lists in increments of 4 since we have 4 columns for each set of address data.

Power Query:
    RC = Table.RemoveColumns(List,{"Location 1 Name", "Location 1 Address 1", "Remit 1 Name", "Remit 1 Address 1", "Location 2 Name", "Location 2 Address 1", "Remit 2 Name", "Remit 2 Address 1"}),
    Expand = Table.ExpandListColumn(RC, "Custom"),

These ones are kind of self explanatory and are done through the GUI, remove columns and expand the column with the lists.

Power Query:
Extract = Table.TransformColumns(Expand, {"Custom", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),

Here I am transforming the column with the lists from a list to a text string with Text.Combine

Power Query:
Split = Table.SplitColumn(Extract, "Custom", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4"}),

This splits the combined text from the previous step into columns.


Power Query:
CH = List.Transform(List.Range(Table.ColumnNames(Source),2,4), each Text.Replace(Text.Select(_,{"A".."z"," "}),"  "," ")),

I get the original column names from the first step, the original table, and select the columns with Location, Address, Remit Name, and Remit Address. Then I select just the text and spaces, get rid of the numbers, with Text.Select, and also replace the double spaces with single spaces.

Power Query:
HH = Table.RenameColumns(Split,List.Zip({List.Range(Table.ColumnNames(Split),2,4),CH}))

I do the same thing here with getting columns names, but this time working with the SPLIT step. Since when we extracted the list to columns PQ gave the columns names like column1, column2, etc... I am going to rename them. List.Zip combines the 2 lists into the format I need as the argument for Table.RenameColumns.

Hope that helps explain it a bit.
 

GiveMeYourBeans

New Member
Joined
Feb 10, 2021
Messages
6
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows

ADVERTISEMENT

This code is a bit cleaner and probably easier to follow. This one doesn't have a bunch of nested formulas on one line like the last one.

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Last Name", "First Name"}, "Attribute", "Value"),
    RemoveNum = Table.TransformColumns(Unpivot,{{"Attribute", each Text.Replace(Text.Select(_,{"A".."z"," "}),"  "," ")}}),
    Index = Table.AddIndexColumn(RemoveNum, "Index", 0, 1, Int64.Type),
    IntDiv = Table.AddColumn(Index, "Integer-Division", each Number.IntegerDivide([Index], 4), Int64.Type),
    RemoveIndex = Table.RemoveColumns(IntDiv,{"Index"}),
    Pivot = Table.Pivot(RemoveIndex, List.Distinct(RemoveIndex[Attribute]), "Attribute", "Value"),
    RemoveInt = Table.RemoveColumns(Pivot,{"Integer-Division"})
in
    RemoveInt
I thought a small example of my dataset would help me with the larger dataset. Unfortunately I was wrong. Would you be willing to help me with the full file? I've been running into a wall all weekend.
 

GiveMeYourBeans

New Member
Joined
Feb 10, 2021
Messages
6
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Sure thing.
First, thank you so much for the help. I can't put into words how helpful this community seems to be.

I'll submit the a full example if we are unable to resolve but I think I have an idea where my major issue is.

I think it is here:
List = Table.AddColumn(Source, "Custom", each List.Split(List.RemoveItems(List.Skip(Record.FieldValues(_),2),{null}),4))

In my "real" file there are 101 columns before the location and remit columns. Additionally (and I think this is causing me the most confusion), the "location" columns make up 14 columns and the "remit" locations make up 10 columns. I can't seem to figure out how to adjust the code you forwarded based off this information.
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
3,097
Office Version
  1. 365
Platform
  1. Windows
I would go with the code I posted in Post #5. But, below is a slight amendment to the code in that post that should account for how many columns you will need. Meaning that you don't need to hard code that number in. Hopefully the code should just work.

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Unpivot = Table.UnpivotOtherColumns(Source, {"Last Name", "First Name"}, "Attribute", "Value"),
    RemoveNum = Table.TransformColumns(Unpivot,{{"Attribute", each Text.Replace(Text.Select(_,{"A".."z"," "}),"  "," ")}}),
    Index = Table.AddIndexColumn(RemoveNum, "Index", 0, 1, Int64.Type),
    IntDiv = Table.AddColumn(Index, "Integer-Division", each Number.IntegerDivide([Index], List.Count(List.Distinct(Index[Attribute]))), Int64.Type),
    RemoveIndex = Table.RemoveColumns(IntDiv,{"Index"}),
    Pivot = Table.Pivot(RemoveIndex, List.Distinct(RemoveIndex[Attribute]), "Attribute", "Value"),
    RemoveInt = Table.RemoveColumns(Pivot,{"Integer-Division"})
in
    RemoveInt
 

Watch MrExcel Video

Forum statistics

Threads
1,127,529
Messages
5,625,351
Members
416,096
Latest member
forevans

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
Top