Complicated Sort in Excel 2007 2

[VBAPrincess]

Perhaps this isn't complicated, but I certainly can't figure out how to make it happen. The data I have looks like the example below:

NAME LINE POL# EXP DATE
Customer A Auto XYZ123 2/29/2012
Customer A Prop ABC987 8/31/2012
Customer B Auto CTE456 3/31/2012
Customer B Prop 8Z9TE3 6/30/2012

I need to sort by expiration date BUT only for the property lines. However, I want to keep the auto and property rows for each customer together. So, it should appear like this:

NAME LINE POL# EXP DATE
Customer B Auto CTE456 3/31/2012
Customer B Prop 8Z9TE3 6/30/2012
Customer A Auto XYZ123 2/29/2012
Customer A Prop ABC987 8/31/2012

Also, if there is a way to view customer data (auto and prop) for customers who have a property policy expiring in March (and this date filter will need to be changed), that would be great too.

Ideally we need to see information for both policies by customer, but we're only concerned with filtering by dates for the property policies.

This needs to remain in Excel because many of the rows are color coded and the coloring needs to stay with the data.

Thanks in advance for any assistance!!

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[SkipVought]

hi,

Using Named Ranges based on your headings, in column E
[tt]
E2: =SUMPRODUCT((NAME=A2)*(LINE="Prop")*(EXP_DATE))
[/tt]
and COPY down

Sort on D, A, B

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[N1GHTEYES]

Diana: does your data have to remain in that format?

In general for future manipulation purposes, it would be better to organise it in 5 columns with one row per customer - Like this

NAME        Auto-POL# Auto-EXP DATE Prop-POL#  Prop-EXP DATE
Customer B  CTE456 3/31/2012   8Z9TE3 6/30/2012
Customer A  XYZ123 2/29/2012   ABC987  8/31/2012

Having your data set up as above would certainly make it a lot easier to keep customer's records together (they are all on one row) and you could sort by any parameter you wanted to.

Tony

 

[N1GHTEYES]

Skip: I don't get either your formula or your instructions. How is the formula supposed to work. I tried it with Diana's data and got Value!. Also, if it did work, what are you suggesting sorting on? D THEN A THEN B or D,A,B simultaneously with D being prime, A next, B last. When does E come into the sort?

Tony

 

[N1GHTEYES]

OK, now that I've made sure the dates were dates not text, I don't get value!. Now the column E cells all return 0.

I still don't get it I'm afraid.

Tony

 

[N1GHTEYES]

OK, now I get it. It wasn't working because there was some extraneous whitespace in my cells.

To understand what it was doing I translated it into array form...

E2: = {=SUM(IF(NAME=A2,IF(LINE="Prop",(EXP_DATE))))}

...which also works.

But wrt my other question, you then just sort on E don't you?

Tony

 

[SkipVought]

Answers to Questions:

1) This assumes that the sample data is like the real data, in that there are ONLY TWO ROWS PER NAME; Prop & Auto

2) The formula returns the Prop EXP_DATE for the given NAME
[tt]
=SUMPRODUCT((NAME=A2)*(LINE="Prop")*(EXP_DATE))
[/tt]
[tt]
NAME LINE POL# EXP DATE KEY
Customer B Prop 8Z9TE3 6/30/2012 6/30/2012
Customer B Auto CTE456 3/31/2012 6/30/2012
Customer A Prop ABC987 8/31/2012 8/31/2012
Customer A Auto XYZ123 2/29/2012 8/31/2012
[/tt]

3) sort on KEY, NAME, LINE Desc

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[VBAPrincess]

Tony: I wish the data came in another format. The data comes from an outside source and we get what we get -- then throw a fit trying to work with it. Before I posted I was trying to figure out how to transform it to look like what you suggested, but I don't know how to write transform queries well enough and I don't know that it would work. If there weren't ~1000 rows I'd manually manipulate it.

I have tried using both formulas presented above and always get VALUE! or 0. I've checked to be sure extra white space is removed in the three columns (name, line, exp date). I know the dates are not text because I already had to convert that before starting anything yesterday. (yet another reason this file gives us fits -- the dates are text with 12:00:00 AM too)

I'm attaching a link to a small sample file with only about 8 rows of data. I appreciate the help guys!



Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[SkipVought]

Answers to Questions:

Oh, and the LAST answer is that I can't 'count' in alpha


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[VBAPrincess]

SKIP: Forgive my stupidity! I just figured it out! I was changing every part of the formula to match my data except A2. Duh! Now it works. Thank you so much!!



Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[MazeWorX]

ahhh if there was ever a reason for a db ...

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[VBAPrincess]

MazeWorX: Don't I know it! If we didn't need the color coding because work has already been done on these, I would have put it in a db LONG ago.

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[SkipVought]

Didn't know they have okie princesses. ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}