| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How eliminate repeating data?
I have static data in a spreadsheet column that repeats so I only want
to show the first occurrence of the repeating data in the column. I'm looking for a way to do this (macro, formula, conditional formatting). Example: Column COL1 (below) has repeating data in it. I want the spreadsheet to show just the first BLVD#4 and the first BLVD#8 with blanks/spaces for teh repeating data. ================================ COL1 BLVD #4 BLVD #4 BLVD #4 BLVD #8 BLVD #8 BLVD #8 BLVD #8 R. MELENDEZ Should instead be like this: COL1 BLVD #4 BLVD #8 R.MELENDEZ |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How eliminate repeating data?
What I'd do - and this may or may not work for you - is have this in
conditional formatting for cell A2: =A2=A1 and then set the format to the font colour the same as the background colour. You can then copy this format to the rest of the list (addresses will adjust) Sian "Wally" wrote: I have static data in a spreadsheet column that repeats so I only want to show the first occurrence of the repeating data in the column. I'm looking for a way to do this (macro, formula, conditional formatting). Example: Column COL1 (below) has repeating data in it. I want the spreadsheet to show just the first BLVD#4 and the first BLVD#8 with blanks/spaces for teh repeating data. ================================ COL1 BLVD #4 BLVD #4 BLVD #4 BLVD #8 BLVD #8 BLVD #8 BLVD #8 R. MELENDEZ Should instead be like this: COL1 BLVD #4 BLVD #8 R.MELENDEZ |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How eliminate repeating data?
Method #1:
Assuming your data is in column A beginning in row 1, enter the following in B1 and copy down: =IF(COUNTIF($A$1:$A1,A1)=1,A1,"" That will give you the answer shown in your question with blank rows for duplicates. Method #2: Extract the unique entries to a separate part of the spreadsheet with Data - Filter - Advanced Filter and specify unique records only. You'll need to have a column header row for the field, criteria, and output column. That will give you a list of unique values in column A with no blank rows. "Wally" wrote: I have static data in a spreadsheet column that repeats so I only want to show the first occurrence of the repeating data in the column. I'm looking for a way to do this (macro, formula, conditional formatting). Example: Column COL1 (below) has repeating data in it. I want the spreadsheet to show just the first BLVD#4 and the first BLVD#8 with blanks/spaces for teh repeating data. ================================ COL1 BLVD #4 BLVD #4 BLVD #4 BLVD #8 BLVD #8 BLVD #8 BLVD #8 R. MELENDEZ Should instead be like this: COL1 BLVD #4 BLVD #8 R.MELENDEZ |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How eliminate repeating data?
Corrects formula for typo:
=IF(COUNTIF($A$1:$A1,A1)=1,A1,"") Sorry. "MrAcquire" wrote: Method #1: Assuming your data is in column A beginning in row 1, enter the following in B1 and copy down: =IF(COUNTIF($A$1:$A1,A1)=1,A1,"" That will give you the answer shown in your question with blank rows for duplicates. Method #2: Extract the unique entries to a separate part of the spreadsheet with Data - Filter - Advanced Filter and specify unique records only. You'll need to have a column header row for the field, criteria, and output column. That will give you a list of unique values in column A with no blank rows. "Wally" wrote: I have static data in a spreadsheet column that repeats so I only want to show the first occurrence of the repeating data in the column. I'm looking for a way to do this (macro, formula, conditional formatting). Example: Column COL1 (below) has repeating data in it. I want the spreadsheet to show just the first BLVD#4 and the first BLVD#8 with blanks/spaces for teh repeating data. ================================ COL1 BLVD #4 BLVD #4 BLVD #4 BLVD #8 BLVD #8 BLVD #8 BLVD #8 R. MELENDEZ Should instead be like this: COL1 BLVD #4 BLVD #8 R.MELENDEZ |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How eliminate repeating data?
Thank you!
Walter "MrAcquire" wrote in message ... Corrects formula for typo: =IF(COUNTIF($A$1:$A1,A1)=1,A1,"") Sorry. "MrAcquire" wrote: Method #1: Assuming your data is in column A beginning in row 1, enter the following in B1 and copy down: =IF(COUNTIF($A$1:$A1,A1)=1,A1,"" That will give you the answer shown in your question with blank rows for duplicates. Method #2: Extract the unique entries to a separate part of the spreadsheet with Data - Filter - Advanced Filter and specify unique records only. You'll need to have a column header row for the field, criteria, and output column. That will give you a list of unique values in column A with no blank rows. "Wally" wrote: I have static data in a spreadsheet column that repeats so I only want to show the first occurrence of the repeating data in the column. I'm looking for a way to do this (macro, formula, conditional formatting). Example: Column COL1 (below) has repeating data in it. I want the spreadsheet to show just the first BLVD#4 and the first BLVD#8 with blanks/spaces for teh repeating data. ================================ COL1 BLVD #4 BLVD #4 BLVD #4 BLVD #8 BLVD #8 BLVD #8 BLVD #8 R. MELENDEZ Should instead be like this: COL1 BLVD #4 BLVD #8 R.MELENDEZ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I eliminate rows that contain the same data as the row ab. | Excel Discussion (Misc queries) | |||
| eliminate data entry of counts by state | New Users to Excel | |||
| How do I eliminate a legend title for one of my data sets? | Charts and Charting in Excel | |||
| Eliminate empty cells in data range | Excel Discussion (Misc queries) | |||
| macro to eliminate repeating account numbers | New Users to Excel | |||
Linear Mode
