Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging fields question
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging fields question
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,COLUMN S($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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Mikael Lindqvist" wrote: 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging fields question
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup based on two fields without merging? | Excel Worksheet Functions | |||
Merging fields | Excel Discussion (Misc queries) | |||
Merging Worksheets - Fields Of Same Data In Different Location | Excel Discussion (Misc queries) | |||
some merging question | New Users to Excel | |||
Fix too few data fields error message when merging excel and word | Excel Discussion (Misc queries) |