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 there are two rows with the same value, I want to put the second row’s column B’s value into column C.

4)  When there is only one row put the value in its column B into column C

 

Here’s the part I cannot figure out:

5)  With combining complete, I want to hide the rows where column C is blank. I can use autofilter to do this but here’s the rub:

6)  I want to copy the visible rows to another location in the spreadsheet. I can’t do that with filtered cell.

 

Here I a example spreadsheet

Before any changes:

A1     TC

A1     R3

A5     BG

A5     6I

A6     Y9

B3     II

B3     KQ

 

After 4 above:

A1     TC     R3

A1     R3

A5     BG     6I

A5     6I

A6     Y9     A6

B3     II     KQ

B3     KQ

 

After filter:

A1     TC     R3

A5     BG     6I

A6     Y9     A6

B3     II       KQ

 

Any suggestions would be appreciated

Dave 

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