Forum Discussion

Dave Shaffer's avatar
Dave Shaffer
Copper Contributor
Apr 13, 2018

Combine columns from two different rows, filter results and copy to a different place in ss

I have a ss in column A is a set of codes and column B is an associated value. 1)  There can be one or two occurrences of the code in column A. 2)  The combination of column A is unique 3)  When t...
Excel
SergeiBaklan's avatar
SergeiBaklan
MVP
Apr 14, 2018

Hi Dave,

 

Perhaps easier to do with Power Query (Microsoft add-in for Excel 2010, 2013 or built-in Get&Transform for Excel 2016). Result as

generated script

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddIndex = Table.AddIndexColumn(Source, "Index", 0, 1),
    AddSecond = Table.AddColumn(AddIndex, "Second", each
        try (if [Code]=Source{[Index]+1}[Code] then Source{[Index]+1}[Value] else [Code])
        otherwise [Code]),
    RemoveDuplicates = Table.Distinct(AddSecond, {"Code"}),
    RemoveIndex = Table.RemoveColumns(RemoveDuplicates,{"Index"})
in
    RemoveIndex

Please see attached

Resources