Merging fields question

[Guest]

Hi,

I have 2 columns: Column A displays "accID" and Column B displays "projID",
this is one-to-many relationship (each accID may have many, or none, projID).

Example:

AccID, ProjID
3010 0
3011 200
3011 300
4010 50
5050 0
6010 300
6010 500

In column C I have written down all unique AccID and in Column D I want to
write a function that merge all ProjID (see below):

3010 | 0
3011 | 200, 300
4010 | 50

Is this possible, or do I need to revert to VBA?

Kindly,
Mikael
Sweden

 

[Guest]

Here's one formulas play which delivers it ..

Source data in cols A and B as posted, data from row2 down

In C2 down you have listed the unique AccIDs, eg:

3010
3011
4010
etc

Let's assume the max # of unique AccIDs listed in C2 down is 10,
and the max ProjIDs associated with any AccID is 5

Put in D2:
=IF(COUNTA($A2:$B2)<2,"",IF($A2=OFFSET($C$1,COLUMNS($A:A),),ROW(),""))

Copy D2 across by 10 cols to M2. Leave D1:M1 blank.
The "10 cols" corresponds to the max # of unique AccIDs

Put in say, O2
=IF(COLUMNS($A:A)>COUNT(OFFSET($C:$C,,MATCH($C2,$C:$C,0)-1)),"",INDEX($B:$B,SMALL(OFFSET($C:$C,,MATCH($C2,$C:$C,0)-1),COLUMNS($A:A))))

Copy O2 across by 5 cols to S2. The "5 cols" corresponds to the max ProjIDs
associated with any AccID.

Then just select D2:S2 and copy down to cover the max expected extent of
source data in cols A and B. Hide away cols D to N. Cols O to S will return
the results that you seek, all neatly bunched to the left. Adapt/extend to
suit.

 

[Herbert Seidenberg]

Another option is to use Pivot Table.
No formulas are required.
The table will look like this:
Count of ProjID ProjID
AccID 0 50 200 300 500
3010 1
3011 1 1
4010 1
5050 1
6010 1 1

A few more keystrokes will format it like this:
3010
3011 200 300
4010 50
5050
6010 300 500