Convert Data

Sort by date Sort by votes
Thank you, if I look row 3 (which starts with ID.1 of 4), I see Addr3 in Mail Street Number1.1 and Addr5 in Mail Street Number1.2 . In one row they need to be the same Addr number. Is that possible?
 
Upvote 0

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
like this?
it doesn't contain Cities, States and Zips

ID.1Distance.1First Name.1Level.1Mail Street Number1.1Mail Street Number2.1Building.1ID.2Distance.2First Name.2Level.2Mail Street Number1.2Mail Street Number2.2Building.2ID.3Distance.3First Name.3Level.3Mail Street Number1.3Building.3ID.4Distance.4First Name.4Level.4Mail Street Number1.4Building.4
1​
0.072​
FN107Addr1R
2​
0.111​
FN201Addr2K
3​
0.111​
FN3
3​
Addr2K
4​
0.114​
FN405Addr32Addr3D
5​
0.119​
FN507Addr4H
6​
0.119​
FN603Addr4O
6​
0.119​
FN7
3​
Addr4O
6​
0.119​
FN8
3​
Addr4O
6​
0.119​
FN9
3​
Addr4O
7​
0.12​
FN1002Addr5D
8​
0.13​
FN1108Addr6H
9​
0.132​
FN1205Addr72Addr7D
10​
0.132​
FN1308Addr8H
11​
0.151​
FN1402Addr9D
12​
0.156​
FN15AAddr10K
13​
0.156​
FN1601Addr11P
14​
0.156​
FN17
2​
Addr11P
14​
0.156​
FN18
2​
Addr11P
15​
0.158​
FN1901Addr12D
16​
0.158​
FN2004Addr13D
17​
0.158​
FN2104Addr14D
18​
0.162​
FN2204Addr15P
19​
0.164​
FN23AAddr16B
 
Upvote 0
I think you have it except for rows 4 and 5 should be on one row. If you can get that corrected, which I suspect you can, can you tell me how you did this?

Thanks!
 
Upvote 0
like this?

ID.1Distance.1Last Name.1First Name.1Level.1Mail Street Number1.1Mail Street Number2.1Building.1.1ID.2Distance.2Last Name.2First Name.2Level.2Mail Street Number1.2Mail Street Number2.2Building.1.2ID.3Distance.3Last Name.3First Name.3Level.3Mail Street Number1.3Building.1.3ID.4Distance.4Last Name.4First Name.4Level.4Mail Street Number1.4Building.1.4ID.5Distance.5Last Name.5First Name.5Level.5Mail Street Number1.5Building.1.5
1​
0.072​
LN1FN107Addr1R
2​
0.111​
LN2FN201Addr2K
3​
0.111​
LN2FN3
3​
Addr2K
4​
0.114​
LN3FN405Addr32Addr3D
5​
0.119​
LN4FN507Addr4H
6​
0.119​
LN4FN6
3​
Addr4O
6​
0.119​
LN4FN7
3​
Addr4O
6​
0.119​
LN4FN8
3​
Addr4O
6​
0.119​
LN4FN9
3​
Addr4O
7​
0.12​
LN5FN1002Addr5D
8​
0.13​
LN6FN1108Addr6H
9​
0.132​
LN7FN1205Addr72Addr7D
10​
0.132​
LN8FN1308Addr8H
11​
0.151​
LN9FN1402Addr9D
12​
0.156​
LN10FN15AAddr10K
13​
0.156​
LN11FN1601Addr11P
14​
0.156​
LN11FN17
2​
Addr11P
14​
0.156​
LN11FN18
2​
Addr11P
15​
0.158​
LN12FN1901Addr12D
16​
0.158​
LN13FN2004Addr13D
17​
0.158​
LN14FN2104Addr14D
18​
0.162​
LN15FN2204Addr15P
19​
0.164​
LN16FN23AAddr16B
 
