Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find identical record in one sheet
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
find identical record in one sheet
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
find identical record in one sheet
IF?? autofilter will work for you then a macro could be created to do as desired. -- Don Guillett SalesAid Software "jiang" wrote in message ... 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" wrote: maybe datafilterautofilterwill help -- Don Guillett SalesAid Software "jiang" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
find identical record in one sheet
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 "Don Guillett" wrote: IF?? autofilter will work for you then a macro could be created to do as desired. -- Don Guillett SalesAid Software "jiang" wrote in message ... 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" wrote: maybe datafilterautofilterwill help -- Don Guillett SalesAid Software "jiang" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find duplicate, but only if an adjacent cell is identical | Excel Worksheet Functions | |||
find identical names in list | Excel Programming | |||
Need to find two consecutive identical cells (in a column). | Excel Programming | |||
I want cell entry to be identical on another sheet | Excel Worksheet Functions | |||
Inexplicable difference in row hiding speed - identical code, identical machines! | Excel Programming |