*Yogi Anand, D.Eng, P.E.*Google Spreadsheet Post #659 Jul 27, 2012

*www.energyefficientbuild.com*.

*user Jabus said: (http://productforums.google.com/forum/#!category-topic/docs/spreadsheets/9naciXcSJcY)*

*Spreadsheet - How to Compare Two String Columns and Add New Rows From Forms?*

*Hi there,*

*I'm creating a bit of an awkward spreadsheet, it's going to end up having 2 columns, 1 with the name of the product and the second with the words add, remove or disregard. An example of the spreadsheet:*

*A B*

*Printer || Add 1*

Printer || Add 1

Printer || Add 1

*Printer || Remove 1*

*Computer || Add 1*

*Computer || Add 1*

*Computer || Add 1*

*Computer || Remove 1*

*I want to get a formula that will compare grab the name of the product in column A and then count how many "Add 1"'s there are and subtract how many "Remove 1"'s. So the end result should show a total tally of Printers = 1 and Computers = 2*

*Note the following may hurt to read: I feel like I'm going about this a really long way around but I don't recall seeing a function, or perhaps my brain just is over complicating things. Because what I ended up doing is creating 4 new columns the first being an IF statement that generates a 1 if column B says "Add 1" and a 5 (random number) if false. Then the same thing for the second column (colmn D now) where I do an IF statement to see if the item is a Printer or not giving 1 or 0 as the true or false. Then my third column does an IF to check it column C is a 1 and column D is a 1 then that amount of add printers is 1 (which i then sum) then same thing for removing printers but I use the the 5 in the column C to add to the 1 from column D (confirming its a printer) and so that if 5+1 = 6 then it's a 1 for the remove column and then sum that up then the the sum of column e is subtracted from d to get the right answers.*

*But that's a horrible solution, anyone have an idea on how I can compare the two columns? and then have new formulas form as people respond to a form? I feel like I'm doing such a poor job of this.*

--------------------------------------------------------------------------------------------

let us have a look at the following solution to the problem

## No comments:

## Post a Comment