Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please, need help, I have to prepare a report today.
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please, need help, I have to prepare a report today.
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please, need help, I have to prepare a report today.
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 "Max" wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please, need help, I have to prepare a report today.
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 --- "JMALTO" wrote: 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,"")) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I prepare a report | Excel Discussion (Misc queries) | |||
how to prepare formulae | Excel Worksheet Functions | |||
How do I prepare a allowance payment tracker? | Excel Discussion (Misc queries) | |||
HOW TO PREPARE SUMMARY OF DIFFERENT ITEMS IN ANOTHER WORKSHEET? | Excel Worksheet Functions | |||
=IF(OR(TODAY() |
Excel Discussion (Misc queries) |