I have a "Have"/Want" checklist for collectible trading/game cards. I would like to convert the "Have" and "Want" entries into consolidated data in a new column, but using different text labels than the actual "Have" and "Want" entries of the checklist columns.

For example: "Have" in Column C should results in "Normal Card" being added into Column B. "Have" in Column D should results in "Foil Variant" being added into Column B... etc. Any other words should be ignored.

The best way to explain it better is to just show you with this editable test mock-up sheet. https://docs.google.com/spreadsheets/d/1bOkYDvBM-TOW4Ix7iaWc6aG1HoEJQzIOj-qmuTHQ81w/edit?usp=sharing

It's explained and shown much more clearly in the test sheet. There are two sheet tabs. One is the desired affect I am going for with the explanations. The other is for editing and adding formulas, etc.

I would normally provide something more to work from and I apologize for not. But I just have no idea where to even start with this one. Any help on at least how to get started would be great.

  • 1
    I think your question would be better if you could include some sample data in the question itself. A link to a sample sheet is great, but if it's ever deleted or access revoked, a large portion of the useful information in this question will be lost, and thus less useful for a future reader with the same problem. We like for questions to be self-contained. – ale Nov 1 '16 at 13:53
  • Good suggestion. I'll try to update it with some sample data soon so others can get more from the solution without having to rely on external factors. – Computer Candy Nov 1 '16 at 19:13

Short answer

On a copy of the sheet Desired example, try the following:

  1. Clear B2:B
  2. Add the following formula to B2
),": ",", "),":","")))


  • IF is used to replace cell values Have by the column header (row 2), other values are replaced by blanks.
  • QUERY & transpose are used to concatenate the row values. This adds a space as separator.
  • TRIMS replaces the consecutive spaces by a single one.
  • The first REGEXREPLACE replaces : by , , the second to eliminate the remainding :
  • ARRAYFORMULA returns an array.
  • This is a pretty elegant solution. Thank you. It's also a great solution if I need to add more columns! This is the solution I chose and there's no need to add more. However, if you're up to it, is there an easy way to integrate adding support for both "Have" as well as "Have¹"? Though an 'OR'? I forgot that I use the superscript ¹ (1) when there is something to note about the card. Or perhaps there is a way to use a wild card for anything after "Have"* in case of a superscript 2, etc? – Computer Candy Nov 1 '16 at 4:00
  • Actually, if there's a way to used a wild card for anything after "Have"*, that would be perfect in case of a superscript 2, 3, etc? Thank you again! – Computer Candy Nov 1 '16 at 4:10
  • There is no need of OR or to use a wilcard if you use LEFT; instead of C3:F12="Have" try LEFT(C3:F12,4)="Have". – Rubén Nov 1 '16 at 5:07
  • That did it. Thank you so much! – Computer Candy Nov 1 '16 at 19:12

Please try (I took out the colons from the labels for convenience):

=iferror(left(if(C3="HAVE",C$2&", ","")&if(D3="HAVE",D$2&", ","")&if(E3="HAVE",E$2&", ","")&if(F3="HAVE",F$2&", ",""),len(if(C3="HAVE",C$2&", ","")&if(D3="HAVE",D$2&", ","")&if(E3="HAVE",E$2&", ","")&if(F3="HAVE",F$2&", ",""))-2),"")

This strings the components to suit, followed by ,, the last of which is then removed by reducing the length overall, and then the formula is wrapped in iferror for those cases where there is no length to reduce.

  • 1
    This works pretty well. It just gets a little long as I add more columns. But it does work if I remove the colons. Thank you. – Computer Candy Nov 1 '16 at 3:22

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.