Dear Asa, Thanks A lotttttttttttttttt it works well. Both the formula and the results are working fine plus it is not time consuming.
thank you very much . but i have just one question if i put additional data in to the table then also it will work as it is or do i need to rebuilt all the formulas cuz the data in the workbook may get updated once in a month or something. like new user addition or deletion so can u please tell me do i need to rebuilt the all the formulas or it wlll automatically extend the range of the table i mean new data also available in results?? Regards, Avinash. On Saturday, March 24, 2012 7:26:14 PM UTC+5:30, Asa R. wrote: > > Avinash my friend, > > My pleasure. > > > > *As to the formula for C2…* You should get the same result for formula #1 > and formula #3. The downside with those is that they refer to the > Responsibility column using A1 notation, which might lead to errors later. > The formula I provided uses notation only available in Excel 2010, I just > learned. The @ notation is a shorthand for referencing a column in the > current row. More info and other 2007/2010 table differences at > http://blogs.office.com/b/microsoft-excel/archive/2010/01/25/table-improvements-in-excel-2010.aspx<http://blogs.office.com/b/microsoft-excel/archive/2010/01/25/table-improvements-in-excel-2010.aspx> > . > > > > You can try the following which I believe should work: > > This is the Excel 2007 version of the formula I provided: > > =tbl_txtList[[#This > Row],[UserMap]]&"--"&SUMPRODUCT(--([Responsibility]:tbl_txtList[[#This > Row],[Responsibility]]=tbl_txtList[[#This Row],[Responsibility]])) > > > > But! Don't bother with it. . . I could have sworn it worked, but it > doesn't seem to give the correct result. I knew I was taking a shortcut on > the notation a bit, but shucks I thought I double checked it and it > worked. Here, this version really should work: > > =tbl_txtList[[#This > Row],[UserMap]]&"--"&SUMPRODUCT(--(INDEX([Responsibility],1):tbl_txtList[[#This > > Row],[Responsibility]]=tbl_txtList[[#This Row],[Responsibility]])) > > > > In Excel 2010 notation it's easier to read: > > > =[@UserMap]&"--"&SUMPRODUCT(--(INDEX([Responsibility],1):[@Responsibility]=[@Responsibility])) > > > > But I also recommend against using SUMPRODUCT for this. > > My preference is for this COUNTIF version, which is faster: > > =tbl_txtList[[#This Row],[UserMap]]&"--"&COUNTIF( > INDEX([Responsibility],1):tbl_txtList[[#This > Row],[Responsibility]],tbl_txtList[[#This > Row],[Responsibility]]) > > > > The Excel 2010 notation would be: > > =[@UserMap]&"--"&COUNTIF(INDEX([Responsibility],1) > :[@Responsibility],[@Responsibility]) > > > > > > *In the Setts sheet,* you have two problems with the Report Key and > related data. > > (1) You have 8 formulas in the Report Key column (cells E8:E15). For > vbaVlookup to work as intended, you need to use just one formula and array > enter it over that entire range (or whatever vertical range is the maximum > number of values you'll need to retrieve). > > Highlight all 8 cells, ctrl-click in the top cell (E8) if it's not already > the active cell, hit F2 or click into the formula bar, then hit > Ctrl-Shift-Enter to confirm your formula as an array formula (and any time > you edit the formula, you must hit ctrl-shift-enter when done). > > (2) You use the following formula in those cells: > > =vbaVlookup($D$2,tbl_txtList,3,"A") > > The "A" at the end of the formula indicates that you want the result > returned in a horizontal array. The default is for a vertical array (or > you can specify "V"), which is what you need since you have a vertical > range of cells. The formula should change to: > > =vbaVlookup($D$2,tbl_txtList,3) > > > > Asa > > > > *From:* excel-macros@googlegroups.com [mailto: > excel-macros@googlegroups.com <excel-macros@googlegroups.com>] *On > Behalf Of *Avinash > *Sent:* Friday, March 23, 2012 7:34 AM > *To:* excel-macros@googlegroups.com > *Subject:* $$Excel-Macros$$ Re: Arry Formula help > > > > Dear Asa, > > > > thanks for your help and using your technique i've found new ways to > perform new task and more use of tables but can you please see the below > formula which you have given cuz it is giving me diffrent values > > > > 1 - Actual formula > > ************ > > =$A2&"--"&SUMPRODUCT(--($B$2:$B2=$B2)) > > > > > > > > 2 - your formula > > ************ > > in 'txt List'!C2: > > > =[@UserMap]&"--"&SUMPRODUCT(--([Responsibility]:[@Responsibility]=[@Responsibility])) > > > > > > > > 3 - Formula which i have used > > ************ > > =tbl_txtList[[#This Row],[UserMap]]&"--"&SUMPRODUCT(--($B$2:$B2=$B2)) > > > > > > in my workbook i used formula number 3 which is also giving me the proper > result so can you please let me know which formula should i use ??? > > > > and is it formula number 3 is correct?? > > > > also i want to tell you that your table technique is realy awesome and it > is very fast but can you please look in setts sub sheet and see column G, H > and I cuz after doing all of this i want final results in colume F > (currently explained in column I > > > > current values are highlited in Column G > > Required Values are in H and bassed on the required values results are > highlited in column I. > > > > please open the attachement workbook for better understanding.. > > > > and thanks in advanced for your support. > > > > Regards, > > > > Avinash > > > > -- > FORUM RULES (986+ members already BANNED for violation) > > 1) Use concise, accurate thread titles. Poor thread titles, like Please > Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice > will not get quick attention or may not be answered. > > 2) Don't post a question in the thread of another member. > > 3) Don't post questions regarding breaking or bypassing any security > measure. > > 4) Acknowledge the responses you receive, good or bad. > > 5) Cross-promotion of, or links to, forums competitive to this forum in > signatures are prohibited. > > NOTE : Don't ever post personal or confidential data in a workbook. Forum > owners and members are not responsible for any loss. > > > ------------------------------------------------------------------------------------------------------ > To post to this group, send email to excel-macros@googlegroups.com > -- FORUM RULES (986+ members already BANNED for violation) 1) Use concise, accurate thread titles. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get quick attention or may not be answered. 2) Don't post a question in the thread of another member. 3) Don't post questions regarding breaking or bypassing any security measure. 4) Acknowledge the responses you receive, good or bad. 5) Cross-promotion of, or links to, forums competitive to this forum in signatures are prohibited. NOTE : Don't ever post personal or confidential data in a workbook. Forum owners and members are not responsible for any loss. ------------------------------------------------------------------------------------------------------ To post to this group, send email to excel-macros@googlegroups.com
