0

I have two lists: A 1 2 3 4 5 6 7

B 5 7 I want output C 1 2 3 4 6 in Excell ?? Any ideas?? Regullarly updating if i add 1 in b it should be removed from output table.

https://i.stack.imgur.com/lm37S.jpg https://i.stack.imgur.com/qjc0s.jpg

Ravi Garg
  • 1
  • 1
  • 2
    I’m voting to close this question because questions must demonstrate a reasonable amount of research & understanding of the problem being solved. Please edit to either a) clearly describe your problem and the research done so far to solve it or b) include attempted solutions plus why they didn't work. In either case, be sure your expected results are clearly presented. – Tetsujin Aug 16 '20 at 11:18
  • Hi @Ravi Garg ,, check my post now I've solved the issue,,, better you [edit] your post & up load the proper screen shot so readers can get idea all abut data you have,, and post the formula if you have tried so far ☺ – Rajesh Sinha Aug 16 '20 at 11:56
  • 1
    Does this answer your question? [Find Items in one column that are not in another column](https://superuser.com/questions/81498/find-items-in-one-column-that-are-not-in-another-column) – Reddy Lutonadio Aug 16 '20 at 12:02
  • What version of Excel? – Ron Rosenfeld Aug 17 '20 at 00:30

2 Answers2

1

This needs one Helper Column & combination of INDEX and MATCH, wrapped with IFERROR:

enter image description here

How it works:

  • Formula in cell P41:

    =IF(ISNA(VLOOKUP(M41,$N$41:$N$423,1,FALSE)),"Yes","")

  • An array (CSE) formula in cell O41:

    {=IFERROR(INDEX($M$41:$M$47, SMALL(IF(COUNTIF($P$41, $P$41:$P$47)*COUNTIF($P$41:$P$47,"<>"), ROW($M$41:$N$47)-MIN(ROW($M$41:$N$47))+1), ROW(A1)), COLUMN(A1)),"")}

N.B.

  • Finish an array formula with Ctrl+Shift+Enter & fill down.

  • For neatness later on you may hide Helper Column.

  • List in column O will change, as soon you add or even delete value in column N.

  • Adjust cell references in the formula as needed.

Rajesh Sinha
  • 8,995
  • 6
  • 15
  • 35
  • yes almost problem solved .. please edit this formula such that when i update value in table B the value in output table C is orderered as table B updates... Thanks a lot – Ravi Garg Aug 16 '20 at 12:09
  • @RaviGarg,, same formula will take care all about data editing in Colum B,, just add 3 in Col B,,, u find new list 1, 2, 4, 6, 7 in Col C,, or if U remove 7 from Col B,, get 1, 2, 3, 4, 6, 7. Hope this help U. Now if you find it's working for U ,, you may accept it as Answer as well Up vote also. ☺ – Rajesh Sinha Aug 16 '20 at 14:24
1

You could use:

=UNIQUE(IF(ISERROR(VLOOKUP(UNIQUE(B1:B20,0,TRUE),UNIQUE(A1:A20,0,TRUE),1,FALSE)),UNIQUE(B1:B20,0,TRUE),""),0,1)

instead.

Jeorje
  • 11
  • 1