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

dourpil

Board Regular
Joined
Nov 26, 2013
Messages
101
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:

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
 
Upvote 0
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
Cell Formulas
RangeFormula
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))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.


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

Note they are array formulas so Ctrl Shift Enter
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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

<TBODY>
</TBODY>
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))),"")}

<TBODY>
</TBODY>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<TBODY>
</TBODY>



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:
Upvote 0
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-announcements/515787-forum-posting-guidelines.html#post2545970

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

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top