How do I find data excluded from a list in excel?
HI,
Let say I have the following list in cells B2 to B10: "B2, B3, B4, B5, B6, B7, B8, B9, B10". After some manipulations, I have selected the following from the list:"B2, B3, B4, B8, B9, B10" in cells C2 to C7. I then would like to display in cells C8 to 11 the data that have not been selected in cells C2 to C7. How do I go about setting this up without using multiple nested if statements? Or a simplified version of the above: Let say I have the letters "A" in cell B2, "B" in cell B3, and "C" in cell B4. In cell C2 and C3 I enter two of the letters. (For example, I enter in cell C2 the letter "A" and in cell C3 I enter "C"). In cell C4 I want to display the letter that has not been selected (which should be "B"). How do I go about setting this up without using multiple nested if statements? Thanks, |
How do I find data excluded from a list in excel?
Based on your first description enter this array formula** in C8 and copy
down to C10: =INDEX(B$2:B$10,SMALL(IF(ISNA(MATCH(B$2:B$10,C$2:C $7,0)),ROW(B$2:B$10)),ROWS(C$8:C8))-ROW(B$2)+1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Nguyen92002" wrote in message ... HI, Let say I have the following list in cells B2 to B10: "B2, B3, B4, B5, B6, B7, B8, B9, B10". After some manipulations, I have selected the following from the list:"B2, B3, B4, B8, B9, B10" in cells C2 to C7. I then would like to display in cells C8 to 11 the data that have not been selected in cells C2 to C7. How do I go about setting this up without using multiple nested if statements? Or a simplified version of the above: Let say I have the letters "A" in cell B2, "B" in cell B3, and "C" in cell B4. In cell C2 and C3 I enter two of the letters. (For example, I enter in cell C2 the letter "A" and in cell C3 I enter "C"). In cell C4 I want to display the letter that has not been selected (which should be "B"). How do I go about setting this up without using multiple nested if statements? Thanks, |
How do I find data excluded from a list in excel?
Thank you. That was great.
"T. Valko" wrote: Based on your first description enter this array formula** in C8 and copy down to C10: =INDEX(B$2:B$10,SMALL(IF(ISNA(MATCH(B$2:B$10,C$2:C $7,0)),ROW(B$2:B$10)),ROWS(C$8:C8))-ROW(B$2)+1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Nguyen92002" wrote in message ... HI, Let say I have the following list in cells B2 to B10: "B2, B3, B4, B5, B6, B7, B8, B9, B10". After some manipulations, I have selected the following from the list:"B2, B3, B4, B8, B9, B10" in cells C2 to C7. I then would like to display in cells C8 to 11 the data that have not been selected in cells C2 to C7. How do I go about setting this up without using multiple nested if statements? Or a simplified version of the above: Let say I have the letters "A" in cell B2, "B" in cell B3, and "C" in cell B4. In cell C2 and C3 I enter two of the letters. (For example, I enter in cell C2 the letter "A" and in cell C3 I enter "C"). In cell C4 I want to display the letter that has not been selected (which should be "B"). How do I go about setting this up without using multiple nested if statements? Thanks, |
How do I find data excluded from a list in excel?
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Nguyen92002" wrote in message ... Thank you. That was great. "T. Valko" wrote: Based on your first description enter this array formula** in C8 and copy down to C10: =INDEX(B$2:B$10,SMALL(IF(ISNA(MATCH(B$2:B$10,C$2:C $7,0)),ROW(B$2:B$10)),ROWS(C$8:C8))-ROW(B$2)+1) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "Nguyen92002" wrote in message ... HI, Let say I have the following list in cells B2 to B10: "B2, B3, B4, B5, B6, B7, B8, B9, B10". After some manipulations, I have selected the following from the list:"B2, B3, B4, B8, B9, B10" in cells C2 to C7. I then would like to display in cells C8 to 11 the data that have not been selected in cells C2 to C7. How do I go about setting this up without using multiple nested if statements? Or a simplified version of the above: Let say I have the letters "A" in cell B2, "B" in cell B3, and "C" in cell B4. In cell C2 and C3 I enter two of the letters. (For example, I enter in cell C2 the letter "A" and in cell C3 I enter "C"). In cell C4 I want to display the letter that has not been selected (which should be "B"). How do I go about setting this up without using multiple nested if statements? Thanks, |
All times are GMT +1. The time now is 02:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com