Hello
It would be easier to build an intermediate work table to retrieve names for each attribute and extract data from the work table into final summary table.
E.g.,
The following Work table works as follows:
- Work::I:N are calculating row indices of Data::B that matches the attribute in Work::A.
- Work::C:H are retrieving name of Data::A whose row index is calculated in corresponding Work::I:N.
- Work::B is composing comma delimited string from Work::C:H.
Work (excerpt)
A1 attribute
A2 A-1
A3 B-1
B1 names
B2 =C2&IF(LEN(C2)*COUNTIF(D2:H2,"<>")>0,", ","")
&D2&IF(LEN(D2)*COUNTIF(E2:H2,"<>")>0,", ","")
&E2&IF(LEN(E2)*COUNTIF(F2:H2,"<>")>0,", ","")
&F2&IF(LEN(F2)*COUNTIF(G2:H2,"<>")>0,", ","")
&G2&IF(LEN(G2)*COUNTIF(H2:H2,"<>")>0,", ","")
&H2
B3 =C3&IF(LEN(C3)*COUNTIF(D3:H3,"<>")>0,", ","")
&D3&IF(LEN(D3)*COUNTIF(E3:H3,"<>")>0,", ","")
&E3&IF(LEN(E3)*COUNTIF(F3:H3,"<>")>0,", ","")
&F3&IF(LEN(F3)*COUNTIF(G3:H3,"<>")>0,", ","")
&G3&IF(LEN(G3)*COUNTIF(H3:H3,"<>")>0,", ","")
&H3
C1 n1
C2 =IFERROR(INDEX(Data::$A,I2,1),"")
C3 =IFERROR(INDEX(Data::$A,I3,1),"")
D1 n2
D2 =IFERROR(INDEX(Data::$A,J2,1),"")
D3 =IFERROR(INDEX(Data::$A,J3,1),"")
E1 n3
E2 =IFERROR(INDEX(Data::$A,K2,1),"")
E3 =IFERROR(INDEX(Data::$A,K3,1),"")
F1 n4
F2 =IFERROR(INDEX(Data::$A,L2,1),"")
F3 =IFERROR(INDEX(Data::$A,L3,1),"")
G1 n5
G2 =IFERROR(INDEX(Data::$A,M2,1),"")
G3 =IFERROR(INDEX(Data::$A,M3,1),"")
H1 n6
H2 =IFERROR(INDEX(Data::$A,N2,1),"")
H3 =IFERROR(INDEX(Data::$A,N3,1),"")
I1 i1
I2 =IFERROR(MATCH($A2,Data::$B,0),"")
I3 =IFERROR(MATCH($A3,Data::$B,0),"")
J1 i2
J2 =IFERROR(MATCH($A2,OFFSET(Data::$B,I2,0,ROWS(Data::$B)-I2,1),0)+I2,"")
J3 =IFERROR(MATCH($A3,OFFSET(Data::$B,I3,0,ROWS(Data::$B)-I3,1),0)+I3,"")
K1 i3
K2 =IFERROR(MATCH($A2,OFFSET(Data::$B,J2,0,ROWS(Data::$B)-J2,1),0)+J2,"")
K3 =IFERROR(MATCH($A3,OFFSET(Data::$B,J3,0,ROWS(Data::$B)-J3,1),0)+J3,"")
L1 i4
L2 =IFERROR(MATCH($A2,OFFSET(Data::$B,K2,0,ROWS(Data::$B)-K2,1),0)+K2,"")
L3 =IFERROR(MATCH($A3,OFFSET(Data::$B,K3,0,ROWS(Data::$B)-K3,1),0)+K3,"")
M1 i5
M2 =IFERROR(MATCH($A2,OFFSET(Data::$B,L2,0,ROWS(Data::$B)-L2,1),0)+L2,"")
M3 =IFERROR(MATCH($A3,OFFSET(Data::$B,L3,0,ROWS(Data::$B)-L3,1),0)+L3,"")
N1 i6
N2 =IFERROR(MATCH($A2,OFFSET(Data::$B,M2,0,ROWS(Data::$B)-M2,1),0)+M2,"")
N3 =IFERROR(MATCH($A3,OFFSET(Data::$B,M3,0,ROWS(Data::$B)-M3,1),0)+M3,"")
Summary
A1 attribute 1
A2 A-1
A3 B-1
A4 C-1
A5 D-1
B1 names
B2 =IF(LEN(A2)>0,VLOOKUP(A2,Work::$A:$B,2,0),"")
B3 =IF(LEN(A3)>0,VLOOKUP(A3,Work::$A:$B,2,0),"")
B4 =IF(LEN(A4)>0,VLOOKUP(A4,Work::$A:$B,2,0),"")
B5 =IF(LEN(A5)>0,VLOOKUP(A5,Work::$A:$B,2,0),"")
C1 attribute 2
C2 A-2
C3 B-2
C4 C-2
C5 D-2
D1 names
D2 =IF(LEN(C2)>0,VLOOKUP(C2,Work::$A:$B,2,0),"")
D3 =IF(LEN(C3)>0,VLOOKUP(C3,Work::$A:$B,2,0),"")
D4 =IF(LEN(C4)>0,VLOOKUP(C4,Work::$A:$B,2,0),"")
D5 =IF(LEN(C5)>0,VLOOKUP(C5,Work::$A:$B,2,0),"")
E1 attribute 3
E2 A-3
E3
E4 C-3
E5 D-3
F1 names
F2 =IF(LEN(E2)>0,VLOOKUP(E2,Work::$A:$B,2,0),"")
F3 =IF(LEN(E3)>0,VLOOKUP(E3,Work::$A:$B,2,0),"")
F4 =IF(LEN(E4)>0,VLOOKUP(E4,Work::$A:$B,2,0),"")
F5 =IF(LEN(E5)>0,VLOOKUP(E5,Work::$A:$B,2,0),"")
G1 attribute 4
G2 A-4
G3
G4
G5 D-4
H1 names
H2 =IF(LEN(G2)>0,VLOOKUP(G2,Work::$A:$B,2,0),"")
H3 =IF(LEN(G3)>0,VLOOKUP(G3,Work::$A:$B,2,0),"")
H4 =IF(LEN(G4)>0,VLOOKUP(G4,Work::$A:$B,2,0),"")
H5 =IF(LEN(G5)>0,VLOOKUP(G5,Work::$A:$B,2,0),"")
Notes.
The formula in Work::C2 can be filled down and right across Work::C2:H17.
The formula in Work::I2 can be filled down across Work::I2:I17.
The formula in Work::J2 can be filled down and right across Work::J2:N17.
The formula in Summary::B2 can be copied and pasted across Summary::B2:B5, Summary::D2:D5, Summary::F2:F5 and Summary::H2:H5.
You may extend Work::C:H and the corresponding Work:I:N as is needed to cover the names and modify the joining formula in Work::B accordingly.
Tables are built in Numbers v2.
Good luck,
H
