Google Sheets – Consolidating Data from Multiple Columns

google sheets

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.

Best Answer

Short answer

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

  1. Clear B2:B
  2. Add the following formula to B2
=ArrayFormula(TRANSPOSE(REGEXREPLACE(REGEXREPLACE(TRIM(
QUERY(TRANSPOSE(IF(C3:F12="Have",C2:F2,)),,2000000)
),": ",", "),":","")))

Explanation

  • 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.
Related Topic