How to sort information faster than manually doing it.

[zacari01]

Basically what I'm doing is sorting peoples information in just 1 row. It's so tiring in doing cut and paste if I'm doing 20,000 information in a sheet.
What I want to happen is to sort John Smith's other phone# (from The Philippines) and to put it on phone#2 and phone#3. Also John Smith's other phone# (from US) to phone#2 and 3.
This might be tricky because some people might have similar names all over the world and I dont want to mix their information. The only way to identify them (which is which) is through their address..

Example:

testfirstname	testsurname	testphone#1	testphone#2	testphone#3	testaddress
John	Smith	(000) 252-1618			PHILIPPINES
John	Smith	(000) 454-1617			PHILIPPINES
John	Smith	(000) 656-1619			PHILIPPINES
John	Smith	(222) 181-2222			UNITED STATES OF AMERICA
John	Smith	(222) 181-3333			UNITED STATES OF AMERICA
John	Smith	(222) 181-3333			UNITED STATES OF AMERICA

 

[mikerickson]

Insert a helper column between testsurname and testphone#1. For this example, that would be column C.
If name + testaddress is the identifier you want,
Then put =A2&B2&G2 in C2 and drag down.

Then in the testphone#2 column (column E)
in E2, put =IFERROR(VLOOKUP($C2, $C3:$D10000, 2, False), "") And drag down

for telephon#3
the in F2 put =IFERROR(VLOOKUP($C2,$C3:E$999,3,FALSE),"") and drag down.

 

[zacari01]

Thanks for giving your time to response. It works in telephone #2 however in telephone number 3 its show "0" instead of the person's 3rd phone number.

 

[mikerickson]

It should show all three number on the first row of that person's data, fewer thereafter.
Also, all of the formulas in column E (second number) have to be in place for the column F formula to succeed.

 

[zacari01]

Sorry for my late response. I finally made it work! thanks for your help! You made my work a lot easier. I used to do it manually before for 13,000-15,000 data

 

[pjmorris]

mike, hope you don't mind an alternative approach using PowerQuery.

Zacari, this has the advantage that you only need to 'Refresh All' to update any changes you may have to your data.

1. While in your source data, click 'From Table' in the 'Get & Transform' tab of the Data Ribbon. This will import your data into PowerQuery and present you with the PowerQuery edit window.
2. On the Query tab of the Home ribbon (toward the left) you'll find the 'Advanced Editor' button. Click this and you will see a window that starts with:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

