Pull unique data within a date range that matches criteria...confused.

Hello all, I'm a new visitor to these forums, I'm here because I'm stuck trying to solve an excel problem.

I've created a workbook that functions as a log to record issues that I've had with companies I work with. Generally speaking it is 3 tabs:

1. The data page, which I call the "Issue log".
2. A lists page, for validation lists "Validation Lists".
3. A issue summary page, formatted for the external companies to see what I've logged, called "Company Summary".

My concept was to enter data into the log, and then I've set up my company summary page so that outside of the print area I select the company I want the summary to be for, and the date range (starting date & ending date) for the summary. Then (theoretically) the summary would auto-populate with each of the individual issues that I've entered into the log that match those criteria (company & date range).

My problems are:

• I can get it to pull the reference numbers for the right company from the first occurance of the company's name to the last, but if an issue is in the middle of that data out of order, it includes that issue as well.
• When I try to sort the data to fix that problem, it changes the reference number (because it's counted based on order of entry), and in some cases the other company's already have that reference number.

I've created my first column in the log as a concatenate that is formatted as "2AA1", where '2' is the total sequencial count for the log, "AA" would be a 2 letter abbreviation for the company in question, and "1" is the issue count just for that company. The log essentially looks like this:

1AA1 - 6/1/2011 - Issue notes
2BB1 - 6/2/2011 - Issue notes
3AA2 - 6/15/2011 - Issue notes
4CC1 - 6/10/2011 - Issue notes

So basically on the next tab I would select something like:
Builder - *AA*
Start Date - *6/1/2011*
End Date - *6/31/2011*

And I'd like it to pull the specific reference numbers in that range that match my criteria so I can vlookup the other data I need to populate the rest of the form.

I've tried SUMPRODUCT, VLOOKUP, arrays, Pivot Tables, INDEX/MATCH, SUMIF, COUNTIF, etc. I have literally spent the last 12 hours consecutively trying to figure this out on my own, and I just can't make it work. I know one of those methods (or more) can work, I just don't have the skills to do it.

Does anyone have any advice for me? I really appreciate it.

Thanks so much...
-LawC

Originally Posted by MarvinP

Hi LawC and welcome to the forum,

Perhaps Advanced Filters will pull what you need?
http://www.contextures.com/xladvfilter01.html is a good link and shows how to use date filters too.

Thanks MarvinP, I will look into that - I appreciate the tip.

Originally Posted by Azam Ali

it is better to upload a sample workbook with sample data and your desired results

In order to attach a file Click the Go Advanced button, and click on the Manage Attachments button

Great advice Azam Ali, thanks. I've created a simplified version of what I have so far and uploaded it. Again my big problem is when the items end up out of order, they don't get pulled correctly since essentially I'm looking for the 1st reference # and then pulling the counted quantity that took place "in that date range". But it's not a true date range, it's the date range in my log.

Maybe some type of auto sort or something could help? I do use a user form to input the data, maybe there's a way to have the user form put the data in the proper order on the log based on date? Any advice would be great, thanks so much!

Originally Posted by MarvinP

Hi LawC,

I like what you have done. It looks to me you simply need an autofilter on your data on the Issue Log Tab. Sort the data by Issue Date, old to new, and things seem to work correctly. If that isn't the case, tell me what doesn't still work.

See http://www.contextures.com/xlautofilter01.html for autofilters and tables info.

Thanks for taking the time to check it out, MarvinP.

I just played around with it a bit more, I still have a problem. Here's what happens when I sort (I changed some of the dates for ease of seeing what I'm talking about):

Sorted Data:
1AC1 - AUG 11, 2011
2AB1 - JUN 22, 2011
3FC1 - SEP 11, 2010
4AC2 - SEP 9, 2011
5AC3 - JAN 1, 2008
6FC2 - SEP 11, 2011

Original Data:
1AC1 - JAN 1, 2008
2FC1 - SEP 11, 2010
3AB1 - JUN 22, 2011
4AC2 - AUG 11, 2011
5AC3 - SEP 9, 2011
6FC2 - SEP 11, 2011

The problem is, the way I use my reference numbers, they are changing when I sort. Notice how they still start out: 1, 2, 3, 4, 5, 6? I give those numbers to customers as a reference to look up issues in the future, it needs to somehow stay relevant to the specific issue (almost like a case number). Additionally I currently use that data as my VLOOKUP for the summary sheet.

Thanks again for your efforts!