Forum Discussion
Column headings to rows - changing my list
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?
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?
Yes, perfect! Thank you so much!! You made mye day!
If so it'll be the same script
I have realised it was not as easy as I first thought. The list I have is more complex, so I wonder if this isn't the best way. I try my luck again.
Does anyone know how to make this list look like the list below?
Hovedbokskonto Kontonr Navn 1070 Utsatt skattefordel Hovedbokstransaksjon År Periode Bilagsnr Bilagsdato Bil.art 2016 1 0 0 2016 1 50 01.01.2016 39 2016 1 0 0 Hovedbokskonto Kontonr Navn 1380 Fordringer ansatte Hovedbokstransaksjon År Periode Bilagsnr Bilagsdato Bil.art 2016 1 0 0 2016 1 342 22.12.2015 21 2016 7 42 08.07.2016 1 2016 7 235 29.07.2016 50 2016 8 3 09.08.2016 50 2016 9 42 09.09.2016 50 2016 9 0 0 Hovedbokskonto Kontonr Navn 1500 Kundefordringer Hovedbokstransaksjon År Periode Bilagsnr Bilagsdato Bil.art 2016 1 0 0 2016 1 16 01.01.2016 90 2016 1 62 01.01.2016 90 2016 1 301 01.01.2016 39 2016 1 320 01.01.2016 39 2016 1 3450 04.01.2016 90 This is how I want it to look;
Hovedbokskonto Kontonr Navn 1070 Utsatt skattefordel Hovedbokstransaksjon År Periode Bilagsnr Bilagsdato Bil.art 1070 1 0 0 1070 1 50 42370 39 1070 1 0 0 Hovedbokskonto Kontonr Navn 1380 Fordringer ansatte Hovedbokstransaksjon År Periode Bilagsnr Bilagsdato Bil.art 1380 1 0 0 1380 1 342 42360 21 1380 1 30583 42360 21 1380 1 60228 42370 39 1380 1 30632 42382 21 1380 1 20178 42388 1 1380 2 20243 42401 1 1380 2 1038 42408 50 1380 2 1038 42408 50 1380 2 20186 42409 1 1380 2 20187 42412 1 1380 3 1039 42439 50 1380 3 1039 42439 50 1380 3 1039 42439 50 1380 4 20256 42466 1 1380 4 1040 42468 50 1380 4 1040 42468 50 1380 4 20265 42489 1 1380 5 1041 42499 50 1380 5 1041 42499 50 1380 5 1041 42499 50 1380 6 1042 42530 50 1380 6 1043 42551 50 1380 7 42 42559 1 1380 7 235 42580 50 1380 8 3 42591 50 1380 9 42 42622 50 1380 9 0 0 Hovedbokskonto Kontonr Navn 1500 Kundefordringer Hovedbokstransaksjon År Periode Bilagsnr Bilagsdato Bil.art 1500 1 0 0 1500 1 16 42370 90 1500 1 62 42370 90 1500 1 301 42370 39 1500 1 320 42370 39 1500 1 3450 42373 90 I do not need to know the Year in the first colum (2016), but I need the "kontonr" on each row.