Here's one formulas model which delivers the exact results indicated
Source table as posted is assumed in A1:H8, data in row2 down
In I2:
=IF(AND(OR(INDEX($F$2:$F$8,COLUMNS($A:A))=$H2,INDE X($G$2:$G$8,COLUMNS($A:A))=$H2),INDEX($B$2:$B$8,CO LUMNS($A:A))=1),COLUMNS($A:A),"")
Copy I2 across to O2, fill down to O8. This is the criteria matrix, as per
your specs.
[Copy across by as many cols as there are source data rows]
In P2:
=IF(COLUMNS($A:A)COUNT($I2:$O2),"",INDEX($A$2:$A$ 8,SMALL($I2:$O2,COLUMNS($A:A))))
Copy P2 across to V2, fill down to V8. This part returns the multiple
results, neatly packed to the left in each row, with each result in its own
cell. To me, this sort of results format would normally suffice/is better?
To concat the multiple results per row as desired (ie with a comma-space)
Put in W2:
=SUBSTITUTE(TRIM(P2&" "&Q2&" "&R2&" "&S2&" "&T2&" "&U2&" "&V2)," ",", ")
Copy down to W8, to return exact results indicated
Since the above should've helped, press the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"JMALTO" wrote:
I need a formula that combines data from 2 or more
cells from column "A", when cells from columns "F and/or G"
MATCH data in cells from column "H", and only IF column "B" is =1.
Example "I1, I2, I3, etc"
A B C D E F G H I
WSt USA Ch Srv Ob LHD TD TL USA
1 100 1 30 24 24 100, 103
2 101 1 33 30 100, 102, 105
3 102 1 30 33 101, 103
4 103 1 33 24 50 105
5 104 1 84 85 74
6 105 1 30 50 84
7 106 1 33 24 85
Just human