Return a list of comments from a range that contains blank cells

[AmieDa]

Hi All,

I have survey results that come out like the attached. I need to produce a report from this and would like to have a table by each area. Eg. Alignment to Outcomes and list the comments without the blank cells, and then the "agree, diagree etc... "

I tried using the below, but it doesn't work with blank cells, and a lot of the comments are blank.
=INDEX(Tbl_data[Alignment To Outcomes Comments],MATCH(0,COUNTIF(Comments!$D$6:D6,Tbl_data[Alignment To Outcomes Comments]),0))
[TABLE="width: 689"]
<tbody>[TR]
[TD]Can anyone help please?
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Link attached to a sample file. Hope this works.

https://1drv.ms/f/s!AlG9MGLoWUezmmwNCrOooVdGFAdP

 

[sandy666]

something like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#F79646]Alignment To Outcomes[/td][td=bgcolor:#F79646]Alignment To Outcomes Comments[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FDE9D9]Agree[/td][td=bgcolor:#FDE9D9]comment here[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Agree[/td][td]comment comment[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FDE9D9]Disagree[/td][td=bgcolor:#FDE9D9]another comment[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Disagree[/td][td]need to list comment[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FDE9D9]Neither Agree nor Disagree[/td][td=bgcolor:#FDE9D9]commenting again[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Strongly Disagree[/td][td]comments[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#F79646]Information and Support[/td][td=bgcolor:#F79646]Information and Support Comments[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FDE9D9]Agree[/td][td=bgcolor:#FDE9D9]comment here[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Agree[/td][td]comments[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#FDE9D9]Disagree[/td][td=bgcolor:#FDE9D9]another comment[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Strongly Disagree[/td][td]comment comment[/td][/tr]
[/table]

because e.g. Agree has two comments they are in two different rows or if you want it can be in single row separated by comma(s). It's up to you
btw. this is a PowerQuery so if you are able to use PQ I'll post a M-code

 

[AmieDa]

Thank you, yes that is exactly what I am looking for. But I don't have access to power query unfortunately. on the work computer and I can't download anything without IT permission.

 

[sandy666]

That's pity.
Anyway, have a nice day

btw. only 2010 / 2013 need PowerQuery add-in. In higher versions PowerQuery aka Get&Transform is built-in

 

[AmieDa]

Thank you for trying We have Professional Plus 2010 at work. But I'd have to go through IT to get the add-on loaded and a list of approvals from managers etc.. etc.. might be something looking into for the new year. Hopefully they upgrade soon.

 

[sandy666]

just in case you can install PowerQuery here is your example excel file

 

[sandy666]

but you can do that with PivotTable
select first two columns, insert PivotTable, add both fields to Row Area and filter second column by blank
the same with second two columns

don't select whole table

file is here

 

[AmieDa]

but you can do that with PivotTable
select first two columns, insert PivotTable, add both fields to Row Area and filter second column by blank
the same with second two columns

don't select whole table

file is here

Thank you. I didn't even think of that. That will work perfectly. Thanx for your help.

 

[sandy666]

You are welcome