Merge info from multiple rows where value in column a is the same

[Guest]

Good Afternoon -

I have a worksheet with 20,000+ rows-

The first column is a unique identifier for a group, and then the rest of
the columns B - M are services. What I need to do is merge the roles so that
if any listing of the 'group' in column A has something in any column B - M
it will show all listed. I will show an example below because I know it
sounds more confusing then it is...
This is how the data is

Group Red Service Green Service Blue Service Purple Service
1 Red Blue
1 Green
1 Red
2 Red
2
Purple
3 Green
4 Green Blue
4 Red

And this is how I would love it to be -

Group Red Service Green Service Blue Service
Purple Service
1 Red Green Blue
2 Red
Purple
3 Green
4 Red Green Blue

 

[Guest]

Make a copy of you worksheet and do this on the copy.

assume the first "1" in your sample data is in A2 and you have header
information in row 1.

then select B2:C9 and do Edit=>goto=>special and select Blanks

C2 should then be the activecell with only the blank cells selected. Put in
a formula like (this is specific to C2 and would be adjusted if another cell
is active).

=IF(AND($A3=$A2,C3<>""),C3,"")

and hit Ctrl+enter rather than just enter. This should fill all the
selected cells with this formula and this formula will put the missing
entries in the first row of each group as a minimum (at least it worked for
my test data - I may have missed something)

now select B2:C9 again and do Edit=>Copy, then Edit=>Paste Special and
select values to replace the formulas with the values they returned.

Now go to F2 and enter the formula

=if(A2<>A1,"Show","Hide")

and drag fill this formula down column f. Select column F and do
Data=>Autofilter and in the dropdown, select show

Select A1:C9 and do edit => copy, then go to a new sheet and select a single
cell. Do edit=>paste. Only the visible cells should be copie and this
should be what you want.

 

[Guest]

Did you ever know that you're my hero?

Thanks TOM!

 

[Guest]

Hopefully that means it worked!