Macro for looping through cells and concatenating based on criteria

[redlonghorn]

Hi all,

I'm currently trying to take some simple tabular data and put it into a more useful format using concatenation. I have been trying to create a macro to do this but have only found partial code online which hasn't really helped much. Here is an example of the data I would like to clean up from Sheet 1 (also attaching image)

A B C
Skill_1 M1 Experience
Skill_2 M2 Exposure
Skill_3 M1 Exposure
Skill_4 M3 Education
Skill_5 M2 Exposure
Skill_6 M3 Education
Skill_7 M1 Experience


sheet 1.jpg

I'm looking to create a macro that will run through this data and take all cells in column A where the corresponding cells in columns B and C = "M1" AND "Experience", respectively, and concatenate them (separated by commas) in a new sheet. So in this example, Skill_1 and Skill_7 would be concatenated in a new cell in Sheet 2. I'm looking to do this for all possible combinations of column B and C values. The final output in Sheet 2 would look as follows:


M1 M2 M3
Experience Skill_1, Skill_7
Exposure Skill_3 Skill_2, Skill_5"
Education Skill_4, Skill_6
Expectations


sheet 2.jpg

Any help would be much appreciated. Thanks!

[patel45]

no jpg please but excel sample file

[redlonghorn]

I have attached the sample file. The Data tab contains what I need to cleanup and the Sample tab contains what I need the output to look like. Thanks!

concat_loop.xlsx

[patel45]

3 levels and 4 categories ? or more ?

[Hawkeye16]

Is this what you are looking for? It is very similar to something I wrote for someone last week and posted on this forum - although finding it in a search in a timely manner is not always easy.

It is a UDF that takes 5 inputs similar to a SumIfs or CountIfs (Condition 1 Range, Condition 1, Condition 2 Range, Condition 2, Concatenation Range)

[patel45]

try attached

[redlonghorn]

Hawkeye -- Thanks for the help. I actually saw that thread for the UDF but unfortunately while it does solve the sample, I can't use it for my actual data. This is going to be used by a lot of different users who will essentially just enter data in one worksheet, click a button, and then see the results on another.

[redlonghorn]

This is great! This is exactly what I need. Now I just need to make sure I understand the code and can replicate on a larger scale. Thanks!!