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

 

[oldbrewer]

fred

be ready

fred

ant hill

problem statement

2

ant hill,

ant hill, alas no, at home,

fred

alas no

3

alas no,

fred

can do

concatenate cells starting with a

fred

bye bye

fred

at home

6

at home,

helpers label rows of interest

where there is a row number place B col words by side with a comma space concatenated

finally concatenate the whole of M2

<colgroup><col width="64" span="16" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[bearcub]

Sorry, I don't follow.

I have a member who might have multiple records in a dataset because they have held different offices (positions). They could range from a delegate, member, president, treasurer, vice president, etc. These would be local (in their district) position. If they get elected to a state position (those starting with Service Center or State) these are must be excluded from the local column and placed in different columns. My goal is to have one record per member ID. To do this I have to move local and state positions into 1 record.

As I'm writing this I realize that my explanation might be cofusing and I apologize in advance/

Here is some of the data that I have. The first two rows I've combined and deleted the duplicate record. The other ones are for one member (RA Delegate isn't included in any of the columns)\\

I used the textjoin function to concatenate the list using a nested If function which looks at the the member ID to see how many times it appears in the record. If it appears more than once, that combined column will remain blank for the second, third or 4th occurrence of the ID number. However, the contents of these cells appear in the very first record (the first occurrence of the ID). If they include State or Service Center Positions then they will appear in a separate column, not the combined column.

I hope this makes better sense.

Thank you for your help.

Michael


Contact ID Local Leadership Positions Combined State Council Service Center Positions
5238212 VICE PRESIDENT VICE PRESIDENT, BARGAINING COMMITTEE CHAIR
10008125 MEMBER ENGAGEMENT TEAM MEMBER ENGAGEMENT TEAM, SITE REPRESENTATIVE
3815369 SITE REPRESENTATIVE SITE REPRESENTATIVE, MEMBER ENGAGEMENT CHAIR, MEMBERSHIP CONTACT
3815369 MEMBER ENGAGEMENT CHAIR
3815369 MEMBERSHIP CONTACT
6025539 SECRETARY SECRETARY, SITE REPRESENTATIVE
6025539 SITE REPRESENTATIVE
912133 SITE REPRESENTATIVE SITE REPRESENTATIVE
912133 RA DELEGATE
7252376 SITE REPRESENTATIVE SITE REPRESENTATIVE, TREASURER
7252376 TREASURER
6237175 PRESIDENT PRESIDENT, TREASURER
6237175 TREASURER
788512 STATE COUNCIL MEMBER EQUITY/HUMAN RIGHTS CHAIR, MEMBER ENGAGEMENT TEAM STATE COUNCIL MEMBER SERVICE CENTER COUNCIL - HUMAN RIGHTS CHAIR
788512 SERVICE CENTER COUNCIL - HUMAN RIGHTS CHAIR
788512 EQUITY/HUMAN RIGHTS CHAIR
788512 MEMBER ENGAGEMENT TEAM
4728994 MEMBER ENGAGEMENT TEAM MEMBER ENGAGEMENT TEAM, VICE PRESIDENT
4728994 VICE PRESIDENT
995288 CO - BARGAINING COMMITTEE CHAIR CO - BARGAINING COMMITTEE CHAIR, MEMBER ENGAGEMENT TEAM
995288 MEMBER ENGAGEMENT TEAM
870274 LOCAL - REPRESENTATIVE AT LARGE LOCAL - REPRESENTATIVE AT LARGE, MEMBER ENGAGEMENT TEAM
870274 MEMBER ENGAGEMENT TEAM
797695 MEMBER ENGAGEMENT TEAM MEMBER ENGAGEMENT TEAM, BARGAINING COMMITTEE CHAIR, VICE PRESIDENT, GRIEVANCE COMMITTEE CHAIR
797695 BARGAINING COMMITTEE CHAIR
797695 VICE PRESIDENT
797695 GRIEVANCE COMMITTEE CHAIR

 

[oldbrewer]

i DEMONSTRATED THAT certain "terms" can be identified and joined inside one cell, thinking that is what you want to do, so fred - who had 6 "jobs" now has only ant hill, alas no, at home, against his "id". The principle can surely now be applied to your specific requirements ?

 

[bearcub]

