Building a list from table

[Luin29]

Hello everyone,

I would like some help writing a code that will take a table I use and turn it into a list.

I would like to take this current table:

Well ID	8260B	7199	314.0
MW-1C	--	1	1
MW-2B	1	--	1
MW-3A	--	--	1

<tbody>
</tbody>

And alter it so that it will look like this:

Well ID	Test
MW-1C	7199
MW-1C	314.0
MW-2B	8260
MW-2B	314.0
MW-3A	314.0

<tbody>
</tbody>

In short: Create a row for each well and each test.

I have spent some time writing a few lines of code (which will copy, paste, and delete rows of data to achieve the final product) but I would like to see if anyone has other ideas (I am currently researching how to perform this task through the use of setting up a dictionary but am not sure how it would work if I was to use the same code on a different table that had more test).

Any and all help is greatly appreciated.

Thanks!

 

[sandy666]

you can try PowerQuery

Well ID	8260B	7199	314.0		Well ID	Attribute
MW-1C	--	1​	1​		MW-1C	7199
MW-2B	1​	--	1​		MW-1C	314.0
MW-3A	--	--	1​		MW-2B	8260B
					MW-2B	314.0
					MW-3A	314.0

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Well ID"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] = 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Value"})
in
    #"Removed Columns"[/SIZE]

 

[Luin29]

I have not used Power Query before, so I will look into that as well.

 

[sandy666]

Excel 2010/2013 PowerQuery add-in
Excel 2016/365/2019 PowerQuery aka Get&Transform built-in