Please, need help, I have to prepare a report today.

[JMALTO]

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

 

[Max]

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,INDEX($G$2:$G$8,COLUMNS($A:A))=$H2),INDEX($B$2:$B$8,COLUMNS($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]

Max, thanks. That was wonderfull.
Having so limited space to include in this forum and falling into the
assumption that there will be only one whole formula that will handle all the
conditions; I did resize the amount of data for the purpose of explaining
what I need, in reality there are 300+ rows, and beside column "I"; there are
3 more that must be filled up when the columns "C, D and E" =1.

Is it possible to use the "StrinConcat" function to do all this within one
formula?

This is what I have so far with this function but I haven't add succesfully
the "IF" column B=1 into it yet.
=StringConcat(",
",IF($F$7:$F$343=H7,$A$7:$A$343,""),IF($G$7:$G$343=H7,$A$7:$A$343,""))

Just human

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,INDEX($G$2:$G$8,COLUMNS($A:A))=$H2),INDEX($B$2:$B$8,COLUMNS($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
---

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

 

[Max]

I'm not sure there's a single cell formula that can handle all of the complex
logics that you seek to do. If there is, you would have received responses to
that effect by now to your many repeat postings. Maybe just stick to your
macro which you say is doing the job for you right now? (in one of your
earlier posts)

Kindly press the YES button in my earlier response
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik