Creating a Filtered List

[ed2row5]

Help please Guys!!

I have a long list of cells that return different values in different cells based on some editable search criteria.

The list is some 750 cells long but at any one time only 2 or 3 of these may have data in them. I am looking to create a simple way of collating the non blank values at the top of the list. I'll try to give a truncated example.


This is how the data may appear in A10:B18

A B
10
11 F25
12 F26
13
14
15 F27
16
17 F28
18

I would like a simple read out that would give me the result below in A1:B4

A B
1 F25
2 F26
3 F27
4 F28


I hope this makes sense.

Thanks in advance.

Ed Jones

[:) Sixthsense :)]

Use Pivot Table

[ed2row5]

Thanks, I'm working on trying that but I'm struggling to format it how I want and to get it to auto update.

I'll keep at it - all suggestions are and have been gratefully received.

[ed2row5]

Use Pivot Table

Thanks, I'm working on trying that but I'm struggling to format it how I want and to get it to auto update.

I'll keep at it - all suggestions are and have been gratefully received.

[XOR LX]

Hi,

Not quite sure I understand. You say you want the returned results to go in A1:B4, but what are these results in the A column, then?

Why is F25, for example, which has a corresponding value of 11 in your source list, suddenly paired with 1 in the results?

Regards

[ed2row5]

Hi,

Not quite sure I understand. You say you want the returned results to go in A1:B4, but what are these results in the A column, then?

Why is F25, for example, which has a corresponding value of 11 in your source list, suddenly paired with 1 in the results?

Regards

My fault. My formatting is terrible. Ignore column A, the data is listed in column B - i was trying to demonstrate that I want the data listed at the top of the sheet. The numbers are row references.

[XOR LX]

Put this array formula** in B1 and copy down until you start to get blanks for the results:

=IF(ROWS($1:1)>COUNTIF($B$10:$B$18,"<>"),"",INDEX($B$10:$B$18,SMALL(IF($B$10:$B$18<>"",ROW($B$10:$B$18)-MIN(ROW($B$10:$B$18))+1),ROWS($1:1))))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

[ed2row5]

Put this array formula** in B1 and copy down until you start to get blanks for the results:

=IF(ROWS($1:1)>COUNTIF($B$10:$B$18,"<>"),"",INDEX($B$10:$B$18,SMALL(IF($B$10:$B$18<>"",ROW($B$10:$B$18)-MIN(ROW($B$10:$B$18))+1),ROWS($1:1))))

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

That seems to have worked perfectly. Thank you very very much. Many headaches saved!!

[XOR LX]

You're welcome.

[ed2row5]

You're welcome.

This now works a treat but.......

Is there a way of getting the same result but from 2 rows of data? ie examine the data in column B and C but return the results in one column only?

[XOR LX]

Could you post an example (preferably in an actual workbook this time!) including your expected results?

Regards

[ed2row5]

Could you post an example (preferably in an actual workbook this time!) including your expected results?

Regards

Attached as requested.Base.xlsx

[XOR LX]

Thanks. Again, array formula:

=IF(ROWS($1:1)>COUNTIF($A$14:$B$30,"<>"),"",INDIRECT(TEXT(SMALL(IF($A$14:$B$30<>"",ROW($A$14:$B$30)*10^6+COLUMN($A$14:$B$30)),ROWS($1:1)),"R000000C000000"),0))

Regards

[ed2row5]

Thanks. Again, array formula:

=IF(ROWS($1:1)>COUNTIF($A$14:$B$30,"<>"),"",INDIRECT(TEXT(SMALL(IF($A$14:$B$30<>"",ROW($A$14:$B$30)*10^6+COLUMN($A$14:$B$30)),ROWS($1:1)),"R000000C000000"),0))

Regards

Once again - works perfectly. Can't thank you enough for the help.

[XOR LX]

No worries. Glad I could help.