Forum Discussion
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
11000
11001
11002
11003
1100
23000
11000
23001
1200
23003
1250
23001
32501
I want to add another column that will show the four digit number on the row of each fiwe digit number;
1000 11000
1000 11001
1000 11002
1000 11003
1100 23000
1100 11000
1100 23001
1200 23003
1250 23001
1250 32501
Is it possible?
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?
16 Replies
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 RemoveSourceColumnWith formulas - perhaps also possible, but better to know how exactly your data is structured.
Wonderful! Thank you!
Next question is; will the information that I have in colums B to P (because I have a lot of information on each row) remain connected to th five digit numbers using this method?
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?
