Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match duplicate data in 2 columns in a worksheet
I am working in 1 worksheet and have data in 2 columns and I would like to
search for duplicates and have the matches highlight automatically. Below is an example: Tenant Debit Credit ABC $500 ABC $500 Then the 2 duplicate entries would be highlighted in blue. I am somewhat of a beginner at the function entries, so please explain the steps in detail. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match duplicate data in 2 columns in a worksheet
Check out this web site of Chip Pearson.
Scroll down to the D's, and see all the articles on dealing with duplicates. http://www.cpearson.com/excel/topic.htm -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Kris" wrote in message ... I am working in 1 worksheet and have data in 2 columns and I would like to search for duplicates and have the matches highlight automatically. Below is an example: Tenant Debit Credit ABC $500 ABC $500 Then the 2 duplicate entries would be highlighted in blue. I am somewhat of a beginner at the function entries, so please explain the steps in detail. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match duplicate data in 2 columns in a worksheet
I reviewed the section regarding duplicates; however,it only explains finding
duplicates in a single column. I need to match duplicates in 2 columns. How do I do this?? "Ragdyer" wrote: Check out this web site of Chip Pearson. Scroll down to the D's, and see all the articles on dealing with duplicates. http://www.cpearson.com/excel/topic.htm -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Kris" wrote in message ... I am working in 1 worksheet and have data in 2 columns and I would like to search for duplicates and have the matches highlight automatically. Below is an example: Tenant Debit Credit ABC $500 ABC $500 Then the 2 duplicate entries would be highlighted in blue. I am somewhat of a beginner at the function entries, so please explain the steps in detail. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match duplicate data in 2 columns in a worksheet
High light your range. Assuming your range from A2:B100
Conditional Formatting, Select Formula IS =SUMPRODUCT(--($A$2:$A$100=$A2),--($B$2:$B$5=$B2))1 Select Format Font, Color Blue, then click OK --------------- mama no teeth "Kris" wrote: I am working in 1 worksheet and have data in 2 columns and I would like to search for duplicates and have the matches highlight automatically. Below is an example: Tenant Debit Credit ABC $500 ABC $500 Then the 2 duplicate entries would be highlighted in blue. I am somewhat of a beginner at the function entries, so please explain the steps in detail. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match duplicate data in 2 columns in a worksheet
Which 2 columns?
Your example shows 3 columns! Do you want to highlight just the two $500 entries? Go to the original link: http://www.cpearson.com/excel/topic.htm Scroll down and click on: "Duplicate Entries, Highlighting" Then, simply follow the directions. Just because Chip used an example range name of "range1", *doesn't* mean that the range *must* be a *single* column. Select your 2 columns, revise the formula to insert your top left cell of your 2 column range, and follow directions. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Kris" wrote in message ... I reviewed the section regarding duplicates; however,it only explains finding duplicates in a single column. I need to match duplicates in 2 columns. How do I do this?? "Ragdyer" wrote: Check out this web site of Chip Pearson. Scroll down to the D's, and see all the articles on dealing with duplicates. http://www.cpearson.com/excel/topic.htm -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Kris" wrote in message ... I am working in 1 worksheet and have data in 2 columns and I would like to search for duplicates and have the matches highlight automatically. Below is an example: Tenant Debit Credit ABC $500 ABC $500 Then the 2 duplicate entries would be highlighted in blue. I am somewhat of a beginner at the function entries, so please explain the steps in detail. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match duplicate data in 2 columns in a worksheet
Nobody, I entered in the below formula and nothing happened. Let me be more
specific with what I need: First, I copy and paste my document into EXCEL and sort in alpha order by the tenants name. Then I have to go down column 1 that are the payments received and cross-reference with column 2, which are the charges and have the matched data highlighted, so I can eliminate it to get to the bottom number. EXAMPLE: Tenant Name Column1 Column2 TBELL 50.00 TBELL 50.00 I would like the system to highlight the 2 - 50.00 entries. Would I need to do this as a conditional format or some other function? I really appreciate your help! "Nobody" wrote: High light your range. Assuming your range from A2:B100 Conditional Formatting, Select Formula IS =SUMPRODUCT(--($A$2:$A$100=$A2),--($B$2:$B$5=$B2))1 Select Format Font, Color Blue, then click OK --------------- mama no teeth "Kris" wrote: I am working in 1 worksheet and have data in 2 columns and I would like to search for duplicates and have the matches highlight automatically. Below is an example: Tenant Debit Credit ABC $500 ABC $500 Then the 2 duplicate entries would be highlighted in blue. I am somewhat of a beginner at the function entries, so please explain the steps in detail. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match duplicate data in 2 columns in a worksheet
It's been a whole day now!
Have you tried using Chip's directions as I've suggested? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Kris" wrote in message ... Nobody, I entered in the below formula and nothing happened. Let me be more specific with what I need: First, I copy and paste my document into EXCEL and sort in alpha order by the tenants name. Then I have to go down column 1 that are the payments received and cross-reference with column 2, which are the charges and have the matched data highlighted, so I can eliminate it to get to the bottom number. EXAMPLE: Tenant Name Column1 Column2 TBELL 50.00 TBELL 50.00 I would like the system to highlight the 2 - 50.00 entries. Would I need to do this as a conditional format or some other function? I really appreciate your help! "Nobody" wrote: High light your range. Assuming your range from A2:B100 Conditional Formatting, Select Formula IS =SUMPRODUCT(--($A$2:$A$100=$A2),--($B$2:$B$5=$B2))1 Select Format Font, Color Blue, then click OK --------------- mama no teeth "Kris" wrote: I am working in 1 worksheet and have data in 2 columns and I would like to search for duplicates and have the matches highlight automatically. Below is an example: Tenant Debit Credit ABC $500 ABC $500 Then the 2 duplicate entries would be highlighted in blue. I am somewhat of a beginner at the function entries, so please explain the steps in detail. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match duplicate data in 2 columns in a worksheet
RagDyer,
While I can surely appreciate your help in resolving this matter, I would rather you not treat me like a dumbass. As I said in my initial posting...I am relatively new to this process and would need a step by step explanation on how to accomplish my task. I have reviewed the Chip's directions and unfortunately I can not figure out how to compare 2 columns on a single sheet. Can you assist me with this-minus the sarcasm, please? "RagDyer" wrote: It's been a whole day now! Have you tried using Chip's directions as I've suggested? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Kris" wrote in message ... Nobody, I entered in the below formula and nothing happened. Let me be more specific with what I need: First, I copy and paste my document into EXCEL and sort in alpha order by the tenants name. Then I have to go down column 1 that are the payments received and cross-reference with column 2, which are the charges and have the matched data highlighted, so I can eliminate it to get to the bottom number. EXAMPLE: Tenant Name Column1 Column2 TBELL 50.00 TBELL 50.00 I would like the system to highlight the 2 - 50.00 entries. Would I need to do this as a conditional format or some other function? I really appreciate your help! "Nobody" wrote: High light your range. Assuming your range from A2:B100 Conditional Formatting, Select Formula IS =SUMPRODUCT(--($A$2:$A$100=$A2),--($B$2:$B$5=$B2))1 Select Format Font, Color Blue, then click OK --------------- mama no teeth "Kris" wrote: I am working in 1 worksheet and have data in 2 columns and I would like to search for duplicates and have the matches highlight automatically. Below is an example: Tenant Debit Credit ABC $500 ABC $500 Then the 2 duplicate entries would be highlighted in blue. I am somewhat of a beginner at the function entries, so please explain the steps in detail. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match duplicate data in 2 columns in a worksheet
I thought my *second* post contained enough information to help you do what
you wished. For some reason, you chose not to answer that post, even possibly stating that you were still confused. *That* was the reason for the 3rd post, *not* that I thought you were a "dumb ass". As you read through these groups, you should notice that "sugar" generates more help then "vinegar". In this case, I'll forget your "vinegar" post. From Chip's web page, as I directed you in my second post:: "Duplicate Entries, Highlighting" <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< <<<<<<<<<<<<< Our first task is to highlight the cells in Range1 that are duplicates. We use Excel's Conditional Formatting tool to accomplish this. First, highlight the entire Range1. Then, select the Conditional Formatting tool from the Format menu: Format-Conditional Formatting. Change the "Cell Value Is" option to "Formula Is" and enter the following formula in the formula text box: =IF(COUNTIF(Range1, A5)1,TRUE,FALSE) Where A5 is the first cell in Range1. Then, click the Format button and select the font or color you want your cell formatted with. Finally, click OK. Duplicate entries in Range1 will be formatted as you selected. For example, if "Able" occurs twice in Range1, both occurrences of "Able" will appear highlighted. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< <<<<<<<<<<<<< 1] Highlite entire Range 1 In your case, it might be B1 to C100 2] Then, select the Conditional Formatting tool from the Format menu: While your range is *still* selected, from the menu bar, click: <Format <Conditional Formatting 3]Change the "Cell Value Is" option to "Formula Is" Expand the "Cell Value Is" box by clicking the small down arrow on the right side, And click on "Formula Is" 4] enter the following formula in the formula text box: =IF(COUNTIF(Range1, A5)1,TRUE,FALSE) Where A5 is the first cell in Range1 In your case, the formula would be: =IF(COUNTIF($B$1:$C$100,B1)1,TRUE,FALSE) This formula does work, but it can shortened however to: =COUNTIF($B$1:$C$100,B1)1 5] Then, click the Format button and select the font or color you want your cell formatted with. This should be self explanatory. 6] Finally, click OK As you can see, Chip explained it all ... *AND* ... in my second post ... I explained that Range1 could be larger then a single column. It would be nice now, that if this works or not, you reply back with the result. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Kris" wrote in message ... RagDyer, While I can surely appreciate your help in resolving this matter, I would rather you not treat me like a dumbass. As I said in my initial posting...I am relatively new to this process and would need a step by step explanation on how to accomplish my task. I have reviewed the Chip's directions and unfortunately I can not figure out how to compare 2 columns on a single sheet. Can you assist me with this-minus the sarcasm, please? "RagDyer" wrote: It's been a whole day now! Have you tried using Chip's directions as I've suggested? -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Kris" wrote in message ... Nobody, I entered in the below formula and nothing happened. Let me be more specific with what I need: First, I copy and paste my document into EXCEL and sort in alpha order by the tenant's name. Then I have to go down column 1 that are the payments received and cross-reference with column 2, which are the charges and have the matched data highlighted, so I can eliminate it to get to the bottom number. EXAMPLE: Tenant Name Column1 Column2 TBELL 50.00 TBELL 50.00 I would like the system to highlight the 2 - 50.00 entries. Would I need to do this as a conditional format or some other function? I really appreciate your help! "Nobody" wrote: High light your range. Assuming your range from A2:B100 Conditional Formatting, Select Formula IS =SUMPRODUCT(--($A$2:$A$100=$A2),--($B$2:$B$5=$B2))1 Select Format Font, Color Blue, then click OK --------------- mama no teeth "Kris" wrote: I am working in 1 worksheet and have data in 2 columns and I would like to search for duplicates and have the matches highlight automatically. Below is an example: Tenant Debit Credit ABC $500 ABC $500 Then the 2 duplicate entries would be highlighted in blue. I am somewhat of a beginner at the function entries, so please explain the steps in detail. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match duplicate data in 2 columns in a worksheet
Thank you it worked!!! I appreciate your help!!
"RagDyeR" wrote: I thought my *second* post contained enough information to help you do what you wished. For some reason, you chose not to answer that post, even possibly stating that you were still confused. *That* was the reason for the 3rd post, *not* that I thought you were a "dumb ass". As you read through these groups, you should notice that "sugar" generates more help then "vinegar". In this case, I'll forget your "vinegar" post. From Chip's web page, as I directed you in my second post:: "Duplicate Entries, Highlighting" <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< <<<<<<<<<<<<< Our first task is to highlight the cells in Range1 that are duplicates. We use Excel's Conditional Formatting tool to accomplish this. First, highlight the entire Range1. Then, select the Conditional Formatting tool from the Format menu: Format-Conditional Formatting. Change the "Cell Value Is" option to "Formula Is" and enter the following formula in the formula text box: =IF(COUNTIF(Range1, A5)1,TRUE,FALSE) Where A5 is the first cell in Range1. Then, click the Format button and select the font or color you want your cell formatted with. Finally, click OK. Duplicate entries in Range1 will be formatted as you selected. For example, if "Able" occurs twice in Range1, both occurrences of "Able" will appear highlighted. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< <<<<<<<<<<<<< 1] Highlite entire Range 1 In your case, it might be B1 to C100 2] Then, select the Conditional Formatting tool from the Format menu: While your range is *still* selected, from the menu bar, click: <Format <Conditional Formatting 3]Change the "Cell Value Is" option to "Formula Is" Expand the "Cell Value Is" box by clicking the small down arrow on the right side, And click on "Formula Is" 4] enter the following formula in the formula text box: =IF(COUNTIF(Range1, A5)1,TRUE,FALSE) Where A5 is the first cell in Range1 In your case, the formula would be: =IF(COUNTIF($B$1:$C$100,B1)1,TRUE,FALSE) This formula does work, but it can shortened however to: =COUNTIF($B$1:$C$100,B1)1 5] Then, click the Format button and select the font or color you want your cell formatted with. This should be self explanatory. 6] Finally, click OK As you can see, Chip explained it all ... *AND* ... in my second post ... I explained that Range1 could be larger then a single column. It would be nice now, that if this works or not, you reply back with the result. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Kris" wrote in message ... RagDyer, While I can surely appreciate your help in resolving this matter, I would rather you not treat me like a dumbass. As I said in my initial posting...I am relatively new to this process and would need a step by step explanation on how to accomplish my task. I have reviewed the Chip's directions and unfortunately I can not figure out how to compare 2 columns on a single sheet. Can you assist me with this-minus the sarcasm, please? "RagDyer" wrote: It's been a whole day now! Have you tried using Chip's directions as I've suggested? -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Kris" wrote in message ... Nobody, I entered in the below formula and nothing happened. Let me be more specific with what I need: First, I copy and paste my document into EXCEL and sort in alpha order by the tenant's name. Then I have to go down column 1 that are the payments received and cross-reference with column 2, which are the charges and have the matched data highlighted, so I can eliminate it to get to the bottom number. EXAMPLE: Tenant Name Column1 Column2 TBELL 50.00 TBELL 50.00 I would like the system to highlight the 2 - 50.00 entries. Would I need to do this as a conditional format or some other function? I really appreciate your help! "Nobody" wrote: High light your range. Assuming your range from A2:B100 Conditional Formatting, Select Formula IS =SUMPRODUCT(--($A$2:$A$100=$A2),--($B$2:$B$5=$B2))1 Select Format Font, Color Blue, then click OK --------------- mama no teeth "Kris" wrote: I am working in 1 worksheet and have data in 2 columns and I would like to search for duplicates and have the matches highlight automatically. Below is an example: Tenant Debit Credit ABC $500 ABC $500 Then the 2 duplicate entries would be highlighted in blue. I am somewhat of a beginner at the function entries, so please explain the steps in detail. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match duplicate data in 2 columns in a worksheet
You're welcome, and appreciate the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Kris" wrote in message ... Thank you it worked!!! I appreciate your help!! "RagDyeR" wrote: I thought my *second* post contained enough information to help you do what you wished. For some reason, you chose not to answer that post, even possibly stating that you were still confused. *That* was the reason for the 3rd post, *not* that I thought you were a "dumb ass". As you read through these groups, you should notice that "sugar" generates more help then "vinegar". In this case, I'll forget your "vinegar" post. From Chip's web page, as I directed you in my second post:: "Duplicate Entries, Highlighting" <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< <<<<<<<<<<<<< Our first task is to highlight the cells in Range1 that are duplicates. We use Excel's Conditional Formatting tool to accomplish this. First, highlight the entire Range1. Then, select the Conditional Formatting tool from the Format menu: Format-Conditional Formatting. Change the "Cell Value Is" option to "Formula Is" and enter the following formula in the formula text box: =IF(COUNTIF(Range1, A5)1,TRUE,FALSE) Where A5 is the first cell in Range1. Then, click the Format button and select the font or color you want your cell formatted with. Finally, click OK. Duplicate entries in Range1 will be formatted as you selected. For example, if "Able" occurs twice in Range1, both occurrences of "Able" will appear highlighted. <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< <<<<<<<<<<<<< 1] Highlite entire Range 1 In your case, it might be B1 to C100 2] Then, select the Conditional Formatting tool from the Format menu: While your range is *still* selected, from the menu bar, click: <Format <Conditional Formatting 3]Change the "Cell Value Is" option to "Formula Is" Expand the "Cell Value Is" box by clicking the small down arrow on the right side, And click on "Formula Is" 4] enter the following formula in the formula text box: =IF(COUNTIF(Range1, A5)1,TRUE,FALSE) Where A5 is the first cell in Range1 In your case, the formula would be: =IF(COUNTIF($B$1:$C$100,B1)1,TRUE,FALSE) This formula does work, but it can shortened however to: =COUNTIF($B$1:$C$100,B1)1 5] Then, click the Format button and select the font or color you want your cell formatted with. This should be self explanatory. 6] Finally, click OK As you can see, Chip explained it all ... *AND* ... in my second post ... I explained that Range1 could be larger then a single column. It would be nice now, that if this works or not, you reply back with the result. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "Kris" wrote in message ... RagDyer, While I can surely appreciate your help in resolving this matter, I would rather you not treat me like a dumbass. As I said in my initial posting...I am relatively new to this process and would need a step by step explanation on how to accomplish my task. I have reviewed the Chip's directions and unfortunately I can not figure out how to compare 2 columns on a single sheet. Can you assist me with this-minus the sarcasm, please? "RagDyer" wrote: It's been a whole day now! Have you tried using Chip's directions as I've suggested? -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Kris" wrote in message ... Nobody, I entered in the below formula and nothing happened. Let me be more specific with what I need: First, I copy and paste my document into EXCEL and sort in alpha order by the tenant's name. Then I have to go down column 1 that are the payments received and cross-reference with column 2, which are the charges and have the matched data highlighted, so I can eliminate it to get to the bottom number. EXAMPLE: Tenant Name Column1 Column2 TBELL 50.00 TBELL 50.00 I would like the system to highlight the 2 - 50.00 entries. Would I need to do this as a conditional format or some other function? I really appreciate your help! "Nobody" wrote: High light your range. Assuming your range from A2:B100 Conditional Formatting, Select Formula IS =SUMPRODUCT(--($A$2:$A$100=$A2),--($B$2:$B$5=$B2))1 Select Format Font, Color Blue, then click OK --------------- mama no teeth "Kris" wrote: I am working in 1 worksheet and have data in 2 columns and I would like to search for duplicates and have the matches highlight automatically. Below is an example: Tenant Debit Credit ABC $500 ABC $500 Then the 2 duplicate entries would be highlighted in blue. I am somewhat of a beginner at the function entries, so please explain the steps in detail. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
Retrieve data from all of the worksheet within the file | Excel Worksheet Functions | |||
Excel-rolling data linked to chart & worksheet columns must stay | Charts and Charting in Excel | |||
Sort pages? | Excel Discussion (Misc queries) | |||
MATCH function - 2 columns w/ SIMILAR, not EXACT data | Excel Worksheet Functions |