0

Essentially I have 4 columns of values I'm pulling from.

A: "Old # of Cards"

B: "Old Std Pack Qty"

C: "New # of Cards"

D: "New Std Pack Qty"

In a separate column E, I comment what changes need to be made.

If all values stay the same then I write "Keep Same"

If the A > C "Delete X card(s)" or if A < C "Add X card(s)" where X = A-C or C-A

If B ≠ D "Change Std Pack to Y" where Y = B-D or D-B

Also if A ≠ C and B ≠ D then "Add/Delete X Card(s); Change Std Pack to Y"

I almost had it figured out with a bunch of nested if(and's but I'm struggling with getting that "X" variable between the "Add/Delete" and the "card(s)". Also not sure if it's possible to pluralize the "card" if > 1. I'm sure there may be an easier way that doesn't involve the if formulas but I'm not sure what that would be.

Here is what my table would look like:

Card Changes

2
  • Welcome to SuperUser! What happens if A=C but B ≠ D? – Reddy Lutonadio Aug 28 '20 at 15:17
  • Are you asking for how to concatenate a variable with text? So if X is a formula or cell reference it would be "ADD " & X & " Card(s)" – Forward Ed Aug 28 '20 at 16:11
1

Assuming your table is in A1 to E7.

Place the following formula in E2 and copy down

=IF(AND(A2=C2,B2=D2),"Keep Same",IF(A2<>C2,IF(A2>C2,"Delete ","Add ")&ABS(A2-C2)&" card"&IF(ABS(A2-C2)<>1,"s",""),"")&IF(AND(A2<>C2,B2<>D2),"; ","")&IF(B2<>D2,"Change Std Pack to "&D2,""))

Note the following part from the formula above is optional. It is just taking care of placing the s on the end of card when there is more than 1 card being added or deleted.

&IF(ABS(A2-C2)<>1,"s","")

POC

3
  • That worked perfectly! I had a complete brain fart on absolute value... I tried googling "make solution positive" to no avail.. So if I'm looking at this correctly, "&" is used to tie the text strings / formulas together? – Nate Smith Aug 28 '20 at 17:21
  • & can tie String and (formula, string, numbers, or cell content ie A1). It is the short form for the formula CONCATENATE. Of note is the & does not generate a space so you need to include it at the end/start of your string, or potentially in between like A1&" "&B2. – Forward Ed Aug 28 '20 at 23:51
  • BTW the result of any concatenation results in a string. 1&1=11 The 11 is a string and not a number. – Forward Ed Aug 29 '20 at 11:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.