Apologies - i've tried finding the answer to this in an already posted
question, but i'm struggling.

I have a code in column A and a date in column G. There are different
combinations of both, plus some codes without dates (blanks), but also
duplicates.

I want to copy these to a different sheet but with only one combination of
each code and date, not including the blanks.

Any ideas?? Much appreciated.

This bit isn't urgent but would be a nice touch!

Once here, I then want to have the top ten (newest date) of each code and
date to go elsewhere. I think this might be using =LARGE?

Cheers

Adam, this wont copy but it will display items on Sheet2.

"GoBow777" wrote:


Adam;650795 Wrote:
Apologies - i've tried finding the answer to this in an already posted
question, but i'm struggling.

I have a code in column A and a date in column G. There are different

combinations of both, plus some codes without dates (blanks), but also

duplicates.

I want to copy these to a different sheet but with only one combination
of
each code and date, not including the blanks.

Any ideas?? Much appreciated.

This bit isn't urgent but would be a nice touch!

Once here, I then want to have the top ten (newest date) of each code
and
date to go elsewhere. I think this might be using =LARGE?

Cheers

Adam, this wont copy but it will display items on Sheet2.


+-------------------------------------------------------------------+
|Filename: Adam.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=108|
+-------------------------------------------------------------------+



--
GoBow777

Hi thanks very much for your help -

one problem

I need to see duplicates of the date still if they have different codes.

Sorry, should have put that in my first question.







"GoBow777" wrote:


Adam;650795 Wrote:
Apologies - i've tried finding the answer to this in an already posted
question, but i'm struggling.

I have a code in column A and a date in column G. There are different

combinations of both, plus some codes without dates (blanks), but also

duplicates.

I want to copy these to a different sheet but with only one combination
of
each code and date, not including the blanks.

Any ideas?? Much appreciated.

This bit isn't urgent but would be a nice touch!

Once here, I then want to have the top ten (newest date) of each code
and
date to go elsewhere. I think this might be using =LARGE?

Cheers

Adam, this wont copy but it will display items on Sheet2.


+-------------------------------------------------------------------+
|Filename: Adam.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=108|
+-------------------------------------------------------------------+



--
GoBow777

Adam:

I’m still a bit confused when you say, “I need to see duplicates of the date still if they have different codes”. I’m guessing you mean to say, “I need to see duplicates of the date regardless of the different codes”? I hope I’ve got that right. Populate column AA with this formula.

I've just read what I had written and it didn't make sense - very sorry!

It might be easier if i just explain my scenerio.

The code represents a course that I run.
The date represents the date that it ran.
The spreadsheet is a complete list of all the people I have
attending/attended these courses.
There is usually 60+ people attending each course.
More than one course can occur on one day.

What I would like is (if it is possible) to have a list on a seperate sheet
of the most recent dates with the course name next to it. This would mean
there maybe duplicates of dates when there is more that one course on that
day. Then next to them dates and course names I would be able to SUM results
of that day in other boxes using the rest of the data on the original
spreadsheet.

Your first coding was great - but it did not reflect when more than one
course occured on one date.

Many thanks for your time on this - very much appreciated!


"GoBow777" wrote:


Adam;651370 Wrote:
Hi thanks very much for your help -

one problem

I need to see duplicates of the date still if they have different
codes.

Sorry, should have put that in my first question.

Adam:

Im still a bit confused when you say, €œI need to see duplicates of the
date still if they have different codes€�. Im guessing you mean to say,
€œI need to see duplicates of the date regardless of the different
codes€�? I hope Ive got that right. Populate column AA with this
formula.

Code:
--------------------
=IF(OR(A2="",G2=""),"",IF(COUNTIF($G:$G,G2)1,RANK ($G2,$G:$G,0)+ROW()/100000,""))
--------------------


+-------------------------------------------------------------------+
+-------------------------------------------------------------------+



--
GoBow777

Adam, How about this.

