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    Mar 10, 2013
user meph2u :(!category-topic/docs/spreadsheets/N1OnKIx6-lY)
Finding common values in a variable number of columns

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 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