| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Excel
If I have a master list of 20 part numbers in one Excel column or worksheet
how can I compare each part numbers in the master column against actual values/part numbers in another column or worksheet, and highlight in red the missing parts in a third column? Is there a wizzard to help me or will I have to write a complicated macro or loop? |
|
#2
|
|||
|
|||
|
Hi!
Column A1:A19 = secondary list Column B1:B19 = master list In C1 enter this formula with the key combo of CTRL,SHIFT,ENTER: =IF(ISERROR(SMALL(IF(COUNTIF(A$1:A$19,B$1:B$19)=0, ROW ($1:$19)),ROW(1:1))),"",INDEX(B$1:B$19,SMALL(IF(CO UNTIF (A$1:A$19,B$1:B$19)=0,ROW($1:$19)),ROW(1:1)))) Copy down until you get "blanks". Will return: C1 = 12A221332 C2 = 12A221333 C3 = blank Biff -----Original Message----- If I have a master list of 20 part numbers in one Excel column or worksheet how can I compare each part numbers in the master column against actual values/part numbers in another column or worksheet, and highlight in red the missing parts in a third column? Is there a wizzard to help me or will I have to write a complicated macro or loop? . |
|
#3
|
|||
|
|||
|
Biff
I selected C1 and presses key combo of CTRL,SHIFT,ENTER, but was unable to paste in the formula. "Biff" wrote: Hi! Column A1:A19 = secondary list Column B1:B19 = master list In C1 enter this formula with the key combo of CTRL,SHIFT,ENTER: =IF(ISERROR(SMALL(IF(COUNTIF(A$1:A$19,B$1:B$19)=0, ROW ($1:$19)),ROW(1:1))),"",INDEX(B$1:B$19,SMALL(IF(CO UNTIF (A$1:A$19,B$1:B$19)=0,ROW($1:$19)),ROW(1:1)))) Copy down until you get "blanks". Will return: C1 = 12A221332 C2 = 12A221333 C3 = blank Biff -----Original Message----- If I have a master list of 20 part numbers in one Excel column or worksheet how can I compare each part numbers in the master column against actual values/part numbers in another column or worksheet, and highlight in red the missing parts in a third column? Is there a wizzard to help me or will I have to write a complicated macro or loop? . |
|
#4
|
|||
|
|||
|
Hi!
Copy/Paste the formula into cell C1. The formula will appear in the Formula Bar. Goto the Formula Bar and place the cursor at the end of the formula. Hold down the CTRL key and the SHIFT key then hit ENTER. If done properly Excel will enclose the formula in squiggly braces { } which means that it is an array formula. You must use the key combo of CTRL,SHIFT,ENTER for it to work. You can not just simply type the braces in. Biff -----Original Message----- Biff I selected C1 and presses key combo of CTRL,SHIFT,ENTER, but was unable to paste in the formula. "Biff" wrote: Hi! Column A1:A19 = secondary list Column B1:B19 = master list In C1 enter this formula with the key combo of CTRL,SHIFT,ENTER: =IF(ISERROR(SMALL(IF(COUNTIF(A$1:A$19,B$1:B$19)=0, ROW ($1:$19)),ROW(1:1))),"",INDEX(B$1:B$19,SMALL(IF(CO UNTIF (A$1:A$19,B$1:B$19)=0,ROW($1:$19)),ROW(1:1)))) Copy down until you get "blanks". Will return: C1 = 12A221332 C2 = 12A221333 C3 = blank Biff -----Original Message----- If I have a master list of 20 part numbers in one Excel column or worksheet how can I compare each part numbers in the master column against actual values/part numbers in another column or worksheet, and highlight in red the missing parts in a third column? Is there a wizzard to help me or will I have to write a complicated macro or loop? . . |
|
#5
|
|||
|
|||
|
Biff,
I get an error message "It says my formula is missing parenthesis. This is the formula I copy/pasted into C1, then clicked at the end of the formula and pressed Ctrl, Shift, Enter =IF(ISERROR(SMALL(IF(COUNTIF(A$1:A$19,B$1:B$19)=0, ROW ($1:$19)),ROW(1:1))),"",INDEX(B$1:B$19,SMALL(IF(CO UNTIF (A$1:A$19,B$1:B$19)=0,ROW($1:$19)),ROW(1:1)))) "Biff" wrote: Hi! Copy/Paste the formula into cell C1. The formula will appear in the Formula Bar. Goto the Formula Bar and place the cursor at the end of the formula. Hold down the CTRL key and the SHIFT key then hit ENTER. If done properly Excel will enclose the formula in squiggly braces { } which means that it is an array formula. You must use the key combo of CTRL,SHIFT,ENTER for it to work. You can not just simply type the braces in. Biff -----Original Message----- Biff I selected C1 and presses key combo of CTRL,SHIFT,ENTER, but was unable to paste in the formula. "Biff" wrote: Hi! Column A1:A19 = secondary list Column B1:B19 = master list In C1 enter this formula with the key combo of CTRL,SHIFT,ENTER: =IF(ISERROR(SMALL(IF(COUNTIF(A$1:A$19,B$1:B$19)=0, ROW ($1:$19)),ROW(1:1))),"",INDEX(B$1:B$19,SMALL(IF(CO UNTIF (A$1:A$19,B$1:B$19)=0,ROW($1:$19)),ROW(1:1)))) Copy down until you get "blanks". Will return: C1 = 12A221332 C2 = 12A221333 C3 = blank Biff -----Original Message----- If I have a master list of 20 part numbers in one Excel column or worksheet how can I compare each part numbers in the master column against actual values/part numbers in another column or worksheet, and highlight in red the missing parts in a third column? Is there a wizzard to help me or will I have to write a complicated macro or loop? . . |
|
#6
|
|||
|
|||
|
Hi!
Should work, there's nothing wrong with the formula. The formula is all on one line. Sometimes if you copy from a certain source you can get messed up with line breaks or word wrap. If you want to send me a copy of the file I'll do it for you. Post back with an email address and I'll contact you. Biff -----Original Message----- Biff, I get an error message "It says my formula is missing parenthesis. This is the formula I copy/pasted into C1, then clicked at the end of the formula and pressed Ctrl, Shift, Enter =IF(ISERROR(SMALL(IF(COUNTIF(A$1:A$19,B$1:B$19)=0 ,ROW ($1:$19)),ROW(1:1))),"",INDEX(B$1:B$19,SMALL(IF(C OUNTIF (A$1:A$19,B$1:B$19)=0,ROW($1:$19)),ROW(1:1)))) "Biff" wrote: Hi! Copy/Paste the formula into cell C1. The formula will appear in the Formula Bar. Goto the Formula Bar and place the cursor at the end of the formula. Hold down the CTRL key and the SHIFT key then hit ENTER. If done properly Excel will enclose the formula in squiggly braces { } which means that it is an array formula. You must use the key combo of CTRL,SHIFT,ENTER for it to work. You can not just simply type the braces in. Biff -----Original Message----- Biff I selected C1 and presses key combo of CTRL,SHIFT,ENTER, but was unable to paste in the formula. "Biff" wrote: Hi! Column A1:A19 = secondary list Column B1:B19 = master list In C1 enter this formula with the key combo of CTRL,SHIFT,ENTER: =IF(ISERROR(SMALL(IF(COUNTIF(A$1:A$19,B$1:B$19)=0, ROW ($1:$19)),ROW(1:1))),"",INDEX(B$1:B$19,SMALL(IF (COUNTIF (A$1:A$19,B$1:B$19)=0,ROW($1:$19)),ROW(1:1)))) Copy down until you get "blanks". Will return: C1 = 12A221332 C2 = 12A221333 C3 = blank Biff -----Original Message----- If I have a master list of 20 part numbers in one Excel column or worksheet how can I compare each part numbers in the master column against actual values/part numbers in another column or worksheet, and highlight in red the missing parts in a third column? Is there a wizzard to help me or will I have to write a complicated macro or loop? . . . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| html to excel | Excel Discussion (Misc queries) | |||
| Excel error - Startup (and Acrobat PDFMaker) | Setting up and Configuration of Excel | |||
| Merge from Excel to Excel | Excel Discussion (Misc queries) | |||
| Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) | |||
| Shortcut file fails to open | Excel Discussion (Misc queries) | |||
Hybrid Mode
