locked
Filter Data with Merged and non merged columns in one RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi there,

    I have an excel spreadsheet that has got merged and non merged columns. What I want to be able to do is, filter a row that has got merged column and non merged columns. But when I filter it only takes the first line, rather than the merged and non merged columns.

    With this data I have one merged column which spans 6 rows, and then in the same row I have 6 rows with different points in, and what I want to do is filter my list, but be able to see the merged coloum aswell as the 6 points.

    Any ideas are much appriciated.

    Cheers

    SAN

    Wednesday, October 5, 2011 9:05 AM

Answers

  • Question
    Sign in to vote
    2
    Sign in to vote

    You would need to insert a new column A, and in A4, use the formula

    =B4

    and in A5, use the formula

    =IF(B5="",A4,B5)

    and copy down, then filter on A.

    Bernie

    HTH, Bernie
    • Marked as answer by SAN_IT Wednesday, October 5, 2011 4:02 PM
    Wednesday, October 5, 2011 3:08 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi San

    Could you put screenshort for what you want to do?

     

     

    Best Regards Jun LOU
    Wednesday, October 5, 2011 1:36 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    You cannot filter across a row - so I have assumed that what you mean is that some cells in columns are merged to serve as the headers, and the data is in the came columns but in the row(s) below the header. If this is incorrect, ignore this post.

    For this example, I have assumed E to J are the column of data and merged cells, column K is free, and the first merged header is in row 1:

    In K1, enter

    =E1

    in K2, enter

    =IF(COUNTA(E2:J2)=1,E2,K1)

    and copy down. Then filter based on column K, and it will show the headers and the data for the selected header value.

     

    HTH, Bernie
    Wednesday, October 5, 2011 1:45 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Bernie / Jun,

    Thank you for your reply. Please see a screen shot of the sort of filtering I wish to achieve.

    What I am trying to do, is filter by "Example", but when I filter it only selects line "4" and not "4 to 9"? The reason is because I wish to be able to see all the names in Column "L" when filtering.

    Any ideas?

    SAN

    Wednesday, October 5, 2011 2:22 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    OK, San,

    I understand now.

    I'll find out it for you.

    Best Regards Jun LOU
    Wednesday, October 5, 2011 2:26 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi San,

    Here is the solution :

    Select <Example 1> and (Blanks) in your example, you'll see your rows from 4 to 9.

     

     

    Best Regards Jun LOU

    • Edited by Jun LOU Wednesday, October 5, 2011 2:54 PM
    Wednesday, October 5, 2011 2:53 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Jun,

    Thank you for your response, but unfortuently still no joy.

    In the screen shot above, this is only the first example, I have approx 40 different ones, with all the other information also. When I select "Example 1" and "blanks" it selects other information, when I would just like it to choose "Example 1".

    Thanks again.

    SAN

    Wednesday, October 5, 2011 3:03 PM
  • Question
    Sign in to vote
    2
    Sign in to vote

    You would need to insert a new column A, and in A4, use the formula

    =B4

    and in A5, use the formula

    =IF(B5="",A4,B5)

    and copy down, then filter on A.

    Bernie

    HTH, Bernie
    • Marked as answer by SAN_IT Wednesday, October 5, 2011 4:02 PM
    Wednesday, October 5, 2011 3:08 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    You are brilliant, Bernie !  Here is the screen short if Sam has the difficulty. :)

     

     

    Best Regards Jun LOU
    Wednesday, October 5, 2011 3:24 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi San,

    Don't forget mark this question is anwsered if you find solution is suitable.

    Benie deserves it.

    :)

    Best Regards Jun LOU
    • Edited by Jun LOU Wednesday, October 5, 2011 3:30 PM
    Wednesday, October 5, 2011 3:29 PM
  • Question
    Sign in to vote
    1
    Sign in to vote

    Another solution is to use a database - to convert, select your table, format all cells as unmerged, then use goto special and select blank cells. Press =, then the up arrow key once, and press Ctrl-Enter. Then reselect your entire table, and copy pastespecial values. And that is it - you have a database and filters, pivot tables, etc. will work properly.

     

    HTH, Bernie
    Wednesday, October 5, 2011 4:01 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Top Man!! works like a charm!

    Thanks for the help guys.

    Wednesday, October 5, 2011 4:02 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Bernie, 

    Could you please elaborate on this alternative solution? I understand it was in 2011, but I have a similar issue that I am trying to solve

    Thank you,

    Beka

    • Proposed as answer by Hamid.Hamid Friday, October 3, 2014 5:40 PM
    Thursday, September 18, 2014 6:58 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello Bernie,

    I have an issue, I am preparing accounts payable sheet where in  column"A" i merge 4 rows and in column "B" these rows are unmerged..

    again next 4 rows in column "A" are merged and in Column "B" same rows are unmerged... And I have full spreadsheet like this...

    now when I apply the filter as per your first solution it does not work, kindly help me to sort out this issue coz i really need to filter data out of it.

    Thanks 

    mazhar

    Sunday, September 28, 2014 12:31 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi there,

    Bernie's alternative answer is explained in this link with screenshots,

    I tried it and it works just fine!

    http://www.extendoffice.com/documents/excel/1955-excel-filter-merged-cells.html

    Cheers
    Hamid

    Friday, October 3, 2014 5:39 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    A few years late but I can only say a BIG BIG BIG Thank You to you and Bernie for this trick... 
    Sunday, September 27, 2015 3:24 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    A year after, I have the similar problem...

    Im trying to filter a work schedule I get the explanation in link above "http://www.extendoffice.com/documents/excel/1955-excel-filter-merged-cells.html"

    all is perfect, except when I use the format paint tool I still cant format all the unmerged cells underneath to display the filter I want ( so its still only applying the filter to the first column)...So pretty much the same issue but with columns not rows.

    Example:

    - A2 to A5 Merged at row 1 (Monday).... A6 to A10 merged (Tuesday)

    -A2 contains names (AJ/Mark/DA)

    -A3 contains names (AY/Z/LA)

    and so on...

    A1 contains timing (9AM-10AM-etc)

    I want to be able to filter Merged section A2-A5 to only display the hours that (AJ) is working with no other value showing... IS that possible????

    Saturday, April 9, 2016 1:22 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi this response is one year late, but I can offer one other solution, if it helps:

    1. Copy data from merged cells to a new column, then copy down the data, in unmerged new column.

        You would need to insert a new column A, and in A4, use the formula

        =B4, and in A5, use the formula:  =IF(B5="",A4,B5);  and copy down, then filter on A.

        Then copy/paste the values.

       2. Copy/paste the format of the merged column onto the newly created column in #1 above.

      Select the merged cells and copy.  Then select the column A (new created column with unmerged cells) and paste format.

    3.  Delete the unnecessary columns (the original column that had merged cells, not the new one where the format was pasted).

    AMD

    Monday, April 24, 2017 6:07 PM