Filter in place on a range of columns - Help!

[JonWhite123]

Hi All,

Long time reader, first time poster...

I have a report which has 14 columns. In one column there is a list of system names (often duplicates). What I need to do is show only one system name, depending on what data is in the other columns. Here's where it gets tricky.

This is a simple example of the problem:

A B C D
Word 4 12/12/13 Apples
Excel 5 1/12/13 Oranges
Word 3 31/6/13 Oranges
Excel 4 12/3/13 Apples

What I'd like it to do is take the highest value in column B, the earliest date in column D and the nicest fruit in column D (where Apples are nicer that Oranges!). So the filtered list in the example would look like this:

A B C D
Word 4 31/6/13 Apples
Excel 5 12/3/13 Apples

I hope this makes sense. I've looked at using sort/advanced and filter in place, but cannot see the best way to approach it. I'm using VBA to do a lot of stuff in this workbook, so perhaps there is an answer there? I'm using Excel 2007. If anyone can help, or offer any help I would greatly appreciate it!

Thanks all.

 

[JonWhite123]

Sorry, I meant "earliest date in column C".

 

[Peter_SSs]

Jon

I'm not sure that I can solve your problem, but I do have a couple of comments

Long time reader, first time poster...

1. Not quite first time poster - did you forget about this one a few years ago?

2. You seem to have invented a new date in 31/6/13 so I'm assuming 30/6/13

3. You will not be able to "filter in place" to to this. Filtering in Excel hides/shows whole rows. Since your desired results are made up from bits on different rows, filtering will not be able to achieve it.

4. Here are some manual step that you can investigate. If it's what you want you may be able to convert to vba, or ask for help here to do so.

My comments refer to may data and layout below, assuming headings with the original data in columns A:D. My steps were ..

a) Use Advanced Filter on A1:A5, with Copy to another location (F1) and Unique records only. That gives me the list of unique items from column A.

b) Added my fruit in order with "nicest" first in column K

c) Added the formulas in G2, H2 & I2. These are array formulas so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formulas can then be copied down.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
1	A	B	C	D		A	B	C	D		Fruit Order
2	Word	4	12/12/13	Apples		Word	4	30/06/13	Apples		Apples
3	Excel	5	1/12/13	Oranges		Excel	5	12/03/13	Apples		Oranges
4	Word	3	30/06/13	Oranges
5	Excel	4	12/03/13	Apples
6
Creat List

 

[JonWhite123]

Hi Peter,

1. Yes, it looks like I did! Doesn't time fly eh?
2. I like June, another day seems a perfect idea to me....
3. Your answer is fantastic, thank you. I'll use what you've put and make it work with what I'm doing. At least I'll be doing it the "right" way now, rather than get even more confused. What a great site, thanks again.

 

[Peter_SSs]

Hi Peter,

1. Yes, it looks like I did! Doesn't time fly eh?
2. I like June, another day seems a perfect idea to me....
3. Your answer is fantastic, thank you. I'll use what you've put and make it work with what I'm doing. At least I'll be doing it the "right" way now, rather than get even more confused. What a great site, thanks again.

Yes, end of June is a busy time where I work so having an extra day to get it all done would be a good idea - so long as I can have an extra day OFF somewhere else.

Glad to have helped. Don't leave it so long before you come back again to post.