I am trying to figure out how to return values that are not contained in a range. For example, in my attached sheet if I have a range of 1 through 8 in cells A1:H1, in cells A2:D2 I have values, 7, 3, 2, 6.
How do I return 1, 4, 5, 8 in cells A4:D4? (The numbers of 1 through 8 that are not contained in the cells A4:D4)
The values do not have to be returned in consecutive order in A4:D4.
They will not likely ever be consecutive in A2:D2
They will always be consecutive in A1:H1
Thanks
In A4:
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Please Login or Register to view this content.
Drag accross
Quang PT
That was entirely too fast of an answer for something I spent over an hour trying to solve!
It's perfect! Thank you so much for solving my problem!
That was entirely too fast of an answer for something I spent over an hour trying to solve!
It's perfect! Thank you so much for solving my problem!
I essentially came up with a similar solution, but I see you already have you answer..
Only change is, even if you add another number to your Row 2, it will take care of the same.
Anyways,
=IFERROR(INDEX($A$1:$H$1,1,SMALL(IF(COUNTIF($A$2:$H$2,$A$1:$H$1)=0,COLUMN($A$1:$H$1),""),COLUMNS($A$4:A4))),"")
Use Ctrl+Shift+Enter
Cheers!
Last edited by NeedForExcel; 11-10-2015 at 02:00 AM.
Cheers!
Deep Dave
Another way. Array-entered in A4 an filled across.Array formulas are not committed in the regular way. You commit by pressing and holding Ctrl + Shift as you hit Enter. You will know that it has been entered successfully when you can see curly braces {} around your formula in the formula bar. You do not type these in yourself. Excel does it for you.Formula:
Please Login or Register to view this content.
Dave
Thanks for your kind words.Originally Posted by Kerik
It is a typical question and it does not take much time to get around and to build a formula.
I essentially came up with a similar solution, but I see you already have you answer..
Only change is, even if you add another number to your Row 2, it will take care of the same.
Anyways,
=IFERROR(INDEX($A$1:$H$1,1,SMALL(IF(COUNTIF($A$2:$H$2,$A$1:$H$1)=0,COLUMN($A$1:$H$1),""),COLUMNS($A$4:A4))),"")
Use Ctrl+Shift+Enter
Cheers!
This will also come in handy, I will likely use it for a different spreadsheet. Thanks for providing it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks