find identical record in one sheet

[Guest]

Hello,

I'd like to identify same record in one sheet, and make small report.

For instance, there are three column for one record. Year/Month/Day.

For record one, year 1999/month 12/ day 10/ New York
For record two, year 1998/month 1/day 13/ Shanghai
For record three, year 1999/month 12/day 10/New York
For record four, year 1999/month 12/day 10/ New York
.....

In the end of this sheet, i want to creat a report to say, record one and
record three and four are same, with result 'year 1999/month 12/day 10/New
York'. Pivot table doesn't really work if combination is too many.

Is there any suggestion? in VBA or formula? Thanks in advance.

 

[Don Guillett]

maybe data>filter>autofilter>will help

 

[Guest]

Thanks.

But problem of autofilter is that I have to do this job everytime manually,
and when have more records with more combination, it's difficult to select
all different years first by combined with different month/day/location, it's
impossible;

at the same time, I have to write down those lines are identical then later
put into excel again as one report.

but thanks for your reply

 

[Don Guillett]

IF?? autofilter will work for you then a macro could be created to do as
desired.

 

[Guest]

Hello, Don Guillett

I found a solution for this problem. A bit complicated and it works.

1. I gave list all possible contents of year/month/day/location, like
1999/2000/2001.. in seperate column under the name of year.

2. I gave each column a number, for instance, 1 to column year/1999; 2 to
column year/2002. ..... 100 to column month 1....;

3. when input record, I only put '1' in coloumn I wanna select.

if a record is 1999/12/01/new york. I input '1' in corresponding column.
then I time this line with defined column number, I got a number for each
line; then I added these numbers together. If added total number is equal in
different rows, it means these record are duplicated.

by using array fomula '=IF(COUNTIF(range;???)>1;"Duplicate";"")' in new
column, I could define which line(record) is repeated. (range is cells of
added total number and ??? is start cell of range).

Last I use pivot table to show only lines with 'Duplicate' record, pivot
table also automatically sort added numbers, then I could see which records
are repeated and what option has been choosed.

The only problem is, if record has too many answer, this pivot table will be
very long and looks funny.

Thanks