Grouping Cells by response

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!

Here are the attached examples of what I am looking for. I hope this helps! I'm not sure why it attached twice.

Sure. The actual file has 5 other tabs in it already so I suppose whatever solution you come up with, I can hide in one of the other tabs as you suggested.

This looks amazing! I was certainly not expecting something that gave me EXACTLY what I needed. Although I don't need to understand the formula, the fact that I don't is making it hard to fix the cell references to be correct in my actual spreadsheet. And I feel bad for asking... But is there a way that you can do this for the sheet I have attached here? I can change the references to the sheet names in my actual file but I can't seem to get everything else correct.

If it gets a lot more complicated due to the changes I have suggested, that is fine. Is there a way that this can be done more easily without the extra tab needed? It is possible to hide it as mentioned but it would still not be ideal since there are so many rows and columns it takes up (I was under the impression it wouldn't take up as much real estate). And it doesn't need to be as perfect as Loganeb's response was (which again, was AWESOME). Thanks so much for the help!

Oh! And in this sheet, the drop down list options are from B294:B299 on the EXAMPLE sheet. This is just so the cell references match exactly in my actual spreadsheet. Sorry for not clarifying that!

For some reason it looks like the concatenation does not work for "will be available" and the "Money" and "Erasers" categories as well as for some other combinations in the file you attached, if you can help with these last few kinks then this would be great!

And please don't apologize! I'm the one who's being difficult and saying I don't want the macro or VBA (I just don't want to have to save it as a Macro-enabled workbook so I prefer anything without macros) so thank you very much for helping me and listening to my suggestions!

I am sorry but I am not sure I can figure these out quickly enough to try and fix the problem myself but I am sure your explanations will help in the future when I get a chance to look at them! I am extremely grateful for all your help Loganeb!

Haha I hate to be the bearer of bad news, but I changed up some of the drop down choices and it seemed to screw up again. But you've helped me enough! I will try this weekend to figure out the magic you did to make this wonderful tool and attempt to fix it myself or maybe sometime after that. But if I can't fix it... You may hear from me again lol

Have a great night! =)

Thanks for the new sheet! It still looks like certain combinations screw up the concatenation, though. Can anyone think of a different approach even if it maybe not be as perfect as Loganeb's response? I feel like there has to be a way to have it done within one cell using formulas.

Did my initial logic make sense at all? I know it wouldn't be as refined but it would actually be much better if I didn't need another sheet or multiple cells. But I still very much appreciate the help Loganeb!

Thanks! Sorry I forgot to mention that I added that extra item. It was so that the rows and columns references could easily match up with what I have. And no worries! I don't need it to just be in one cell, it can span over the 7 rows that the table consists of if that helps at all. And I don't need something as clean as you have made so if in each cell it could just put together things that "will be available" and then the next cell puts together things that "will be available in 0-2 weeks" or something along those lines. Sorry for being so picky! And even if you can't get it to work, I may have to go ahead and find a way to include what you have already done because it's so awesome!

Originally Posted by Loganeb

Fixed the issue, didn't realize that there was a 7th item added.

There may be a way to do this in one cell with a formula, but to warn you, it will probably be a paragraph of a formula, lol. I'll look into trying to get it into one cell, but I can't promise anything.

Attachment 355298

Thanks so much again. I have been using what you made for me and it has been working very well for what I need it to do. I just have one question. I have copied and pasted the concatenator into my Excel file and made it match references and such, but I have attached the file you sent back to me just for reference since the problem will be in the same relative location.

It appears that the function in Row 17 of Concatenator is causing the Excel sheet to ask if we want to save changes every time we open up the file and close it (even if we didn't make any changes). Do you know of any reason this is happening? I don't want to change the Excel file to not auto-update (which is what some places suggested) but I'm not sure why it is that Row which is giving trouble (I figured this out through trial and error, deleting rows to see which deletion would result in no longer asking to save changes if nothing was changed and I am 100% it is something in that Row). I'm guessing it may be the OFFSET function within that row which is making the file ask for changes even when nothing is changed? Because I know that the IF function isn't a problem since I have plenty of those elsewhere and never had a problem.

If you could help with this last thing, it would be greatly appreciated!