Log In
Register
Home
New Member
June 22, 2023
Offline
Hi All,
Apologies if this has been asked before but I was not able to find any other question relevant to my concern.
Need your kind help in one of my Excel projects where I have been given thousands of 10-digit numbers and I have to find out which specific digits are used along with which specific digit is used and how many times it has been used within that 10-digit number.
Attaching the workbook for your reference. Any help is highly appreciated.
Thanks and Regards,
Rahul.
October 5, 2010
Offline
Hi Rahul,
You can do this in Power Query with this code which you'll find in the attached file. NOTE the column containing the 'numbers' must be text. make sure PQ doesn't automatically change the column type to Any (ABC 123)
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Number", type text}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "All Numbers Used", each if [Number] is null then null else Text.Combine(List.Distinct(Text.ToList(Text.Remove([Number], " "))))),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "0's", each Text.Length(Text.Select([Number], "0"))),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "1's", each Text.Length(Text.Select([Number], "1"))),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "2's", each Text.Length(Text.Select([Number], "2"))),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "3's", each Text.Length(Text.Select([Number], "3"))),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "4's", each Text.Length(Text.Select([Number], "4"))),
#"Added Custom6" = Table.AddColumn(#"Added Custom5", "5's", each Text.Length(Text.Select([Number], "5"))),
#"Added Custom7" = Table.AddColumn(#"Added Custom6", "6's", each Text.Length(Text.Select([Number], "6"))),
#"Added Custom8" = Table.AddColumn(#"Added Custom7", "7's", each Text.Length(Text.Select([Number], "7"))),
#"Added Custom9" = Table.AddColumn(#"Added Custom8", "8's", each Text.Length(Text.Select([Number], "8"))),
#"Added Custom10" = Table.AddColumn(#"Added Custom9", "9's", each Text.Length(Text.Select([Number], "9"))),
#"Added Custom11" = Table.AddColumn(#"Added Custom10", "Sum", each Text.Length(Text.Remove([Number], " ")))
in
#"Added Custom11"
Regards
Phil
July 16, 2010
Offline
Hi Rahul,
You can do this with formulas too, however the count of digits used requires splitting the number into separate columns, then using COUNTIF to summarise, as opposed to being able to do it in one formula. See file attached.
Mynda
1 Guest(s)
