Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to hide duplicate records but show them once
I'm trying to weed out duplicate records but I need to show them once. I
can't figure out how to do this without alot of formatting and deleting. Can anyone help? |
#2
|
|||
|
|||
See Chip Pearson's website for advice on dealing with dupes
http://www.cpearson.com/excel/duplicat.htm "Lisa" wrote: I'm trying to weed out duplicate records but I need to show them once. I can't figure out how to do this without alot of formatting and deleting. Can anyone help? |
#3
|
|||
|
|||
if the data is in one column, highlight the column, the select Data - Filter - Advanced Filter. Make sure the range is correct and select the unique records only field. This will filter the list in place with only one occurence of the records in the list. -- barrfly Excel User - Energy markets ------------------------------------------------------------------------ barrfly's Profile: http://www.excelforum.com/member.php...fo&userid=4141 View this thread: http://www.excelforum.com/showthread...hreadid=391880 |
#4
|
|||
|
|||
Thanks Duke but this only tells me how to tag or highlight and I just want to
see each record once if it's listed once or 15 times I still only want it to show once and can't figure this out. "Duke Carey" wrote: See Chip Pearson's website for advice on dealing with dupes http://www.cpearson.com/excel/duplicat.htm "Lisa" wrote: I'm trying to weed out duplicate records but I need to show them once. I can't figure out how to do this without alot of formatting and deleting. Can anyone help? |
#5
|
|||
|
|||
You can use Conditional Formatting to hide the duplicates. There are
instructions he http://www.contextures.com/xlCondFor...html#Duplicate Lisa wrote: I'm trying to weed out duplicate records but I need to show them once. I can't figure out how to do this without alot of formatting and deleting. Can anyone help? -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#6
|
|||
|
|||
Thanks for the suggestion, Unfortunately this will not show the item if it is
listed twice. It will only show those that are listed once. "barrfly" wrote: if the data is in one column, highlight the column, the select Data - Filter - Advanced Filter. Make sure the range is correct and select the unique records only field. This will filter the list in place with only one occurence of the records in the list. -- barrfly Excel User - Energy markets ------------------------------------------------------------------------ barrfly's Profile: http://www.excelforum.com/member.php...fo&userid=4141 View this thread: http://www.excelforum.com/showthread...hreadid=391880 |
#7
|
|||
|
|||
This shows each unique record only once. If a row has an occurence of the same value 1, 2, 5 or 300 times, the resulting list will only show each unique value only once. If it is not working for you, it could be one of two things that I can think of off the top of my head. 1. is that the values are number values and are rounded to look the same, but have different decimal tailing decimal values. 2. the values you are sorting are formulas and not "hard-coded" values. The advanced filter looks at the cell contents (ie. hard coded values or formulas) and determines uniqueness accordingly. What type of information are you sorting? numbers, names? could there be non-visible trailing spaces that you can not see? -- barrfly Excel User - Energy markets ------------------------------------------------------------------------ barrfly's Profile: http://www.excelforum.com/member.php...fo&userid=4141 View this thread: http://www.excelforum.com/showthread...hreadid=391880 |
#8
|
|||
|
|||
A simple way to do this with formulas would be to do the following. 1. sort the column that has the values you are evaluating. 2. in an adjacent column, set the first cell (even with the top value in your list) equal to 1. so if your values are in column A, put the "1" in column B. 3. below the "1" cell, (in this case B2) enter the formula =IF(A2=A1,"x",1) 4. this will give you a list with lots of x and 1s. apply an autofilter to this list and filter out only the 1s Let me know how this works. -- barrfly Excel User - Energy markets ------------------------------------------------------------------------ barrfly's Profile: http://www.excelforum.com/member.php...fo&userid=4141 View this thread: http://www.excelforum.com/showthread...hreadid=391880 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting specific records | Excel Discussion (Misc queries) | |||
deleting duplicate records in a mail merge | Excel Discussion (Misc queries) | |||
pivot table - hide details but show subtotal for calculated field | Excel Discussion (Misc queries) | |||
hide column but show chart | Charts and Charting in Excel | |||
Duplicate records in Excel | Excel Discussion (Misc queries) |