Hello,
I apologize if I sound like a big dummy. I need this for work and would really appreciate some help. I have a list of names and a list of programs in the column next to it.
In a single box: I need to list all the names associated with a specific program in one box and all the names associated with another program in another box, and so on. So let's say I have 1,000 names in column A and each name has 1 of 5 different types of programs in column B, what I am trying to do is create a formula where all of the names in Column A that are part of Program 1 are listed in one cell with carriage returns between the names, all the names that are linked to Program 2 in another cell, and the same with program 3.
For a visual representation, this is what it would look like:
Name Program
Danny Program 1
Sally Program 3
Jesse Program 2
Frank Program 5
Kyle Program 1
Sophie Program 3
Aaron Program 2
Blain Program 4
Angela Program 3
Allison Program 5
So I want to be able to put in a a formula (or code if needed) that will automatically display any name that is on the same row as Program 1 (Danny & Kyle).
Here is what I have done so far:
1.) I found a VBA code I copied off the internet that allows me to list items in a range of cells in one cell, then I added CHAR(10) to seperate the names by the line. The code is "CAT"
2.) I am using the following code: =IF(ISNUMBER(SEARCH("*Program 1*",B1:B1000)),(CAT(A1:A1000, CHAR(10))),"")
3.) Tried changing it to =IF(COUNTIF(B1:B1000, "Program 1")),(CAT(A1:A1000, CHAR(10))),"")
Here is my problem: I cannot figure out how to make this a conditional request that will only include names that have "Program 1" in Column B. So, if a name from column A was "Program 2" in column B (like Jesse from above), I don't want that that name to list in the section meant for Program 1 Names.
Right now, the frustrating part is the names will only display if the first cell from the formula is "Program 1" using ISNUMBER(SEARCH formula and with COUNTIF it will display all the Names in Column A if ANY cell in column B contains "Program 1."
I do not want to "Sort & Filter" because in actuality there are many programs and I need a quick way to show the names all on one sheet underneath the heading for the programs with different data entered weekly.
Is this possible? I would be greatly indebted if you can figure this out for me!!
Bookmarks