Upvote 0
Post#3 and part of code here :LOL: :devilish:

Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Distance", type number}, {"Last Name", type text}, {"First Name", type text}, {"Level", type text}, {"Mail Street Number1", type text}, {"Mail Street Number2", type text}, {"Mail City", type text}, {"Mail State", type text}, {"Mail Zip Code", type text}, {"Building", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Mail Street Number1"}, {{"Count", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "ID", each Table.Column([Count],"ID")),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom", {"ID", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom1" = Table.AddColumn(#"Extracted Values", "Distance", each Table.Column([Count],"Distance")),
    #"Extracted Values1" = Table.TransformColumns(#"Added Custom1", {"Distance", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom7" = Table.AddColumn(#"Extracted Values1", "Last Name", each Table.Column([Count],"Last Name")),
    #"Extracted Values7" = Table.TransformColumns(#"Added Custom7", {"Last Name", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom2" = Table.AddColumn(#"Extracted Values7", "First Name", each Table.Column([Count],"First Name")),
    #"Extracted Values2" = Table.TransformColumns(#"Added Custom2", {"First Name", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom3" = Table.AddColumn(#"Extracted Values2", "Level", each Table.Column([Count],"Level")),
    #"Extracted Values3" = Table.TransformColumns(#"Added Custom3", {"Level", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom4" = Table.AddColumn(#"Extracted Values3", "Mail Street Number1.1", each Table.Column([Count],"Mail Street Number1")),
    #"Extracted Values4" = Table.TransformColumns(#"Added Custom4", {"Mail Street Number1.1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom5" = Table.AddColumn(#"Extracted Values4", "Mail Street Number2", each Table.Column([Count],"Mail Street Number2")),
    #"Extracted Values5" = Table.TransformColumns(#"Added Custom5", {"Mail Street Number2", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Added Custom6" = Table.AddColumn(#"Extracted Values5", "Building.1", each Table.Column([Count],"Building")),
    #"Extracted Values6" = Table.TransformColumns(#"Added Custom6", {"Building.1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values6",{"Mail Street Number1", "Count"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Removed Columns", "ID", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"ID.1", "ID.2", "ID.3", "ID.4", "ID.5"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ID.1", Int64.Type}, {"ID.2", Int64.Type}, {"ID.3", Int64.Type}, {"ID.4", Int64.Type}, {"ID.5", Int64.Type}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1", "Distance", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Distance.1", "Distance.2", "Distance.3", "Distance.4", "Distance.5"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Distance.1", type number}, {"Distance.2", type number}, {"Distance.3", type number}, {"Distance.4", type number}, {"Distance.5", type number}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type2", "Last Name", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Last Name.1", "Last Name.2", "Last Name.3", "Last Name.4", "Last Name.5"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"Last Name.1", type text}, {"Last Name.2", type text}, {"Last Name.3", type text}, {"Last Name.4", type text}, {"Last Name.5", type text}}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Changed Type3", "First Name", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"First Name.1", "First Name.2", "First Name.3", "First Name.4", "First Name.5"}),
    #"Changed Type4" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"First Name.1", type text}, {"First Name.2", type text}, {"First Name.3", type text}, {"First Name.4", type text}, {"First Name.5", type text}}),
    #"Split Column by Delimiter4" = Table.SplitColumn(#"Changed Type4", "Level", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Level.1", "Level.2", "Level.3", "Level.4", "Level.5"}),
    #"Changed Type5" = Table.TransformColumnTypes(#"Split Column by Delimiter4",{{"Level.1", type text}, {"Level.2", Int64.Type}, {"Level.3", Int64.Type}, {"Level.4", Int64.Type}, {"Level.5", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type5",{{"Mail Street Number1.1", "Mail Street Number1"}}),
    #"Split Column by Delimiter5" = Table.SplitColumn(#"Renamed Columns", "Mail Street Number1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Mail Street Number1.1", "Mail Street Number1.2", "Mail Street Number1.3", "Mail Street Number1.4", "Mail Street Number1.5"}),
    #"Changed Type6" = Table.TransformColumnTypes(#"Split Column by Delimiter5",{{"Mail Street Number1.1", type text}, {"Mail Street Number1.2", type text}, {"Mail Street Number1.3", type text}, {"Mail Street Number1.4", type text}, {"Mail Street Number1.5", type text}}),
    #"Split Column by Delimiter6" = Table.SplitColumn(#"Changed Type6", "Mail Street Number2", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Mail Street Number2.1", "Mail Street Number2.2"}),
    #"Changed Type7" = Table.TransformColumnTypes(#"Split Column by Delimiter6",{{"Mail Street Number2.1", type text}, {"Mail Street Number2.2", type text}}),
    #"Split Column by Delimiter7" = Table.SplitColumn(#"Changed Type7", "Building.1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Building.1.1", "Building.1.2", "Building.1.3", "Building.1.4", "Building.1.5"}),
    #"Changed Type8" = Table.TransformColumnTypes(#"Split Column by Delimiter7",{{"Building.1.1", type text}, {"Building.1.2", type text}, {"Building.1.3", type text}, {"Building.1.4", type text}, {"Building.1.5", type text}}),
    #"Demoted Headers" = Table.DemoteHeaders(#"Changed Type8"),
    #"Changed Type9" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}, {"Column5", type any}, {"Column6", type any}, {"Column7", type any}, {"Column8", type any}, {"Column9", type any}, {"Column10", type any}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}, {"Column14", type text}, {"Column15", type text}, {"Column16", type text}, {"Column17", type text}, {"Column18", type text}, {"Column19", type text}, {"Column20", type text}, {"Column21", type text}, {"Column22", type any}, {"Column23", type any}, {"Column24", type any}, {"Column25", type any}, {"Column26", type text}, {"Column27", type text}, {"Column28", type text}, {"Column29", type text}, {"Column30", type text}, {"Column31", type text}, {"Column32", type text}, {"Column33", type text}, {"Column34", type text}, {"Column35", type text}, {"Column36", type text}, {"Column37", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type9"),
    #"Inserted Last Characters" = Table.AddColumn(#"Transposed Table", "Last Characters", each Text.End([Column1], 1), type text),
    #"Added Index" = Table.AddIndexColumn(#"Inserted Last Characters", "Index", 1, 1),
    #"Sorted Rows" = Table.Sort(#"Added Index",{{"Last Characters", Order.Ascending}, {"Index", Order.Ascending}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Sorted Rows",{"Last Characters", "Index"}),
    #"Transposed Table1" = Table.Transpose(#"Removed Columns1"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Changed Type10" = Table.TransformColumnTypes(#"Promoted Headers",{{"ID.1", Int64.Type}, {"Distance.1", type number}, {"Last Name.1", type text}, {"First Name.1", type text}, {"Level.1", type text}, {"Mail Street Number1.1", type text}, {"Mail Street Number2.1", type text}, {"Building.1.1", type text}, {"ID.2", Int64.Type}, {"Distance.2", type number}, {"Last Name.2", type text}, {"First Name.2", type text}, {"Level.2", Int64.Type}, {"Mail Street Number1.2", type text}, {"Mail Street Number2.2", type any}, {"Building.1.2", type text}, {"ID.3", Int64.Type}, {"Distance.3", type number}, {"Last Name.3", type text}, {"First Name.3", type text}, {"Level.3", Int64.Type}, {"Mail Street Number1.3", type text}, {"Building.1.3", type text}, {"ID.4", Int64.Type}, {"Distance.4", type number}, {"Last Name.4", type text}, {"First Name.4", type text}, {"Level.4", Int64.Type}, {"Mail Street Number1.4", type text}, {"Building.1.4", type text}, {"ID.5", Int64.Type}, {"Distance.5", type number}, {"Last Name.5", type text}, {"First Name.5", type text}, {"Level.5", Int64.Type}, {"Mail Street Number1.5", type text}, {"Building.1.5", type text}})
in
    #"Changed Type10"[/SIZE]
 
Upvote 0
each post here has a number (top right corner, eg this post has #18 )

check this file with the partial result (as I said in post#12)
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

FrumpyJones
  • Solved
Table not showing up in data validation source
Replies
9
Views
90
Georgiboy
Georgiboy
T
  • Question
Pop Up Message if specific cells are greater than other specific cells
Replies
5
Views
513
tabs1063
T
A
  • Question
Macros for cleaning excel file
2
Replies
18
Views
474
jolivanes
jolivanes
J
  • Question
Commission Sheet auto load into commission log
Replies
3
Views
142
Rijnsent
Rijnsent
T
  • Question
how to display in userform and worksheet a Flag(L/H) of the result
Replies
8
Views
273
trying_to_learn_coding
T

Forum statistics

Threads
1,216,037
Messages
6,128,442
Members
449,453
Latest member
jayeshw

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