Concatenate ifs

[karl.igniter]

Hi All,
I am struggling to develop a formula that will enable me to concatenate only cells that have content and that meet a specific criteria.

I have a spreadsheet of data that contain a rows of records each of which contain a location code; e.g. ABB, ELX, NP. In this spreadsheet there is a comments column - not all rows have a comment.

On a separate worksheet have a drop down menu of these location codes, which populates a table.

One of the cells of the table has to concatenate any comments for that particular location separated by a new line in the same cell. Adjacent to this column is another column containing the counta formula which returns a "1" if the comments field has been populated

My current attempt which has not worked is:

{=IF(location code=drop down menu,IF(CountAcolumn=1,Concatenate(comments),),)}

The best results I get with this is a 0.

Any suggestions?

[FDibbins]

Hi and welcome to the forum

When you say "comments" do you mean that some cells have comments attached to them (as in - right-click the cell, select "insert comments") or is it just a column with data in it that you have called comments?

i would suggest you upload a sample work book, (all sensitive data removed), showing what data you are working with, a few examples of what your expected outcome is, and how you arrived at that
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[karl.igniter]

When you say "comments" do you mean that some cells have comments attached to them (as in - right-click the cell, select "insert comments") or is it just a column with data in it that you have called comments?

A column with data in it. I have attached a (very sanitised) version of the spreadsheet I am working on.

[FDibbins]

Just so it's clear, if you selected ELX, then the answer you expect in F9 would be...

Taking the full day off prior to BZ testing 24/2/12, Extra permit areas for pole 3, make Jason availble for this Concern over achieving work with five DS's in one day, either allocate more men or extend the working day. Concern over achieving work with five DS's in one day, either allocate more men or extend the working day. Judge weather conditions on day if necessary defer work

Assuming that is what you want, try this...

add a helper column on sheet1 (I used AU, use whichever 1 you want to). Then copy this down in that column as far as you need it...
=IF(SUM(A9:AJ9)=0,"",IF(F8<>Stats!$E$4,AU9,IF(R8="",AU9,AU9&IF(AU9="","","…")&R8)))

then in sheet2, F9, either use...
=INDEX('Planning Data'!AU:AU,MATCH(Stats!$E$4,'Planning Data'!$F:$F,0),1)
or just ='Planning Data'!AU7

Hope that helps

[karl.igniter]

Hi there,
Many thanks, it works. But I cannot understand why the formula =IF(SUM(A9:AJ9)=0,"",IF(F8<>Stats!$E$4,AU9,IF(R8="",AU9,AU9&IF(AU9="","","…")&R8)))
would reference row 9 when the data starts on row 7.

Can you explain please?

Cheers

[karl.igniter]

Hey FDibbins - after a bit of tinkering around, I got this to work. Many thanks for your help