CODEWITHSUNDEEP
excellistfiltertransform
Andreas
Andreas

Reputation: 53

Transform Excel table into list skipping blank values

Unfortunately I have not found a solution in the web - hope someone can help me with the following:

My input is an excel table like the following:

|         |   Jan   |  Feb    |  Mar    |  Apr    | 
| Topic 1 |   yes   |         |  yes    |         | 
| Topic 2 |         |  yes    |         |         | 
| Topic 3 |   yes   |         |         |  yes    | 
| Topic 4 |         |         |         |  yes    |

My desired output is a list where all blank elements are eliminated. It should look like this:

|         |   Jan   |  Feb    |  Mar    |  Apr    | 
|         | Topic 1 | Topic 2 | Topic 1 | Topic 3 | 
|         | Topic 3 |         |         | Topic 4 |

Anyone has a smart idea how to do this? I thought a second about a pivot table but was not able to do it.

PS: I can easily handle the intermediate step to replace yes in the input table with the respective Topic *, but I am not able to perform the second step of skipping the blanks.

Upvotes: 2

Views: 1379

Answers (3)

Gary's Student
Gary's Student

Reputation: 96791

If we start with:

enter image description here

We can first replace the "yes"s. Then remove the blanks. Then clear the first column. We run macro MAIN

Sub MAIN()
    Call step1
    Call step2
    Call step3
End Sub

Sub step1()
    For Each r In ActiveSheet.UsedRange
        If r.Value = "yes" Then
            r.Value = Cells(r.Row, 1).Value
        End If
    Next r
End Sub

Sub step2()
    Dim r As Range
    Set r = ActiveSheet.UsedRange.Cells.SpecialCells(xlCellTypeBlanks)
    r.Delete (xlShiftUp)
End Sub

Sub step3()
    Range("A:A").Clear
End Sub

to produce:

enter image description here

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60474

For a formula solution, assuming your data is in A1:E5, first copy the months into a new horizontal range, then, in the cell below Jan, enter the following formula as an array-formula by holding down ctrl+shift while hitting enter. (If you do this correctly, Excel will place braces {...} around the formula in the formula bar:

=IFERROR(INDEX($A$1:$A$5,SMALL((B$2:B$5="yes")*ROW($A$2:$A$5),ROWS($1:1)+COUNTBLANK(B$2:B$5))),"")

Adjust the cell references as appropriate, but keep the addressing methods as shown in the formula.

Then drag down and to the right.

Upvotes: 2

pnuts
pnuts

Reputation: 59495

Select the appropriate range, HOME > Editing, Find & Select, Go To Special..., Blanks, select one of those selected, Delete..., Shift cells up, OK.

Upvotes: 0

Related Questions