CODEWITHSUNDEEP
excelexcel-formulavba
Andreea Dana Enache
Andreea Dana Enache

Reputation: 73

Excel filtering for merged cells

I'm trying to make a calculation of hours worked for each employee on each project that he worked on. But i don't know how to select because the cells that are containing the name of the employee are merged like in the picture. And if i want to see on project no. 3 which are the employees that worked on, the Excel Filtering can't take the name "John" which corresponds only to project no.1. To be more clear, I need to know how the filtering will be made for cells A3 and A4. If i will unmerge the cells, John will be only on cell A2, and in fact he worked also on projects 2 & 3.

Thanks!

Excel screenshot

Upvotes: 7

Views: 44334

Answers (3)

bbj
bbj

Reputation: 1

The green tick answer by Chronocidal requires use of Macro. Personally, I don't think it is necessary to go through such cumbersome process.

Suppose merged cells are in Column A; first merged data is in cell A2. Make a new column on the right of merged cell column [B Column]. In the cell B2, type =IF(LEN(A2)=0,B1,A2) and drag this formula down. It will fill data correctly and you can apply filter on column B.

Note: Formula checks length of characters of left cell. If it is zero, uses cell directly above as answer; otherwise uses left cell as is.

Excel Snapshot of Formula

Upvotes: 0

Adam Porter
Adam Porter

Reputation: 21

One other option;

Conditional formatting background for all cells with a particular value (e.g. John, red background) and then filter by colour.

Wouldn't suit all applications but nice and quick.

Upvotes: 2

Chronocidal
Chronocidal

Reputation: 7951

If you have a Merged Cell, and you attempt to Filter for it, you will only get the first row:
Rows with Merged Cells
Filtered Merged Cells only shows first row

To fix this, you first need to start by creating your Merged Cells somewhere else, unmerge your filter-cells, and fill the values into all cells:
Table cells unmerged, merged cells on right

Then, you can Copy the merged cells, and Paste Special > Formats over the cells you want to merge:
Copying the Merged Cells and using Paste Special to put format in data table
The Merged Cell formatting pasted in place

You can now delete your temporary merged cells, and when you filter you will get all rows for the merged cell:
enter image description here

 
{EDIT} Here is a macro that will automatically apply the changes above to a specified range: 

Public Sub FilterableMergedCells()
    Dim WorkingRange As Range
SelectRange:
    Set WorkingRange = Nothing
    On Error Resume Next
    Set WorkingRange = Application.InputBox("Select a range", "Get Range", Type:=8)
    On Error GoTo 0
    'If you click Cancel
    If WorkingRange Is Nothing Then Exit Sub
    'If you select multiple Ranges
    If WorkingRange.Areas.Count > 1 Then
        MsgBox "Please select 1 continuous range only", vbCritical
        GoTo SelectRange
    End If

    Dim ScreenUpdating As Boolean, DisplayAlerts As Boolean, Calculation As XlCalculation
    ScreenUpdating = Application.ScreenUpdating
    DisplayAlerts = Application.DisplayAlerts
    Calculation = Application.Calculation

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual

    Dim WorkingCell As Range, MergeCell As Range, MergeRange As Range, OffsetX As Long, OffsetY As Long
    OffsetX = WorkingRange.Cells(1, 1).Column - 1
    OffsetY = WorkingRange.Cells(1, 1).Row - 1
    'Create temporary sheet to work with
    With Worksheets.Add
        WorkingRange.Copy .Cells(1, 1)
        'Loop through cells in Range
        For Each WorkingCell In WorkingRange.Cells
            'If is a merged cell
            If WorkingCell.MergeCells Then
                'If is the top/left merged cell in a range
                If Not Intersect(WorkingCell, WorkingCell.MergeArea.Cells(1, 1)) Is Nothing Then
                    Set MergeRange = WorkingCell.MergeArea
                    'Unmerge cells
                    MergeRange.MergeCells = False
                    'Replicate value to all cells in formerly merged area
                    For Each MergeCell In MergeRange.Cells
                        If WorkingCell.FormulaArray = vbNull Then
                            MergeCell.Formula = WorkingCell.Formula
                        Else
                            MergeCell.FormulaArray = WorkingCell.FormulaArray
                        End If
                    Next MergeCell
                    'Copy merge-formatting over old Merged area
                    .Cells(WorkingCell.Row - OffsetY, WorkingCell.Column - OffsetX).MergeArea.Copy
                    WorkingCell.PasteSpecial xlPasteFormats
                End If
            End If
        Next WorkingCell
        .Delete
    End With

    Set MergeRange = Nothing
    Set WorkingRange = Nothing

    Application.ScreenUpdating = ScreenUpdating
    Application.DisplayAlerts = DisplayAlerts
    Application.Calculation = Calculation
End Sub

Upvotes: 15

Related Questions