• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Create a field concatenated from multiple fields based on variable input data

R

rmccon

New Member
Hi all

I am looking for a way to solve this.

What is going to be happening is that each quarter we will download this file and have to run a macro on it to clean up the data. One of the items we need to figure out is which age categories a certain program will fall into.

As you will be able to see in the file I uploaded, all the programs have 3 fields for the start and end ages of the participants. (AgeMin, AgeMinMonths, AgeMinWeeks) & (AgeMax, AgeMaxMonths, AgeMaxWeeks).

What I need to do is convert those into a value (which I can do) and then based on the range between the two values, this program may spread across multiple age categories.

The Age categories are as follows:

1. Infant & Toddler - 0 - 2.5
2. Preschool - 2.5 - 4.5
3. Child - 4.51 - 10.5
4. Preteen - 10.51 - 12.99
5. Youth - 13 - 18.99
6. Adult - 19 - 54.99
7. Older Adult - 55 - +

So a program on dance may have an age range that is across Preschool and Child, and I need both of those to be in a single cell with a ", " between the two.

I can do the concatenate, what I don't know how to do is have excel figure out which fields to choose for the concatenation based on the age range for the course (or at least a simple way to do this).

I totally appreciate the time and effort you may spend on this.

Thanks
 

Attachments

Try this in row 7 and copy to rest of rows:

=IF(MATCH(B7+C7/12+D7/52,$B$20:$B$26)<>MATCH(E7+F7/12+G7/52,$B$20:$B$26),INDEX($A$20:$A$26,MATCH(B7+C7/12+D7/52,$B$20:$B$26))&", "&INDEX($A$20:$A$26,MATCH(E7+F7/12+G7/52,$B$20:$B$26)),INDEX($A$20:$A$26,MATCH(B7+C7/12+D7/52,$B$20:$B$26)))
 
@Mike86 - Thanks Mike! The formula works great and it does not fulfill everything I need. I don't think I made it clear that some items may spread over multiple age categories (i.e. child to adult). The way this is set up, it only gives me two age categories(the lowest and highest in the range). I'm guessing I would have to do multiple if statements to get all the items in the range.

@Awesome Wally - Thanks Walley! I'm guessing I will have to go VBA (which I am not strong at) though I am usually able to waddle my way through it. I've looked at what you linked to and while I think it maybe on the correct track, I don't think I have the skills to modify it. If you are able to help with that, I would appreciate any help.

Thanks again to you both for your help and if you have any other input, I would truly appreciate it.

Rob
 
What if you replaced the ", " with " through "? might work and would take less room than doing the entire range.
 
@Mike86 - thanks for that idea. I need it to have all the fields as I am going to import it into a system and those categories will allow the end user to find the courses they want.


What I really need help with is writing the VBA for the following:

1. Find out how many rows are in the list.
2. Writing a loop for the number of rows in the list
3. writing a loop that will test the age range against each age category and then add that age category to a string to input into the cell.

Thanks

Rob
 
You must log in or register to reply here.
Back
Top