Matching columns and removing duplicates

  • Thread starter Thread starter Adam
  • Start date Start date
A

Adam

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;650795 said:
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 differen

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

duplicates.

I want to copy these to a different sheet but with only one combinatio
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 cod
and
date to go elsewhere. I think this might be using =LARGE?

Cheers
Click to expand...

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

+-------------------------------------------------------------------
|Filename: Adam.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=108
+-------------------------------------------------------------------
 
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;651370 said:
Hi thanks very much for your help -

one problem

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

Sorry, should have put that in my first question.
Click to expand...

Adam:

I’m still a bit confused when you say, “I need to see duplicates of th
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 differen
codes”? I hope I’ve got that right. Populate column AA with thi
formula.

Code
 
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;652212 said:
I've just read what I had written and it didn't make sense - ver
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 seperat
sheet
of the most recent dates with the course name next to it. This woul
mean
there maybe duplicates of dates when there is more that one course o
that
day. Then next to them dates and course names I would be able to SU
results of that day in other boxes using the rest of the data on th
original
spreadsheet.

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

course occured on one date.

Many thanks for your time on this - very much appreciated!
Click to expand...

Adam, How about this

+-------------------------------------------------------------------
|Filename: Adam2.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=111
+-------------------------------------------------------------------
 
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;652908 said:
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
Click to expand...

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


+-------------------------------------------------------------------+
|Filename: Adam3.zip |
|Download: http://www.excelbanter.com/attachment.php?attachmentid=112|
+-------------------------------------------------------------------+
 
I gave you a reply in your other post. Is this not good enough for
you?

Regards

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, How about this.
Click to expand...
+-------------------------------------------------------------------+
|Filename: Adam2.zip |
|Download:http://www.excelbanter.com/attachment.php?attachmentid=111|
+-------------------------------------------------------------------+
Click to expand...
Click to expand...
 
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
 
Back
Top