Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
COMPARE IDENTICAL DATA
Hi,
I have two different excel spreadsheets. 1- With a list of product reference numbers,description and EAN code total of 7404 lines 2- The same information as above along with more codes,prices and descriptions. 219 lines Thereference numbers in wrksht 1 and 2 match. So if you did control find you find an exact match! However, to do control find for 219 lines of data takes allot of time. Is there another way I can extract the exact matches? Please let me know if you need more detail. holagigi |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
COMPARE IDENTICAL DATA
Hi,
The first list is in A2:B19, and this range is named OldList. The second list is in D2:E19, and the range is named NewList. The ranges were named using the Insert - Name - Define command. Naming the ranges is not necessary, but it makes them easier to work with. As you can see, items in OldList that do not appear in NewList are highlighted with a yellow background. Items in NewList that do not appear in OldList are highlighted with a green background. These colors are the result of Conditional Formatting. How to do it Start by selecting the OldList range. Choose Format - Conditional Formatting In the Conditional Formatting dialog box, use the drop-down list to choose Formula is. Enter this formula: =COUNTIF(NewList,A2)=0 Click the Format button and specify the formatting to apply when the condition is true (a yellow background in this example). Click OK The cells in the NewList range will use a similar conditional formatting formula. Select the NewList range. Choose Format - Conditional Formatting In the Conditional Formatting dialog box, use the drop-down list to choose Formula is. Enter this formula: =COUNTIF(OldList,D2)=0 Click the Format button and specify the formatting to apply when the condition is true (a green background in this example). Click OK Both of these conditional formatting formulas use the COUNTIF function. This function counts the number of times a particular value appears in a range. If the formula returns 0, it means that the item does not appear in the range. Therefore, the conditional formatting kicks in and the cell's background color is changed. The cell reference in the COUNTIF function should always be the upper left cell of the selected range. hth regards from Brazil Marcelo "holagigi" escreveu: Hi, I have two different excel spreadsheets. 1- With a list of product reference numbers,description and EAN code total of 7404 lines 2- The same information as above along with more codes,prices and descriptions. 219 lines Thereference numbers in wrksht 1 and 2 match. So if you did control find you find an exact match! However, to do control find for 219 lines of data takes allot of time. Is there another way I can extract the exact matches? Please let me know if you need more detail. holagigi |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
COMPARE IDENTICAL DATA
Hi Marcelo,
I am a little confused. When you say A2:B19 you are just using that as an example correct? You realize I have the data in two different worksheets correct? I tried to follow your steps and it didn't work. Any more information would be helpful. holagigi Regards from spain:) "Marcelo" wrote: Hi, The first list is in A2:B19, and this range is named OldList. The second list is in D2:E19, and the range is named NewList. The ranges were named using the Insert - Name - Define command. Naming the ranges is not necessary, but it makes them easier to work with. As you can see, items in OldList that do not appear in NewList are highlighted with a yellow background. Items in NewList that do not appear in OldList are highlighted with a green background. These colors are the result of Conditional Formatting. How to do it Start by selecting the OldList range. Choose Format - Conditional Formatting In the Conditional Formatting dialog box, use the drop-down list to choose Formula is. Enter this formula: =COUNTIF(NewList,A2)=0 Click the Format button and specify the formatting to apply when the condition is true (a yellow background in this example). Click OK The cells in the NewList range will use a similar conditional formatting formula. Select the NewList range. Choose Format - Conditional Formatting In the Conditional Formatting dialog box, use the drop-down list to choose Formula is. Enter this formula: =COUNTIF(OldList,D2)=0 Click the Format button and specify the formatting to apply when the condition is true (a green background in this example). Click OK Both of these conditional formatting formulas use the COUNTIF function. This function counts the number of times a particular value appears in a range. If the formula returns 0, it means that the item does not appear in the range. Therefore, the conditional formatting kicks in and the cell's background color is changed. The cell reference in the COUNTIF function should always be the upper left cell of the selected range. hth regards from Brazil Marcelo "holagigi" escreveu: Hi, I have two different excel spreadsheets. 1- With a list of product reference numbers,description and EAN code total of 7404 lines 2- The same information as above along with more codes,prices and descriptions. 219 lines Thereference numbers in wrksht 1 and 2 match. So if you did control find you find an exact match! However, to do control find for 219 lines of data takes allot of time. Is there another way I can extract the exact matches? Please let me know if you need more detail. holagigi |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
COMPARE IDENTICAL DATA
Hola,
I have tried it here before and it works, yes a2:b19 are sample - adjust for your range as your convinience as we are using names does not matter if it is on another w.sheet both data table as big as? gracias. avante furia nos vemos no sabado.... ;o) "holagigi" escreveu: Hi Marcelo, I am a little confused. When you say A2:B19 you are just using that as an example correct? You realize I have the data in two different worksheets correct? I tried to follow your steps and it didn't work. Any more information would be helpful. holagigi Regards from spain:) "Marcelo" wrote: Hi, The first list is in A2:B19, and this range is named OldList. The second list is in D2:E19, and the range is named NewList. The ranges were named using the Insert - Name - Define command. Naming the ranges is not necessary, but it makes them easier to work with. As you can see, items in OldList that do not appear in NewList are highlighted with a yellow background. Items in NewList that do not appear in OldList are highlighted with a green background. These colors are the result of Conditional Formatting. How to do it Start by selecting the OldList range. Choose Format - Conditional Formatting In the Conditional Formatting dialog box, use the drop-down list to choose Formula is. Enter this formula: =COUNTIF(NewList,A2)=0 Click the Format button and specify the formatting to apply when the condition is true (a yellow background in this example). Click OK The cells in the NewList range will use a similar conditional formatting formula. Select the NewList range. Choose Format - Conditional Formatting In the Conditional Formatting dialog box, use the drop-down list to choose Formula is. Enter this formula: =COUNTIF(OldList,D2)=0 Click the Format button and specify the formatting to apply when the condition is true (a green background in this example). Click OK Both of these conditional formatting formulas use the COUNTIF function. This function counts the number of times a particular value appears in a range. If the formula returns 0, it means that the item does not appear in the range. Therefore, the conditional formatting kicks in and the cell's background color is changed. The cell reference in the COUNTIF function should always be the upper left cell of the selected range. hth regards from Brazil Marcelo "holagigi" escreveu: Hi, I have two different excel spreadsheets. 1- With a list of product reference numbers,description and EAN code total of 7404 lines 2- The same information as above along with more codes,prices and descriptions. 219 lines Thereference numbers in wrksht 1 and 2 match. So if you did control find you find an exact match! However, to do control find for 219 lines of data takes allot of time. Is there another way I can extract the exact matches? Please let me know if you need more detail. holagigi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare month to month data | Excel Worksheet Functions | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Sorting Identical Data set in a work sheet | Excel Worksheet Functions | |||
Line Graph Data Recognition | Charts and Charting in Excel |