Pulling multiple dependent records in a single row...

[sameer24p]

hi,
I have a SQL query that is returning me the correct output. It returns employee and dependent data. dependent record is tied to the employee. so if an employee has 2 dependents (spouse and child), then it returns 3 rows (employee id being the primary key)..Now if I want the output to be displayed as one row per employee, is there a way I can format this in excel ? the sql output currently returns about 23k rows, but it is row-wise. As an example, for employees E1 and E2 the output currently shows as below:
E1
S1
C1
E2
S2
C2

where S1,C1 are spouse and child of E1 respectively & S2,C2 are spouse and child of E2 respectively.

and I require it to be shown one row per employee with dependents
E1 record with S1 AND C1
E2 record with S2 AND C2


Appreciate if anyone knows what formatting I can do in excel to achieve this.


Thanks

 

[MarcelBeug]

If you have the values exactly as indicated in A2:A7 (so A2 = "E1", etcetera), then:

Let $B$2 be a helper field with the number of employees (array formula to be confirmed with CTRL-SHIFT-ENTER, not just ENTER):

=SUM(--(LEFT($A$2:$A$7,1)="E"))

Employees in column C, enter in C2 the following array formula (to be confirmed with CTRL-SHIFT-ENTER, not just ENTER) and copy down:

=IF((ROW(C2)-ROW(C$2)+1)>$B$2,"",INDEX($A$2:$A$7,SMALL(IF(LEFT($A$2:$A$7,1)="E",ROW(INDIRECT("1:"&COUNTA($A$2:$A$7)))),ROW(C2)-ROW(C$2)+1)))

Spouses in column D, enter in D2 the following formula and copy down:

=IF($C2="","",IFERROR(INDEX($A$2:$A$7,MATCH(SUBSTITUTE($C2,"E","S"),$A$2:$A$7,0)),""))

Children in column E, enter in E2 the following formula and copy down:

=IF($C2="","",IFERROR(INDEX($A$2:$A$7,MATCH(SUBSTITUTE($C2,"E","C"),$A$2:$A$7,0)),""))