Listing many unique values based on an if statement

[MCoev]

Ok, I am not sure how best to describe this, so I have attached an example excel file with a sample of what I am trying to do.

My file has a sheet with raw data collected during surveys at various businesses, the sheet is called RawData in this case. The OutputReport is a speadsheet which we are using to create reports for each inividual business where the surveys were done based only on the surveys from their store (the surveys were entered all in one sheet since we also want to be able to create overall reports for the whole commercial district).

My question is if there is a way to list, without repetition, the reasons that people listed for visiting the store "World's Away". I tried using an IF statement in an array, however it only finds the reason the first person listed, and I can't get it to show any of the other participants' reasons, or the reasons for any of the other stores.

My desired results for the OutputReport would look something like:

Why choose this business?
Convenient and store owner rocks!
Regular business/purchases/second home
Product/price
One of my favourite stores


Thank you,
Michael

[tigeravatar]

MCoev,

Welcome to the forum!

Attached is a modified version of your example workbook. I created a data validation list for the business names in cell B13 of the OutputReport sheet. Then in cell B15 is this formula:

Please Login or Register  to view this content.

It is copied down for 15 rows. Just choose the business name in B13 and the formula will do the rest. Is something like that what you were looking for?

[GeneralDisarray]

Hello,

THis is a great pivot table application. If I were you, would create a named range (in the attached example go to formula tab > name manager to see where you define them).

I made the range, RawDataTable defined by =RawData!$A$1:INDEX(RawData!$1:$1048576,COUNTA(RawData!$A:$A),3)

Then, i inserted a pivot table which referenced it (see what that looks like in Output REport tab of the attachment.

[MCoev]

Thank you very much for the fast replies! I am not sure which I will use yet, but they both do exactly what I wanted!

Thanks again!

[MCoev]

I have a follow up question!

I ended up using the Pivot Table since it lets me count as well as select different values to display or hide. For one of the questions I asked people to list other businesses they were visiting, so account for people listing more than one other business I had four columns (fields) to enter other businesses in. Is there a way to list the values on those four columns as just one field in a pivot table?

[GeneralDisarray]

yes, can you upload an example again? can try to make it happen for you.

[MCoev]

Sorry about the delay, I was off for a bit on Christmas holidays! Back now though!

I would like a pivot table, sorted by Store name in column B (only showing values for "World's Away"), with all the values under the columns "Other Stores1", "Other Stores2", "Other Stores3" to be listed as one field. (By that I mean Store2 is not a subsidiary of Store1, the values in all three columns are all to be counted equally). I would like to be able to count the number of responses in total across the three rows. Any suggestions no how to do this?

Any help would be greatly appreciated, thank you!

Result I am looking for would be something like:

World's Away

Other Stores Visited:
Coffee Shop	3
Book Shop	2
Restaurant	2
Used Furniture Store	1
Other Comic Book Stores	1