+ Reply to Thread
Results 1 to 19 of 19

Group responses from repeating samples

  1. #1
    Registered User
    Join Date
    06-01-2015
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    9

    Group responses from repeating samples

    Hi there guys i really hope you can help me with this problem!
    I dont think its that difficult to solve but i just cant wrap my head around it.

    I have a large dataset from an experiment and I need to perform some statics analysis, but to do that I need to group the data by the samples.

    The experiment is a sensory evaluation with 6 samples rated in 5 categories by a total of 64 people. So the sample numbers are repeating. The first column is the sample number, and next ones are scores from the different categories.
    excelhelp.PNG

    I want to group the data so I got 1 column for each sample and the responses for a category underneath like this:
    excel goal.PNG

    Then i can just make a sheet for each response.

    Is there any macro that could copy the value of cell to the right of any cell with value '211' then i could paste it all in one column. Then i could do that for each of the sample numbers.

  2. #2
    Registered User
    Join Date
    06-01-2015
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    9

    Re: Group responses from repeating samples

    An easy way to select the data would be to search for a value (for example 211) and select the cell to the right of this.
    Is there a macro or function that does that?

  3. #3
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Group responses from repeating samples

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  4. #4
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: Group responses from repeating samples

    Can you post you example workbook instead the image? Click "Go Advanced" utton and find paperclip image button to attach your file


    regards

  5. #5
    Registered User
    Join Date
    06-01-2015
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    9

    Re: Group responses from repeating samples

    I think the easiest way to select and copy the data would be to make a macro, but im not used to work in VBA.
    Here's what I need the macro to do:
    1) Search and select all cells with a value (for example '211')
    2) Select all the cells one step to the right (offset (0,1))

  6. #6
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Group responses from repeating samples

    from what you have just described it doesnt even sound like you need a macro
    you could just do it with a CSE formula but without a workbook just shooting in the dark and making assumptions

  7. #7
    Registered User
    Join Date
    06-01-2015
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    9

    Re: Group responses from repeating samples

    Like this?

    So the data is grouped like seen on the left, for each sample the participants of the study have rated it 1-7 in several categories.
    I want the data to appear as on the right. A column for each sample and all the responses for one particular category underneath.

    So the macro should search for a value in 1 column and select the cell to the right of it. That way i can select all answers in a specific category that particular sample.
    Attached Images Attached Images

  8. #8
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Group responses from repeating samples

    we are requesting you upload a workbook so we have something to work with
    we cannot edit pictures
    nor do we want to data entry something to have something similar to what you might have

  9. #9
    Registered User
    Join Date
    06-01-2015
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    9

    Re: Group responses from repeating samples

    Allright i hope this is what you mean?
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Group responses from repeating samples

    how do you want the list values returned ?

    down column B first the column H or B and H at the same time?

  11. #11
    Registered User
    Join Date
    06-01-2015
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    9

    Re: Group responses from repeating samples

    Sample ID's are in column B & H. And i need all responses for a particular category and sample within one column.
    So all responses for Overall Likeness for sample ID 211 in one column etc.

    A macro where I can put in the sample ID and the macro will select offset cell to the right would be perfect, since i can just adjust the macro to what column and offset i need it to be.

  12. #12
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Group responses from repeating samples

    if you are going to use vba there is no need to "offset"
    you can do it within code
    Please Login or Register  to view this content.
    see if this code works for you
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    06-01-2015
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    9

    Re: Group responses from repeating samples

    Thanks mate!
    Im very new to VBA so probably doing some rookie mistake here, but the makro doesnt do anything when I run it.
    Are there some values I need to costumize? Something i have to fill in?

  14. #14
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Group responses from repeating samples

    i saved the file with results already in and as it is running the same data the results are the same
    delete O2:T27 then run the macro to see if anything happens

  15. #15
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Group responses from repeating samples

    if you want to see slow motion what the code is doing
    highlight code
    Please Login or Register  to view this content.
    and press F9 then it will stop at that line after which you can press f8 to follow what the code is doing

    it currently only returns results based on the headers in O1:T1
    211 247 254 369 563 657

    if you want to switch it up
    i can change the code to read the headers instead
    let me know if this is what you want

  16. #16
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Group responses from repeating samples

    Please Login or Register  to view this content.
    this is the code if you want to do by headers in O1:t1
    you can add as many cases as you like, change to your needs
    if you do either..change the clearcontents range as well otherwise it could append results

  17. #17
    Registered User
    Join Date
    06-01-2015
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    9

    Re: Group responses from repeating samples

    Ah yeah i got it working now!
    What if i want it return values from the entire sheet1 and output in sheet 2?

  18. #18
    Registered User
    Join Date
    06-01-2015
    Location
    New Zealand
    MS-Off Ver
    2013
    Posts
    9

    Re: Group responses from repeating samples

    whoops double posting

  19. #19
    Forum Expert
    Join Date
    08-12-2012
    Location
    Sydney, Australia
    MS-Off Ver
    Excel 2010
    Posts
    5,636

    Re: Group responses from repeating samples

    depends on how your entire sheet is listed
    if your talking just extra rows then the code already works for that
    if your talking extra columns then as long as there is 6 columns for each then make the "k" loop larger

    now if you want to output into seperate sheet
    you would need to rework the code for the each case value to be checking on the separate sheet and outputting to the separate sheet

    see attached sheet

    i left the output into columns O-T
    change as necessary
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to create scattered plot with repeating values in the same group?
    By needhelp13 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-20-2014, 02:51 PM
  2. Excel 2007 : Automatically sort samples by Group
    By R.A.Mat in forum Excel General
    Replies: 0
    Last Post: 01-16-2011, 08:25 PM
  3. creating a repeating formula that doesn't show repeating values
    By cybershot in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 12-09-2010, 06:12 PM
  4. Determining Positive responses, Tally them then group them
    By Mwanj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-22-2009, 06:45 AM
  5. Group Repeating Text Values in a Column?
    By ConfusedNHouston in forum Excel General
    Replies: 1
    Last Post: 04-25-2006, 11:10 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1