promotion image of download ymail app
Promoted

subtract column A (names) from column B (names) in excel?

If

Column A = apple, ball, cat

Column B = cat, dog, fish

expected result

Column C = apple, ball

what would be the formula?

If A1 is found in B:B, no entry would be made in C

if A2 is not found in B:B, A2 value would be displayed in C

i have thousands of values in each column, so i want an automated formula, where i do not have to type the value of every row to get results.

Thanks in advance!

2 Answers

Relevance
  • 7 years ago
    Favorite Answer

    sure,

    copy&paste this in C1

    =IF(A1="","",IF(COUNTIF(B:B, A1)>0,"",A1))

    fill the formula down as a bunch of extra rows (thats why i put the extra A1="","".....it keeps the column looking clean for the test.....in the real sheet, you dont have to go down the extra rows.)

    now you can add words in column A and Column B, and you should see Column C automatically work.

    you can turn on autofilter for column C, that will hide all the blank rows, and you can copy a simple list to a new file if you want.

    let me know how it works

    • Commenter avatarLogin to reply the answers
  • 3 years ago

    you may would desire to purely do a team of ifs. Synatx of If: =IF (concern, [value IF genuine] , [value IF fake]) For this occasion you will finally end up having to embed greater ifs interior the value if fake. =IF(Sheet1!A1="call", [Corrosponding quantity in different column] , [ IF(Sheet1!A2="call", [Corrosponding quantity in different column] , [ IF(Sheet1!A3="call", [Corrosponding quantity in different column] , [ IF(Sheet1!A4="call", [Corrosponding quantity in different column] , [ IF(Sheet1!A5="call", [Corrosponding quantity in different column] , [ repeat this format till you have examined each and every call. Then end with: IF(Sheet1!A(final row)="call", [Corrosponding quantity in different column] , [NULL]]]]]]]]]]]] reproduction-Paste (do no longer purely click-drag the backside sq.) into the cells of Column C. Then in Column D use the same technique to get the needed quantity to divide or subtract and do the math. the effect is relatively in Column D. this is plenty too complicated to have in only one cellular.

    • Commenter avatarLogin to reply the answers
Still have questions? Get your answers by asking now.