Complex: Analysing data with uncertain column locations

[NicholasMan]

Hi everyone!

So it's my first week using excel and I've taken on work way above my knowledge level, hoping someone might be able to help me out. Basically I am creating an excel file which analyses data and produces stacked bar charts from this. Unfortunately I cannot manipulate the way the data is organised/labeled in excel.

The data is inputed into sheet 1 in the following format.


Item type........Do you like apples?.........Do you like pink?.........Do you like red?
Item....................Agree...............................Disagree...................Don't know
Item....................Disagree...........................Don't Know.................Agree
Item....................Don't Know........................Agree.......................Don't Know
Item....................Neutral..............................Agree.......................Neutral




From this I basically want to make a stacked bar chart of people who agree/disagree/don't know/neutral to each of the questions (Do you like apples, etc). Presently I have been using the following formula (this formula is for the "Do you like apples question"):

=COUNTIF(Sheet1!B1:B6,"agree")*100/(COUNTIF(Sheet1!A1:A999,"item"))

=COUNTIF(Sheet1!B1:B6,"Disagree")*100/(COUNTIF(Sheet1!A1:A999,"item"))

=COUNTIF(Sheet1!B1:B6,"Don't Know")*100/(COUNTIF(Sheet1!A1:A999,"item"))

=COUNTIF(Sheet1!B1:B6,"Neutral")*100/(COUNTIF(Sheet1!A1:A999,"item"))


from this I get a set of percentage values which I use to make the stacked bar chart. This is all well and good however I have just been informed that the ordering of the questions will change every now and again (so the apple question is no longer in B1, and might start in C1 or B3 or D2) and as this is an analysis they will run several times a year I need it to be smart enough to search for the question not the cell range. So now I am thinking I need some kind of formula to search for the question (Do you like apples?) within the spreadsheet rather than just specify where I imagine the question will be when the data is imported in.

Many of you are probably cringing at my rudimentary approach, so I apoligise if my formula is burning your eyes but I would be the most thankful person alive if somehow could help me out.

Thanks!

[mrice]

Suggest using a user defined function.

Please Login or Register  to view this content.

Open the VBA editor by hitting Alt F11.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.

In a separate column, enter = GetPercentage(1:1,A:A,"Do you like apples?","Agree")

Remember to save the workbook as a macro enabled workbook .xlsm

[NicholasMan]

Wow that is amazing! Thanks so much, do you know how I might be able to put the =GetPercentage(1:1,A:A,"Do you like apples?","Agree") on sheet 2 but have the forumla search sheet 1?

Also, am I correct in believing that if I change the "(1:1, A:A...." to "(1:999, A:A...." it will search every row from 1 to 500 for "Do you like apples?"

Thanks again.

[mrice]

To operate from a different sheet, the function needs to be defined more generically.

Please Login or Register  to view this content.

To search in just the first 500 column headings on Sheet1, the formula would need to be...

= GetPercentage(Sheet1!A:SF,Sheet1!A:A,"Do you like apples?","Agree")

[NicholasMan]

Thanks again for that, it's all working great now. I have another question though if you wouldn 't mind.

The dataset also has an open ended question ("Tell me in 50 words why you love apples"). Similar to my previous question the column containing the question and data for the "Tell me in 50 words why you love apples" question could appear anyway in sheet1. How do I display all the answers to this question in a seperate sheet?

Thanks!

[mrice]

Sorry but I don't understand this second question.

[NicholasMan]

So basically there is an open ended question ("Tell me in 50 words why you love apples") and I want to display all of the answers to this question on Sheet2. Unfortuently the location of the ("Tell me in 50 words why you love apples") question changes quite often in sheet 1. I'm not looking to calculate anything, just a more simple transfer of the responses people gave to another sheet.

For example if on sheet1 there is the following question and responses:

Why do you like apples?

• I love apples because they are so sweet and juicy
• I like apples most due to their health benefits
• Because they are red
• I like how they are crisp

How do I get Excel to search for the question and copying the responses across to sheet 2.

Sorry if this isn't making sense, let me know if it isnt and I'll draw up what I mean in a workbook.

Thanks so much, you're make a project of mine so much simpler.

[mrice]

An example workbook would be good.

[NicholasMan]

Example Book with Open ended question.xlsm


I have attached a workbook and put some instructions in it on Sheet1 and Sheet2. It will probably look really simple when you open it lol. Thanks again!

[mrice]

Sorry for the delay in replying - holidays....

Try pasting the following into Sheet2 tab in the VBA editor Alt F11

Please Login or Register  to view this content.

Remember to save the workbook as .xlsm format.

[NicholasMan]

Hi again Martin, hope you enjoyed the holiday break

The code you gave me works and is exactly what I was after, however it only updates the field on Sheet2 when I highlight the code and click run in the VBA editor. Is there someway to make it update the field in sheet2 automatically when responses are added into the space below the question on Sheet1?

[mrice]

The code should fire every time Sheet2 is activated. Try adding a breakpoint on the first line of the code and then toggle between sheets 1 & 2 to see if it is firing.