|
Find Duplicate in a Row
Hello All:
Here is my dilemma…..I am using Excel 2007. I have a spreadsheet with 17,3322 row of data that spans to Column W (A2:W173322) excluding the header. I am trying to do conditional formatting to bring to light the duplicate values that are identical across the row and highlight them. The problem is that the built in function is highlighting duplicates whenever it finds it. I need to somehow only highlight if the entire row is duplicate and not only portion. A2:W2 is the entire row which represents a data set. I tried using =COUNTIF ($A$2:$W $173322,A2)1 but it is not quit working. Any Ideas……. Ardy |
Find Duplicate in a Row
Let's see if I understand what you want....
If *every* cell in the row contains the same value then highlight that row. If *every* cell in that row does not contain the same value then do nothing. If that's what you want... Assuming there are no empty/blank cells... Try: =COUNTIF($A2:$W2,$A2)=COLUMNS($A2:$W2) -- Biff Microsoft Excel MVP "Ardy" wrote in message ... Hello All: Here is my dilemma…..I am using Excel 2007. I have a spreadsheet with 17,3322 row of data that spans to Column W (A2:W173322) excluding the header. I am trying to do conditional formatting to bring to light the duplicate values that are identical across the row and highlight them. The problem is that the built in function is highlighting duplicates whenever it finds it. I need to somehow only highlight if the entire row is duplicate and not only portion. A2:W2 is the entire row which represents a data set. I tried using =COUNTIF ($A$2:$W $173322,A2)1 but it is not quit working. Any Ideas……. Ardy |
Find Duplicate in a Row
On May 22, 12:16*pm, "T. Valko" wrote:
Let's see if I understand what you want.... If *every* cell in the row contains the same value then highlight that row.. If *every* cell in that row does not contain the same value then do nothing. If that's what you want... Assuming there are no empty/blank cells... Try: =COUNTIF($A2:$W2,$A2)=COLUMNS($A2:$W2) -- Biff Microsoft Excel MVP "Ardy" wrote in message ... Hello All: Here is my dilemma…..I am using Excel 2007. *I have a spreadsheet with 17,3322 row of data that spans to Column W (A2:W173322) excluding the header. *I am trying to do conditional formatting to bring to light the duplicate values that are identical across the row and highlight them. *The problem is that the built in function is highlighting duplicates whenever it finds it. *I need to somehow only highlight if the entire row is duplicate and not only portion. A2:W2 is the entire row which represents a data set. *I tried using =COUNTIF ($A$2:$W $173322,A2)1 but it is not quit working. Any Ideas……. Ardy Yes Partly If *every* cell in the row contains the same value then highlight that row Here is an Example A B C D E 1 al bb cc dd ee 2 zz gg mm ll ff 3 al bb cc dd ee 4 ll mm ff pp ll 5 al bb cc dd ee So the rows that should get highlighted are 1, 3 and 5 becuse the rows contain the same info. Ardy |
Find Duplicate in a Row
Are you looking for the *specific* entries:
al...bb...cc...dd...ee Do they have to be in that *specific* order? Would this be a match? cc...bb...al...ee...dd All the values are the same but they're in a different order. -- Biff Microsoft Excel MVP "Ardy" wrote in message ... On May 22, 12:16 pm, "T. Valko" wrote: Let's see if I understand what you want.... If *every* cell in the row contains the same value then highlight that row. If *every* cell in that row does not contain the same value then do nothing. If that's what you want... Assuming there are no empty/blank cells... Try: =COUNTIF($A2:$W2,$A2)=COLUMNS($A2:$W2) -- Biff Microsoft Excel MVP "Ardy" wrote in message ... Hello All: Here is my dilemma…..I am using Excel 2007. I have a spreadsheet with 17,3322 row of data that spans to Column W (A2:W173322) excluding the header. I am trying to do conditional formatting to bring to light the duplicate values that are identical across the row and highlight them. The problem is that the built in function is highlighting duplicates whenever it finds it. I need to somehow only highlight if the entire row is duplicate and not only portion. A2:W2 is the entire row which represents a data set. I tried using =COUNTIF ($A$2:$W $173322,A2)1 but it is not quit working. Any Ideas……. Ardy Yes Partly If *every* cell in the row contains the same value then highlight that row Here is an Example A B C D E 1 al bb cc dd ee 2 zz gg mm ll ff 3 al bb cc dd ee 4 ll mm ff pp ll 5 al bb cc dd ee So the rows that should get highlighted are 1, 3 and 5 becuse the rows contain the same info. Ardy |
Find Duplicate in a Row
On May 22, 2:35*pm, "T. Valko" wrote:
Are you looking for the *specific* entries: al...bb...cc...dd...ee Do they have to be in that *specific* order? Would this be a match? cc...bb...al...ee...dd All the values are the same but they're in a different order. -- Biff Microsoft Excel MVP "Ardy" wrote in message ... On May 22, 12:16 pm, "T. Valko" wrote: Let's see if I understand what you want.... If *every* cell in the row contains the same value then highlight that row. If *every* cell in that row does not contain the same value then do nothing. If that's what you want... Assuming there are no empty/blank cells... Try: =COUNTIF($A2:$W2,$A2)=COLUMNS($A2:$W2) -- Biff Microsoft Excel MVP "Ardy" wrote in message ... Hello All: Here is my dilemma…..I am using Excel 2007. I have a spreadsheet with 17,3322 row of data that spans to Column W (A2:W173322) excluding the header. I am trying to do conditional formatting to bring to light the duplicate values that are identical across the row and highlight them. The problem is that the built in function is highlighting duplicates whenever it finds it. I need to somehow only highlight if the entire row is duplicate and not only portion. A2:W2 is the entire row which represents a data set. I tried using =COUNTIF ($A$2:$W $173322,A2)1 but it is not quit working. Any Ideas……. Ardy Yes Partly If *every* cell in the row contains the same value then highlight that row Here is an Example * * * *A * * * *B * * * * C * * * * * D * * * * E 1 * * *al * * *bb * * * * cc * * * * dd * * * *ee 2 * * zz * * *gg * * * * mm * * * ll * * * * *ff 3 * * al * * * bb * * * * cc * * * * dd * * * *ee 4 * * ll * * * *mm * * * ff * * * * * pp * * * *ll 5 * * al * * * bb * * * * cc * * * * dd * * * *ee So the rows that should get highlighted are 1, 3 and 5 *becuse the rows contain the same info. Ardy- Hide quoted text - - Show quoted text - They will be a match, same order, exact duplicate....... Ardy |
Find Duplicate in a Row
Ok, because of the amount of data you're dealing with this is not easy!
You need to use a helper column and concatenate all the cells from each row. Like this: A B C D E 1 al bb cc dd ee =A1&B1&C1&D1&E1 This will return the concatenated string: albbccddee However, this has a potential problem. I'm guessing that your real data is not al bb cc dd ee. But, if it were here's where a problem could crop up: al...bb....cc...dd...ee a....lbb...cc...dd...ee When concatenated those rows would match. You can take care of that by adding a unique character between cells like this: =A1&"-"&B1&"-"&C1&"-"&D1&"-"&E1 Now the concatenated strings would be: al-bb-cc-dd-ee a-lbb-cc-dd-ee As you can probably tell this will be a real PITA with the number of columns of data you have. That's why this isn't easy! So, use a helper column and concatenate each row. You can enter the formula in the first row and then copy down to the end of data. Now, to format the rows... Let's assume the helper column is the range H1:H5 Select the range to highlight, assume it's A1:E5 Then use Formula Is: =COUNTIF($H$1:$H$5,$H1)1 -- Biff Microsoft Excel MVP "Ardy" wrote in message ... On May 22, 2:35 pm, "T. Valko" wrote: Are you looking for the *specific* entries: al...bb...cc...dd...ee Do they have to be in that *specific* order? Would this be a match? cc...bb...al...ee...dd All the values are the same but they're in a different order. -- Biff Microsoft Excel MVP "Ardy" wrote in message ... On May 22, 12:16 pm, "T. Valko" wrote: Let's see if I understand what you want.... If *every* cell in the row contains the same value then highlight that row. If *every* cell in that row does not contain the same value then do nothing. If that's what you want... Assuming there are no empty/blank cells... Try: =COUNTIF($A2:$W2,$A2)=COLUMNS($A2:$W2) -- Biff Microsoft Excel MVP "Ardy" wrote in message ... Hello All: Here is my dilemma…..I am using Excel 2007. I have a spreadsheet with 17,3322 row of data that spans to Column W (A2:W173322) excluding the header. I am trying to do conditional formatting to bring to light the duplicate values that are identical across the row and highlight them. The problem is that the built in function is highlighting duplicates whenever it finds it. I need to somehow only highlight if the entire row is duplicate and not only portion. A2:W2 is the entire row which represents a data set. I tried using =COUNTIF ($A$2:$W $173322,A2)1 but it is not quit working. Any Ideas……. Ardy Yes Partly If *every* cell in the row contains the same value then highlight that row Here is an Example A B C D E 1 al bb cc dd ee 2 zz gg mm ll ff 3 al bb cc dd ee 4 ll mm ff pp ll 5 al bb cc dd ee So the rows that should get highlighted are 1, 3 and 5 becuse the rows contain the same info. Ardy- Hide quoted text - - Show quoted text - They will be a match, same order, exact duplicate....... Ardy |
Find Duplicate in a Row
On May 23, 9:31*am, "T. Valko" wrote:
Ok, because of the amount of data you're dealing with this is not easy! You need to use a helper column and concatenate all the cells from each row. Like this: A B C D E 1 al bb cc dd ee =A1&B1&C1&D1&E1 This will return the concatenated string: albbccddee However, this has a potential problem. I'm guessing that your real data is not al bb cc dd ee. But, if it were here's where a problem could crop up: al...bb....cc...dd...ee a....lbb...cc...dd...ee When concatenated those rows would match. You can take care of that by adding a unique character between cells like this: =A1&"-"&B1&"-"&C1&"-"&D1&"-"&E1 Now the concatenated strings would be: al-bb-cc-dd-ee a-lbb-cc-dd-ee As you can probably tell this will be a real PITA with the number of columns of data you have. That's why this isn't easy! So, use a helper column and concatenate each row. You can enter the formula in the first row and then copy down to the end of data. Now, to format the rows... Let's assume the helper column is the range H1:H5 Select the range to highlight, assume it's A1:E5 Then use Formula Is: =COUNTIF($H$1:$H$5,$H1)1 -- Biff Microsoft Excel MVP "Ardy" wrote in message ... On May 22, 2:35 pm, "T. Valko" wrote: Are you looking for the *specific* entries: al...bb...cc...dd...ee Do they have to be in that *specific* order? Would this be a match? cc...bb...al...ee...dd All the values are the same but they're in a different order. -- Biff Microsoft Excel MVP "Ardy" wrote in message ... On May 22, 12:16 pm, "T. Valko" wrote: Let's see if I understand what you want.... If *every* cell in the row contains the same value then highlight that row. If *every* cell in that row does not contain the same value then do nothing. If that's what you want... Assuming there are no empty/blank cells... Try: =COUNTIF($A2:$W2,$A2)=COLUMNS($A2:$W2) -- Biff Microsoft Excel MVP "Ardy" wrote in message .... Hello All: Here is my dilemma…..I am using Excel 2007. I have a spreadsheet with 17,3322 row of data that spans to Column W (A2:W173322) excluding the header. I am trying to do conditional formatting to bring to light the duplicate values that are identical across the row and highlight them. The problem is that the built in function is highlighting duplicates whenever it finds it. I need to somehow only highlight if the entire row is duplicate and not only portion. A2:W2 is the entire row which represents a data set. I tried using =COUNTIF ($A$2:$W $173322,A2)1 but it is not quit working. Any Ideas……. Ardy Yes Partly If *every* cell in the row contains the same value then highlight that row Here is an Example A B C D E 1 al bb cc dd ee 2 zz gg mm ll ff 3 al bb cc dd ee 4 ll mm ff pp ll 5 al bb cc dd ee So the rows that should get highlighted are 1, 3 and 5 becuse the rows contain the same info. Ardy- Hide quoted text - - Show quoted text - They will be a match, *same order, *exact duplicate....... Ardy- Hide quoted text - - Show quoted text - I see your approach, Let me munch on this I like the concatenate solution, how would you parse them back to the original |
Find Duplicate in a Row
You wouldn't need to parse anything. You original data is not changed in any
way. Let me show you an example... Here's your original data: .......A...B...C 1...al...bb...cc 2...zz...gg...mm 3...al...bb...cc 4...ll...mm...ff 5...al...bb...cc Now, use a new column with the concatenated helper formula: .......A...B...C.............H 1...al...bb...cc...........albbcc 2...zz...gg...mm.........zzggmm 3...al...bb...cc...........albbcc 4...ll...mm...ff............llmmff 5...al...bb...cc...........albbcc The original data is still intact. We're just using the helper column as the basis of the formatting. -- Biff Microsoft Excel MVP "Ardy" wrote in message ... On May 23, 9:31 am, "T. Valko" wrote: Ok, because of the amount of data you're dealing with this is not easy! You need to use a helper column and concatenate all the cells from each row. Like this: A B C D E 1 al bb cc dd ee =A1&B1&C1&D1&E1 This will return the concatenated string: albbccddee However, this has a potential problem. I'm guessing that your real data is not al bb cc dd ee. But, if it were here's where a problem could crop up: al...bb....cc...dd...ee a....lbb...cc...dd...ee When concatenated those rows would match. You can take care of that by adding a unique character between cells like this: =A1&"-"&B1&"-"&C1&"-"&D1&"-"&E1 Now the concatenated strings would be: al-bb-cc-dd-ee a-lbb-cc-dd-ee As you can probably tell this will be a real PITA with the number of columns of data you have. That's why this isn't easy! So, use a helper column and concatenate each row. You can enter the formula in the first row and then copy down to the end of data. Now, to format the rows... Let's assume the helper column is the range H1:H5 Select the range to highlight, assume it's A1:E5 Then use Formula Is: =COUNTIF($H$1:$H$5,$H1)1 -- Biff Microsoft Excel MVP "Ardy" wrote in message ... On May 22, 2:35 pm, "T. Valko" wrote: Are you looking for the *specific* entries: al...bb...cc...dd...ee Do they have to be in that *specific* order? Would this be a match? cc...bb...al...ee...dd All the values are the same but they're in a different order. -- Biff Microsoft Excel MVP "Ardy" wrote in message ... On May 22, 12:16 pm, "T. Valko" wrote: Let's see if I understand what you want.... If *every* cell in the row contains the same value then highlight that row. If *every* cell in that row does not contain the same value then do nothing. If that's what you want... Assuming there are no empty/blank cells... Try: =COUNTIF($A2:$W2,$A2)=COLUMNS($A2:$W2) -- Biff Microsoft Excel MVP "Ardy" wrote in message ... Hello All: Here is my dilemma…..I am using Excel 2007. I have a spreadsheet with 17,3322 row of data that spans to Column W (A2:W173322) excluding the header. I am trying to do conditional formatting to bring to light the duplicate values that are identical across the row and highlight them. The problem is that the built in function is highlighting duplicates whenever it finds it. I need to somehow only highlight if the entire row is duplicate and not only portion. A2:W2 is the entire row which represents a data set. I tried using =COUNTIF ($A$2:$W $173322,A2)1 but it is not quit working. Any Ideas……. Ardy Yes Partly If *every* cell in the row contains the same value then highlight that row Here is an Example A B C D E 1 al bb cc dd ee 2 zz gg mm ll ff 3 al bb cc dd ee 4 ll mm ff pp ll 5 al bb cc dd ee So the rows that should get highlighted are 1, 3 and 5 becuse the rows contain the same info. Ardy- Hide quoted text - - Show quoted text - They will be a match, same order, exact duplicate....... Ardy- Hide quoted text - - Show quoted text - I see your approach, Let me munch on this I like the concatenate solution, how would you parse them back to the original |
Find Duplicate in a Row
On May 23, 11:24*am, "T. Valko" wrote:
You wouldn't need to parse anything. You original data is not changed in any way. Let me show you an example... Here's your original data: ......A...B...C 1...al...bb...cc 2...zz...gg...mm 3...al...bb...cc 4...ll...mm...ff 5...al...bb...cc Now, use a new column with the concatenated helper formula: ......A...B...C.............H 1...al...bb...cc...........albbcc 2...zz...gg...mm.........zzggmm 3...al...bb...cc...........albbcc 4...ll...mm...ff............llmmff 5...al...bb...cc...........albbcc The original data is still intact. We're just using the helper column as the basis of the formatting. -- Biff Microsoft Excel MVP "Ardy" wrote in message ... On May 23, 9:31 am, "T. Valko" wrote: Ok, because of the amount of data you're dealing with this is not easy! You need to use a helper column and concatenate all the cells from each row. Like this: A B C D E 1 al bb cc dd ee =A1&B1&C1&D1&E1 This will return the concatenated string: albbccddee However, this has a potential problem. I'm guessing that your real data is not al bb cc dd ee. But, if it were here's where a problem could crop up: al...bb....cc...dd...ee a....lbb...cc...dd...ee When concatenated those rows would match. You can take care of that by adding a unique character between cells like this: =A1&"-"&B1&"-"&C1&"-"&D1&"-"&E1 Now the concatenated strings would be: al-bb-cc-dd-ee a-lbb-cc-dd-ee As you can probably tell this will be a real PITA with the number of columns of data you have. That's why this isn't easy! So, use a helper column and concatenate each row. You can enter the formula in the first row and then copy down to the end of data. Now, to format the rows... Let's assume the helper column is the range H1:H5 Select the range to highlight, assume it's A1:E5 Then use Formula Is: =COUNTIF($H$1:$H$5,$H1)1 -- Biff Microsoft Excel MVP "Ardy" wrote in message ... On May 22, 2:35 pm, "T. Valko" wrote: Are you looking for the *specific* entries: al...bb...cc...dd...ee Do they have to be in that *specific* order? Would this be a match? cc...bb...al...ee...dd All the values are the same but they're in a different order. -- Biff Microsoft Excel MVP "Ardy" wrote in message .... On May 22, 12:16 pm, "T. Valko" wrote: Let's see if I understand what you want.... If *every* cell in the row contains the same value then highlight that row. If *every* cell in that row does not contain the same value then do nothing. If that's what you want... Assuming there are no empty/blank cells... Try: =COUNTIF($A2:$W2,$A2)=COLUMNS($A2:$W2) -- Biff Microsoft Excel MVP "Ardy" wrote in message ... Hello All: Here is my dilemma…..I am using Excel 2007. I have a spreadsheet with 17,3322 row of data that spans to Column W (A2:W173322) excluding the header. I am trying to do conditional formatting to bring to light the duplicate values that are identical across the row and highlight them. The problem is that the built in function is highlighting duplicates whenever it finds it. I need to somehow only highlight if the entire row is duplicate and not only portion. A2:W2 is the entire row which represents a data set. I tried using =COUNTIF ($A$2:$W $173322,A2)1 but it is not quit working. Any Ideas……. Ardy Yes Partly If *every* cell in the row contains the same value then highlight that row Here is an Example A B C D E 1 al bb cc dd ee 2 zz gg mm ll ff 3 al bb cc dd ee 4 ll mm ff pp ll 5 al bb cc dd ee So the rows that should get highlighted are 1, 3 and 5 becuse the rows contain the same info. Ardy- Hide quoted text - - Show quoted text - They will be a match, same order, exact duplicate....... Ardy- Hide quoted text - - Show quoted text - I see your approach, *Let me munch on this I like the concatenate solution, *how would you parse them back to the original- Hide quoted text - - Show quoted text - oh I get it...... This should work..... Thanks |
| All times are GMT +1. The time now is 07:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com