Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: A -possibly- easy issue I'm having trouble with - filtering, extracting data

  1. #1
    Board Regular
    Join Date
    Nov 2013
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default A -possibly- easy issue I'm having trouble with - filtering, extracting data

    Hi all, I hope you're doing great this fine day.

    I'm trying to do the following thing but don't really know where to start:
    So here is what my sheet looks like:
    (A)(B)(C)
    X | 1 | 4
    Y | 2 | 6
    X | 2 | 1
    Y | 5 | 6
    Y | 7 | 8
    X | 4 | 2
    I would like to "sort" that data and create this kind of table:
    for X
    1 | 4
    2 | 1
    4 | 2
    for Y
    2 | 6
    5 | 6
    7 | 8
    So it seems I have to play with filters (from what I've seen) but I don't want to have a range of data selected because I want to be able to add data in my first table and have it automatically copied in the corresponding sorted table.
    So I don't really want to filter my table but create another one with only the information I need. And I can "only copy filtered data to the active sheet" when I try to use the filter thingy.

    I also looked up (pun incoming) stuff about the vlookup (yeey!) function but it doesn't seem to be what I'm looking for.

    Any tip - link - stuff - cake you can give me?

    Thanks in advance!
    Simon
    Last edited by dourpil; Nov 26th, 2013 at 04:05 PM.

  2. #2
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A -possibly- easy issue I'm having trouble with - filtering, extracting data

    Hi Simon and welcome to the forum,

    One easy way to do this might be using a PivotTable. Here are the steps:

    -> Add column headers to your original table (e.g. Label | Data Column 1 | Data Column 2)
    -> Convert your table into a smart table using CTRL -> T (Working with Tables in Excel 2013, 2010 and 2007) - this step allows you to have a dynamic range so that after refresh your PivotTable will automatically reflect newly added or removed data
    -> Insert a PivotTable, specifying the table you have just created as the range
    -> Drag the Label field into the Filters area
    -> Drag the Data Column 1 field into the Row Labels area
    -> Drag the Data Column 2 field into the Row Labels area as well (below the Data column 1 field)
    -> In the PivotTable design options, turn off Subtotals and Grand Totals
    -> Change the Report Layout to Tabular Form
    -> Also in the Report Layout section choose to Repeat All Item Labels
    -> It should now be ready. Apply a filter to the Label dropdown - e.g. X or Y to see the associated sorted data

    Here is a sample workbook with the output:
    http://sdrv.ms/17WaKXc

    Here are a few useful resources for learning more about PivotTables:
    -> http://www.contextures.com/xlPivot01.html#Start
    -> Office 2010 Class #36: Excel PivotTables Pivot Tables 15 examples (Data Analysis) - YouTube
    -> Overview of PivotTable and PivotChart reports - Excel - Office.com

  3. #3
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,565
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: A -possibly- easy issue I'm having trouble with - filtering, extracting data

    Simon,

    If pivot table approach is not suitable for you and your data set is not massive then does this help?

    Excel 2007
    ABCDEFGHI
    1x14For xFor y
    2y261426
    3x212156
    4y564278
    5y785799
    6x423692
    7x57
    8y99
    9y92
    10x36

    Sheet3



    Array Formulas
    CellFormula
    E2{=IFERROR(INDEX(B$1:B$5000,SMALL(IF($A$1:$A$5000="x",($A$1:$A$5000="x")*ROW($A$1:$A$5000),50000),ROWS(E$2:E2))),"")}
    H2{=IFERROR(INDEX(B$1:B$5000,SMALL(IF($A$1:$A$5000="y",($A$1:$A$5000="y")*ROW($A$1:$A$5000),50000),ROWS(E$2:E2))),"")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself



    Copy formula in E2 & H2 across one column and down as far as needed.

    Note they are array formulas so Ctrl Shift Enter
    Tony

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

  4. #4
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A -possibly- easy issue I'm having trouble with - filtering, extracting data

    Hi Snakehips,

    Just a note that your formula approach may need an additional step to sort the data, as I think the OP wants the result sorted by the first data column.

  5. #5
    Board Regular Snakehips's Avatar
    Join Date
    May 2009
    Location
    Coventry UK
    Posts
    4,565
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: A -possibly- easy issue I'm having trouble with - filtering, extracting data

    Quote Originally Posted by circledchicken View Post
    Hi Snakehips,

    Just a note that your formula approach may need an additional step to sort the data, as I think the OP wants the result sorted by the first data column.
    Hi cc,

    I'd assumed that "sort" wasn't meant literally, rather it meant separate. That's my excuse and I'm sticking to it.
    If it needs sorting then he'll have to employ the black art of Pivot Tables.
    Pivot tables are a thing of the unknown for me, the tools of the devil!
    Tony

    AbUsing Excel 2002 to 2013
    This line will be updated just as soon as I come up with something, original, witty, profound or interesting. Please don't hold your breath!

    Please remember - we cannot see your workbook nor read your mind.
    Help us to help you, post clear detail of what you have and what you want - from the start.

    A screen shot can save a thousand words!
    Post a screen shot with one of these: Excel Jeanie, MrExcel HTML Maker
    If posting VBA code, please use Code Tags - see: here

  6. #6
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A -possibly- easy issue I'm having trouble with - filtering, extracting data

    Quote Originally Posted by Snakehips View Post
    Hi cc,

    I'd assumed that "sort" wasn't meant literally, rather it meant separate. That's my excuse and I'm sticking to it.
    If it needs sorting then he'll have to employ the black art of Pivot Tables.
    Pivot tables are a thing of the unknown for me, the tools of the devil!
    Haha! I like your excuse, you might be right.

    - PivotTables are nice (and easy I think with practice), but true they can seem strange at first until you get a chance to use them a number of times.

  7. #7
    Board Regular
    Join Date
    Nov 2013
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A -possibly- easy issue I'm having trouble with - filtering, extracting data

    Quote Originally Posted by circledchicken View Post
    Hi Simon and welcome to the forum,

    One easy way to do this might be using a PivotTable. Here are the steps:

    -> Add column headers to your original table (e.g. Label | Data Column 1 | Data Column 2)
    -> Convert your table into a smart table using CTRL -> T (Working with Tables in Excel 2013, 2010 and 2007) - this step allows you to have a dynamic range so that after refresh your PivotTable will automatically reflect newly added or removed data
    -> Insert a PivotTable, specifying the table you have just created as the range
    -> Drag the Label field into the Filters area
    -> Drag the Data Column 1 field into the Row Labels area
    -> Drag the Data Column 2 field into the Row Labels area as well (below the Data column 1 field)
    -> In the PivotTable design options, turn off Subtotals and Grand Totals
    -> Change the Report Layout to Tabular Form
    -> Also in the Report Layout section choose to Repeat All Item Labels
    -> It should now be ready. Apply a filter to the Label dropdown - e.g. X or Y to see the associated sorted data

    Here is a sample workbook with the output:
    http://sdrv.ms/17WaKXc

    Here are a few useful resources for learning more about PivotTables:
    -> http://www.contextures.com/xlPivot01.html#Start
    -> Office 2010 Class #36: Excel PivotTables Pivot Tables 15 examples (Data Analysis) - YouTube
    -> Overview of PivotTable and PivotChart reports - Excel - Office.com

    Hi and thank you for the quick answer! I've tried that on a 'clean' sheet and it seems to be working fine. However, I'm trying to adapt an existing sheet which seems to cause problems: for example, when I press CTRL+T, an automatic range of data is highlighted.
    I guess tables or formatting or whatnot can cause all sorts of trouble but do you know a quick tip to clear all that from my table and make the pivottable work?

    Right now when I try it on my existing sheet, well... I don't know really know how to explain what happens but it's not what's expected ^^ The data is sorting itself in numerical order for an unknown reason.

    Thanks again though, I'll keep trying.
    Last edited by dourpil; Dec 4th, 2013 at 10:00 AM.

  8. #8
    Board Regular
    Join Date
    Nov 2013
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A -possibly- easy issue I'm having trouble with - filtering, extracting data

    EDIT:
    So what happens with my current pivottable is that:

    - The data is sorting itself in numerical order for "column 2" rather than order of appearance in the "label" column (column 1 is all zeros atm btw)
    - If 2 -or more- entries are the same, they're not repeated

  9. #9
    Board Regular
    Join Date
    Nov 2013
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A -possibly- easy issue I'm having trouble with - filtering, extracting data

    Quote Originally Posted by Snakehips View Post
    Simon,

    If pivot table approach is not suitable for you and your data set is not massive then does this help?

    Excel 2007
    A B C D E F G H I
    1 x 1 4 For x For y
    2 y 2 6 1 4 2 6
    3 x 2 1 2 1 5 6
    4 y 5 6 4 2 7 8
    5 y 7 8 5 7 9 9
    6 x 4 2 3 6 9 2
    7 x 5 7
    8 y 9 9
    9 y 9 2
    10 x 3 6
    Sheet3

    Array Formulas
    Cell Formula
    E2 {=IFERROR(INDEX(B$1:B$5000,SMALL(IF($A$1:$A$5000="x",($A$1:$A$5000="x")*ROW($A$1:$A$5000),50000),ROWS(E$2:E2))),"")}
    H2 {=IFERROR(INDEX(B$1:B$5000,SMALL(IF($A$1:$A$5000="y",($A$1:$A$5000="y")*ROW($A$1:$A$5000),50000),ROWS(E$2:E2))),"")}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself



    Copy formula in E2 & H2 across one column and down as far as needed.

    Note they are array formulas so Ctrl Shift Enter
    Hello! I'm trying to adapt the formula to the way my sheet is organized but I can't enter the formula, I'm getting an error message :

    =IFERROR(INDEX(D$16:D$5000,SMALL(IF($B$16:$B$5000=13,($B$16:$B$5000=13)*ROW($B$16:$B$5000),50000),ROWS(O$16:O16))),"")
    I removed the " " you put for X and Y since my 'filtering' thingies are numbers - IDK if that was a good idea.

    The error however is highlighted at the bold place. Any idea why? It looks like my Excel wants some ";" instead of ","
    Last edited by dourpil; Dec 4th, 2013 at 10:21 AM.

  10. #10
    Board Regular
    Join Date
    Aug 2011
    Posts
    2,932
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: A -possibly- easy issue I'm having trouble with - filtering, extracting data

    Hi,

    Sorry its difficult to tell from the information you've posted what the problem is with the PivotTable on your actual data.
    As long as the actual data is similar to the sample posted the setup should be the same.

    Perhaps post a more representative sample of your actual data using the method described here:
    http://www.mrexcel.com/forum/board-a...ml#post2545970

    Or as a last resort if you also want to include your PivotTable setup, post an anonymised workbook on Skydrive for example.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •