1

I have an excel sheet as shown in the picture

Excel sheet snippet

I want to get the years where there is a x symbol for each row.

BigBen
  • 46,229
  • 7
  • 24
  • 40
Shreya
  • 159
  • 1
  • 9
  • You can query that table and filter out anything that doesn't have an X – Mark S. Feb 04 '20 at 13:56
  • Can you please clarify what the output should look like? Is it a new row with the year, eg. "1920" if any of the rows have an "x"? Or are you looking for one cell listing the years that have an "x", e.g. cell value = "1920,1921,1928"? – Jormund Feb 04 '20 at 13:59
  • The second one- All the values that have x in it – Shreya Feb 04 '20 at 14:00
  • Basically the textjoin function can do the work but it does not seem to work in 2016 version – Shreya Feb 04 '20 at 14:34
  • What formula did you try and what error did you get? – Scott Craner Feb 04 '20 at 14:37
  • I tried =TEXTJOIN(" ",TRUE,C2:BL2) and its giving a #NAME? error – Shreya Feb 04 '20 at 14:40
  • textjoin is only available in 2016 if an office 365 description exists. have a look at https://trumpexcel.com/concatenate-excel-ranges/ they include an example how to build a custom concatenate function using VBA. it should be possible to add the required condition (i.e. "x" in the specific column). – Jormund Feb 04 '20 at 14:40
  • see here for a TEXTJOIN UDF: https://stackoverflow.com/questions/39532189/vlookup-with-multiple-criteria-returning-values-in-one-cell – Scott Craner Feb 04 '20 at 14:51
  • or this more specific answer: https://stackoverflow.com/questions/43104790/concatenate-column-headers-if-value-in-rows-below-is-non-blank – Scott Craner Feb 04 '20 at 14:53
  • Without native TEXTJOIN you will need vba. – Scott Craner Feb 04 '20 at 14:54

1 Answers1

0

A workaround which will not be as "clean" as using textjoin or a custom function would be using some additional space in the spreadsheet and first preparing the dates and then joining them via a regular concatenate function

basis example

Formula in Cell B4 would be 

=IF(B2="x",B1&" ","")

Basically the x is replaced with the date and the seperator you want (here just blank)

Formula in Cell H4 would be 

=CONCATENATE(B4,C4,D4,E4,F4)

Here you join the dates (unfortunately range selection like B4:F4 is not possible).

It is a little bit "ugly" but will get the job done.

Jormund
  • 203
  • 2
  • 14