Formula to exclude items in a range of cells

[bearcub]

I have a number of cells that I want to concatenate into one cell (merge multiple records into one record). However, I would like to exclude cells that start with the words State or Service Center since these have to be placed into a separate field in the record.

Column A has all the member IDS for our organization. During their time with our company, some members have held local and/or state positions (there is presently multiple records per member but we need to merge everything into one cell so we don't have duplicate member ID's in the list) .

Local positions don't have positions that include the terms State or Service Center - these are State positions. State and Service Center positions should not be included with loc positions.

Here is an example:


In range B1:B4 I have the following 4 items (Range A1:A4 has the same member ID so I have to join rows 1:4 into row 1):

B1:Member
B2:State
B3:Service Center
B4: Board

Contents C1: Member, Board; D1: State; E1: Service Center

However if a member, for example, had Member, Treasurer, President listed I would want all three in C1. Only positions that contain State or Service Center have their own fields.

I need a generic formula that would exclude cell that starts with the terms State or Service Center but include everything else.

Is this possible?

Thank you for your help in advance,

Michael

 

[bearcub]

Thank you for the explanation, this is great! I'll try this out when I get back to work tomorrow. I'm sure with these tools i should be able to figure out something that will work for all the scenarios.

I'm still totally lost how get all the spaces in your responses. When I try to create a table by adding spaces or using the tab key my result is a table that is all squished together. I spend a lot of time spacing out my replies but when I post it, the table comes out one big mess. How do you get your responses to appear so nice and clean?

Thank you again,

Michael

 

[oldbrewer]

I have a spreadsheet, obviously - I highlight all of it, control C, move to reply box on here, control V

 

[bearcub]

Ah, I had my tables in Excel lined line like you would normally but I just realized I have split the 2nd and 3 columns (Extremely and Interesting) by adding extra columns (to get these 3 columns to look like this I have to use 7 columns and everything is lef alignment when it comes into this format even though I have everything centered in my spreadsheet):

Helpful Extremely Interesting
1 1 1
2 2 2
3 3
4 4
5
6




To get this format, I had to do the following

Helpful in Column A; Column B is blank, Col C contains Extremely, column D Contains 1-6; Column E Interesting, Column F is blank and column G contains 1 and 2.

Interesting how this works, isn't it?

Michael

 

[bearcub]

Even after I did all this it still comes out squished - unless others aren't seeing what I'm seeing. As I look at this I have the headers without spaces and the numbers are squished together. Maybe there is a feature I have to turn on from my end?