Hello, I am trying to figure out a formula that will group together cell responses. I am using Excel 2010 and I have column1 with Categories 1,2,3,4,5 and 6. Column2 has the responses A,B,C, and D from a pull-down list (A,B,C, and D are all defined in cells elsewhere in the spreadsheet and then referenced for the data validation drop-down list). Lets say the responses are 1-A,2-A,3-B,4-D,5-A,6-B. I want a formula in a different cell that will give me "1,2 and 5 are A; 3 and 6 are B; 4 is D" maybe not that cleanly but as close as possible to that. I do NOT want answers that use VBA or macros, even if it is not possible without them. I would just like to know if there is any way at all this could be done using any combination of formulas. Also, none of what I have written here can be changed. This is the way the spreadsheet is set up.

My first attempt was something similar to IF(OR(ROW1=Cell with info for Choice A,ROW2=Cell with info for Choice A,...,ROW6=Ditto),CONCATENATE(*Something that only takes the answers that were true in the prior OR statement and concatenates them with ROW1, ROW2 and ROW5 are A*),IF(OR(Remaining cells that were false before=Cell with info for Choice B.... And so on.

If the thing above didn't make sense, hopefully this helps... Basically, an if statement to see which categories are equal to A, if true then concatenate those to be so and so are choice A, if not then another if that gets the ones which weren't equal to choice A and checks if they are equal to choice B and then if true concatenates those to be so and so are choice B and so on for every answer choice (C and D remain).

I could be completely wrong in the way I'm going about this so please any help to get me what I am looking for would be greatly appreciated! And if this is not possible in any way at all without a VBA, then please let me know! Thank you so much!