I see, sorry I wasn't used to seeing an algorithm instead of formula. I had an idea, like you mentioned, what I need to do but I didn't know how to translate this into Excel using formulas. I didn't know what you meant by problem statement - is this an If statement followed by true and false conditions. It was also confusing when you mentioned to include words beginning with A. Does this mean to use a negative for cells that include starting with State or Service Center. I can follow your logic but I don't know how to translate this into Excel.

Also, how did you get your example to look so clean. On my screen, my last example doesn't include any spaces between columns or rows. Is there a special tool that I have to use to do this on the message board?

Thank you for your help,

Michael

 

[oldbrewer]

problem statement is just a definition of what I think that you want. I used words beginning with a for simplicity - you want words "that do not begin with, say, a or b. I offered a methodology.

getting the screen on here to reflect spacing between columns is often a problem for amateurs like me.

you need to mark "allowable" terms with row number - then next to row number put the term (using offset match), then use the small function to list out the terms and then concatenate them - I will try to find time later to put it all together with formulas.

 

[bearcub]

Thank you that would great if you can do that (an Excel interpretation). I used textjoin to join the rows but I couldn't figure out how to ignore those other items.

Michael

 

[oldbrewer]

fred

red hat

#2

2

fred

red hat

fred

blue sky

fred

orange car

4

fred

orange car

fred

pink bus

fred

green grass

6

fred

green grass

fred

yellow bird

7

fred

yellow bird

bill

blue sky

bill

orange car

9

bill

orange car

bill

pink bus

bill

green grass

11

bill

green grass

ignore blue and pink cells

col J

2

fred

red hat

red hat

#1

4

fred

orange car

red hat, orange car

6

fred

green grass

red hat, orange car, green grass

I typed in the first 3 columns of this table

7

fred

yellow bird

red hat, orange car, green grass, yellow bird

fred

but easy to get via offset

9

bill

orange car

orange car

with match(small($g$2:$g$11,1)

11

bill

green grass

orange car, green grass

bill

if you cant do this I will put those

formulas up to

fred

red hat, orange car, green grass, yellow bird

bill

orange car, green grass

#1

=IF(H19=H20,"",H19)

red hat, orange car

=IF(H20=H19,J19&", "&I20,I20)

#2

=IF(OR(LEFT(B2,4)="blue",LEFT(B2,4)="pink"),"",ROW())

<colgroup><col><col><col span="6"><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[bearcub]

I can see what you're doing and I like. However, in some instance, some of the members might have 4,5,6,7, or eight different positions that need to be concatenated. I had to create a huge nested if to make this happen.

What did you mane by "if you cant do this I will put those formulas up to". I'd be curious about seeing those formulas to. You are providing me with some different scenarios in how to attack this problem, I'm going to running into this situation quite a bit in this project and would like to know how to attack the different scenarios.

What did you mean bu via offset with Match(small(...) - is this the first or second argument in the match function? And, if you were to nest this within the Offset function, would you put this in the row or column argument. Sorry, I miss be missing something with you explanation though I can see where you're headed (I'm missing something here).


thank you again,

Michael

 

[oldbrewer]

fred

red hat

row 2

#2

2

fred

red hat

fred

blue sky

fred

orange car

4

fred

orange car

fred

pink bus

fred

green grass

6

fred

green grass

fred

yellow bird

7

fred

yellow bird

bill

blue sky

bill

orange car

9

bill

orange car

bill

pink bus

bill

green grass

11

bill

green grass

ignore blue and pink cells

col F

col J

#3

#4

1

2

fred

red hat

red hat

#1

2

4

fred

orange car

red hat, orange car

3

6

fred

green grass

red hat, orange car, green grass

4

7

fred

yellow bird

red hat, orange car, green grass, yellow bird

fred

5

9

bill

orange car

orange car

6

11

bill

green grass

orange car, green grass

bill

fred

red hat, orange car, green grass, yellow bird

the cell under #3 (2) obtained by

bill

orange car, green grass

=OFFSET($G$1,MATCH(SMALL($G$2:$G$11,F19),$G$2:$G$11,0),0)

#1

=IF(H19=H20,"",H19)

fred under #4 obtained by

red hat, orange car

=IF(H20=H19,J19&", "&I20,I20)

=OFFSET($G$1,MATCH(G19,$G$2:$G$11,0),1)

#2

=IF(OR(LEFT(B2,4)="blue",LEFT(B2,4)="pink"),"",ROW())

col F are sequential numbers to make #3 formula simpler

<colgroup><col><col><col span="6"><col><col><col span="3"></colgroup><tbody>
</tbody>