Forum Discussion

Copper Contributor

Oct 17, 2017

Solved

Column headings to rows - changing my list

Is it possible to get excel to return a cell value if another cell meets my criteria, many times over varying spaces? I have a list that might look like this (only muuuch longer) 1000 110...

BI & Data Analytics

Need Help

SergeiBaklan
Oct 18, 2017
Hi Sheena,

Do you mean the information in these columns is only in rows with 5 digits numbers and in resulting table that is to be in the rows against same 5 digits?

SergeiBaklan

Diamond Contributor

Oct 17, 2017

Hi Sheena,

Perhaps the easiest way is to use Power Query (Get&Transform in Excel 2016), that looks like

and the script generated from user interface is

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AsNumbers = Table.TransformColumnTypes(Source,{{"Numbers", Int64.Type}}),
    OnlyNumbers = Table.SelectRows(AsNumbers, each ([Numbers] <> null)),
    #"4Digits" = Table.AddColumn(OnlyNumbers, "4Digits", each if [Numbers] < 10000 then [Numbers] else null),
    NullToNumber = Table.FillDown(#"4Digits",{"4Digits"}),
    #"5Digits" = Table.AddColumn(NullToNumber, "5digits", each if [Numbers] >= 10000 then [Numbers] else null),
    NoNulls = Table.SelectRows(#"5Digits", each ([5digits] <> null)),
    RemoveSourceColumn = Table.RemoveColumns(NoNulls,{"Numbers"})
in
    RemoveSourceColumn

With formulas - perhaps also possible, but better to know how exactly your data is structured.