Monday, March 11, 2013

yogi_Extract in Column A All The Colors That Are Common To Columns From B To K


                                          Google Spreadsheet   Post  #1080
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 10, 2013
user meph2u :(http://productforums.google.com/forum/?zx=tc5oxst1kk1#!category-topic/docs/spreadsheets/N1OnKIx6-lY)
Finding common values in a variable number of columns
https://docs.google.com/spreadsheet/ccc?key=0Atrb4wVf_dsSdDFzQjBHR05KWFlTRUNJTk81TlJaaXc#gid=0

I am looking for a formula for cell A2 (I don't have one or an idea how to do this) that will:

Look at the lists of colors in Columns B- K, and return a list of colors for which there is a corresponding entry in all columns B-K that have data (ignoring those columns that don't have data).   Sometimes only 2 columns will be used....it is variable up to 10.   The list of colors in a column (should it exist) will always start in row 2.

I believe the data in A2-A4 is the answer that would be produced from the sample lists in the spreadsheet

Thanks in advance

---------------------------------------------------------------------------------------------------------------------------
thanks to very fine contribution from our Top Contributor Hyde in the related thread in Google Docs Help Forum, I was inspired to create a single cell solution here in sheet yogi_Sheet2_singleCellFormula