The first one was closer to be honest! It just needed to be tweaked to
include when a date reoccured for a different course.

The original put all the codes under one date - so for example when I had
three courses on the 10/Apr/08, the list only showed up that I had the first
one of them courses and the date - instead of the date 3 times and the
different courses next to it.

Don't worry if it can't be done - it's quite fussy what i'm looking for.

Cheers again

"GoBow777" wrote:


Adam;652212 Wrote:
I've just read what I had written and it didn't make sense - very
sorry!

It might be easier if i just explain my scenerio.

The code represents a course that I run.
The date represents the date that it ran.
The spreadsheet is a complete list of all the people I have
attending/attended these courses.
There is usually 60+ people attending each course.
More than one course can occur on one day.

What I would like is (if it is possible) to have a list on a seperate
sheet
of the most recent dates with the course name next to it. This would
mean
there maybe duplicates of dates when there is more that one course on
that
day. Then next to them dates and course names I would be able to SUM
results of that day in other boxes using the rest of the data on the
original
spreadsheet.

Your first coding was great - but it did not reflect when more than one

course occured on one date.

Many thanks for your time on this - very much appreciated!

Adam, How about this.


+-------------------------------------------------------------------+
|Filename: Adam2.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=111|
+-------------------------------------------------------------------+



--
GoBow777

Adam if this isn’t it, how about you sending me a sample .zip file of what it is that you want to see, I’m certain this can be done. You also might consider using a Pivot Table.
http://www.cpearson.com/excel/pivots.htm

I gave you a reply in your other post. Is this not good enough for
you?

Regards

On Apr 15, 7:48 pm, Adam wrote:
The first one was closer to be honest! It just needed to be tweaked to
include when a date reoccured for a different course.

The original put all the codes under one date - so for example when I had
three courses on the 10/Apr/08, the list only showed up that I had the first
one of them courses and the date - instead of the date 3 times and the
different courses next to it.

Don't worry if it can't be done - it's quite fussy what i'm looking for.

Cheers again

"GoBow777" wrote:

Adam;652212 Wrote:
I've just read what I had written and it didn't make sense - very
sorry!

It might be easier if i just explain my scenerio.

The code represents a course that I run.
The date represents the date that it ran.
The spreadsheet is a complete list of all the people I have
attending/attended these courses.
There is usually 60+ people attending each course.
More than one course can occur on one day.

What I would like is (if it is possible) to have a list on a seperate
sheet
of the most recent dates with the course name next to it. This would
mean
there maybe duplicates of dates when there is more that one course on
that
day. Then next to them dates and course names I would be able to SUM
results of that day in other boxes using the rest of the data on the
original
spreadsheet.

Your first coding was great - but it did not reflect when more than one

course occured on one date.

Many thanks for your time on this - very much appreciated!

Adam, How about this.

+-------------------------------------------------------------------+
|Filename: Adam2.zip |
|Download:http://www.excelbanter.com/attachment.php?attachmentid=111|
+-------------------------------------------------------------------+

--
GoBow777

Hi mate

Thanks for all the effort - much appreciated - my company finally stumped up
the cash to put me through a decent Excel course so i should be able to work
with what you've given me

Cheers


"GoBow777" wrote:


Adam;652908 Wrote:
The first one was closer to be honest! It just needed to be tweaked to

include when a date reoccured for a different course.

The original put all the codes under one date - so for example when I
had
three courses on the 10/Apr/08, the list only showed up that I had the
first
one of them courses and the date - instead of the date 3 times and the

different courses next to it.

Don't worry if it can't be done - it's quite fussy what i'm looking
for.

Cheers again

Adam if this isnt it, how about you sending me a sample .zip file of
what it is that you want to see, Im certain this can be done. You
also might consider using a Pivot Table.
http://www.cpearson.com/excel/pivots.htm


+-------------------------------------------------------------------+
|Filename: Adam3.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=112|
+-------------------------------------------------------------------+



--
GoBow777