Where 'Table1' will have the name of your table. Delete the entire contents of the window and replace with this, if you table has some other name than Table1 replace it in the new Source= line:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"testfirstname", type text}, {"testsurname", type text}, {"testphone#1", type text}, {"testphone#2", type any}, {"testphone#3", type any}, {"testaddress", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Key", each [testfirstname] & "|" & [testsurname]& "|" &[testaddress]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"testfirstname", "testsurname", "testphone#2", "testphone#3", "testaddress"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Key"}, {{"Count", each Table.RowCount(_), type number}, {"Rows", each _, type table [Key=text, #"testphone#1"=text]}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"testphone#1"}, {"testphone#1"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded Rows", "Index", 0, 1),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each Number.Mod([Index],[Count])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "ColumName", each "Phone Number " & Text.From([Custom]+1)),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom2",{"Key", "testphone#1", "Count", "Index", "Custom", "ColumName"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns1",{"Count", "Index", "Custom"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[ColumName]), "ColumName", "testphone#1"),
    #"Demoted Headers" = Table.DemoteHeaders(#"Pivoted Column"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Sorted Rows" = Table.Sort(#"Transposed Table",{{"Column1", Order.Ascending}}),
    #"Transposed Table1" = Table.Transpose(#"Sorted Rows"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Promoted Headers", "Key", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Key.1", "Key.2", "Key.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Key.1", type text}, {"Key.2", type text}, {"Key.3", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Key.1", "FirstName"}, {"Key.2", "LastName"}, {"Key.3", "Address"}})
in
    #"Renamed Columns"

and then click 'Done'. You should return to the PowerQuery Editor window and be presented with your data arranged as you wish. To get it into your spreadsheet simply click on Close&Load on the Home Ribbon and the new table will appear as a new sheet.

For me this is an easier solution and will cater for the fact that someone might have more than 3 phone numbers. The only disadvantages I can see is that it creates a new sheet which you may not want, and I can't ensure that the top phone number in your list will appear under Phone Number 1 in the output. Perhaps someone with more PowerQuery knowledge than I knows how to do so.

HTH

 

[zacari01]

mike, hope you don't mind an alternative approach using PowerQuery.

Zacari, this has the advantage that you only need to 'Refresh All' to update any changes you may have to your data.

1. While in your source data, click 'From Table' in the 'Get & Transform' tab of the Data Ribbon. This will import your data into PowerQuery and present you with the PowerQuery edit window.
2. On the Query tab of the Home ribbon (toward the left) you'll find the 'Advanced Editor' button. Click this and you will see a window that starts with:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],

Where 'Table1' will have the name of your table. Delete the entire contents of the window and replace with this, if you table has some other name than Table1 replace it in the new Source= line:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"testfirstname", type text}, {"testsurname", type text}, {"testphone#1", type text}, {"testphone#2", type any}, {"testphone#3", type any}, {"testaddress", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Key", each [testfirstname] & "|" & [testsurname]& "|" &[testaddress]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"testfirstname", "testsurname", "testphone#2", "testphone#3", "testaddress"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Key"}, {{"Count", each Table.RowCount(_), type number}, {"Rows", each _, type table [Key=text, #"testphone#1"=text]}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"testphone#1"}, {"testphone#1"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded Rows", "Index", 0, 1),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each Number.Mod([Index],[Count])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "ColumName", each "Phone Number " & Text.From([Custom]+1)),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom2",{"Key", "testphone#1", "Count", "Index", "Custom", "ColumName"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns1",{"Count", "Index", "Custom"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[ColumName]), "ColumName", "testphone#1"),
    #"Demoted Headers" = Table.DemoteHeaders(#"Pivoted Column"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Sorted Rows" = Table.Sort(#"Transposed Table",{{"Column1", Order.Ascending}}),
    #"Transposed Table1" = Table.Transpose(#"Sorted Rows"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Promoted Headers", "Key", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Key.1", "Key.2", "Key.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Key.1", type text}, {"Key.2", type text}, {"Key.3", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Key.1", "FirstName"}, {"Key.2", "LastName"}, {"Key.3", "Address"}})
in
    #"Renamed Columns"

and then click 'Done'. You should return to the PowerQuery Editor window and be presented with your data arranged as you wish. To get it into your spreadsheet simply click on Close&Load on the Home Ribbon and the new table will appear as a new sheet.

For me this is an easier solution and will cater for the fact that someone might have more than 3 phone numbers. The only disadvantages I can see is that it creates a new sheet which you may not want, and I can't ensure that the top phone number in your list will appear under Phone Number 1 in the output. Perhaps someone with more PowerQuery knowledge than I knows how to do so.

HTH

Hi Pj, I tried to do this however I'm having an error in Reordered Columns1 -- It doesnt detect the "Phone1" in the table even if there is.

 

[pjmorris]

Hi, Zac,

PQ is pretty pedantic about column names, I simply used the ones that were in your example above. The easiest solution is to simply make sure your table column names match what's in the example exactly. The alternative is to go through and change the names in the code to match those in your table.

Perhaps the ideal is to simply use the table above as an example then highlight each step in the PQ Editor and you''ll be able to see what is happening.

Final option: let me know exactly what your column names are and I'll redo the code!

HTH

 

[zacari01]

First Name

Last Name

Phone1

Phone2

Phone3

Mailing Street

 

[pjmorris]

try this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"First Name", type text}, {"Last Name", type text}, {"Phone1", type text}, {"Phone2", type any}, {"Phone3", type any}, {"Mailing Street", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Key", each [First Name] & "|" & [Last Name]& "|" &[Mailing Street]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"First Name", "Last Name", "Phone2", "Phone3", "Mailing Street"}),
    #"Grouped Rows" = Table.Group(#"Removed Columns", {"Key"}, {{"Count", each Table.RowCount(_), type number}, {"Rows", each _, type table [Key=text, #"Phone1"=text]}}),
    #"Expanded Rows" = Table.ExpandTableColumn(#"Grouped Rows", "Rows", {"Phone1"}, {"Phone1"}),
    #"Added Index" = Table.AddIndexColumn(#"Expanded Rows", "Index", 0, 1),
    #"Added Custom1" = Table.AddColumn(#"Added Index", "Custom", each Number.Mod([Index],[Count])),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "ColumName", each "Phone Number " & Text.From([Custom]+1)),
    #"Reordered Columns1" = Table.ReorderColumns(#"Added Custom2",{"Key", "Phone1", "Count", "Index", "Custom", "ColumName"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Reordered Columns1",{"Count", "Index", "Custom"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"[ColumName]), "ColumName", "Phone1"),
    #"Demoted Headers" = Table.DemoteHeaders(#"Pivoted Column"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}}),
    #"Transposed Table" = Table.Transpose(#"Changed Type1"),
    #"Sorted Rows" = Table.Sort(#"Transposed Table",{{"Column1", Order.Ascending}}),
    #"Transposed Table1" = Table.Transpose(#"Sorted Rows"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table1", [PromoteAllScalars=true]),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Promoted Headers", "Key", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Key.1", "Key.2", "Key.3"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Key.1", type text}, {"Key.2", type text}, {"Key.3", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Key.1", "FirstName"}, {"Key.2", "LastName"}, {"Key.3", "Address"}})
in
    #"Renamed Columns"