0

Function arguments in example spreaedsheet I want stripped out

I have done this before, but I have lost the method/process.

My ideal answers to my lookup are 0,2,3,0,0,0,0,0,0,..

The aggrgate fucntion can only return the first answer, although in the back-end excel , obviously does have the answer - its in the fucntion argument array.

Is there a way (there was - I did it before but lost the method process due to theft of laptop and doing a hundred million other things since I did it and now) to retrieve only the function arguments, or copy-paste them in an adjacent cell to the formula you want the argument from?

It was a 2 step non-VBA simple process in the end I remember.

But if there is a VBA funtion that could do this that would be great. Even though I did it 9-12 months ago without any VBA and it was suprisingly "Why didnt i think of that my self before" simple in the end.

(and useful)

David Wooley - AST
  • 346
  • 2
  • 4
  • 13
  • Are you looking for the method to enter an array formula? Use cntl-enter? – PeterT Oct 28 '18 at 16:37
  • No . Im not.. I know that. – David Wooley - AST Oct 28 '18 at 16:42
  • 1
    It's not clear exactly what you want to do - it *looks* like you want to get the output from an array formula, but beyond that there are no details. Where is the formula you need to evaluate, and what do you want to do with the result? – Tim Williams Oct 28 '18 at 17:05
  • =AGGREGATE(15,3,(Sheet2!$E$7:$E$51=V47)/(Sheet2!$E$7:$E$51=V47)*(ROW(Sheet2!$E$7:$E$51)-ROW(Sheet2!E$6)),1). Gives row value of V47 found in Sheet2 E7:E51. If I change 1 to 2 in the 4th argument, ill get the result of '3', (3rd row), and if I change 1 to 3, Ill get '24' as a result. (24th row). Any other value in the 4th argument of this formula I will get an error as expect, as value in cell v47 only appears 3 times. Being able to copy the formula arguments as text into another cell would save me time & do the job all at once. I will use Mid function afterwards to extract the values i need. – David Wooley - AST Oct 28 '18 at 18:22
  • equally I could re-do formula as:=AGGREGATE(15,3,(Sheet2!$E$7:$E$51=V47)/(Sheet2!$E$7:$E$51=V47)*(ROW(Sheet2!$E$7:$E$51)-ROW(Sheet2!E$6)),column(a1)) ... and do it across 3 or 4 columns to get all the results, and then concatonate these.in the 5th column. so all my row occurances are in one cell. – David Wooley - AST Oct 28 '18 at 18:31
  • I was just hoping there was a way to parse the formula argument results (as they appear when you open the foruma text box help as in the picture above,) as text into the next cell, so you can work on them directly without the need for additional columns. .... Because Excel has already got the results I need in one cell !!! So why do I have to apply the forula accross x number of cells to get what we already have (in excel back end - as is visible in the image above). – David Wooley - AST Oct 28 '18 at 18:38
  • or I can do text-to-columns for the output . P.S. its not CSE array formula. I tried it as that =AGGREGATE(15,3,(Sheet2!$E$7:$E$51=V122)/(Sheet2!$E$7:$E$51=V122)*(ROW(Sheet2!$E$7:$E$51)-ROW(Sheet2!E$6)),{1,2,3,4}) but either got the syntax wrong or aggregate function is itself an array formula without the need for CSE. It cant do an array of an array. (aggregate is already an array formulA) & it would just default to the same result as if i just put "1" in the 4th argument (when i tried {1,2,3,4). As I said =AGGREGATE(15,3,array,x-th value) is itself an array formula without needing CSE . – David Wooley - AST Oct 28 '18 at 18:39
  • Tim - Thanks for your question ! you got me thinking about this more. But I know the actual formula argument results themselves can be copied ("frozen") and pasted as text into adjacent cell, ridding the need for further column work & working with the results directly. Freezing/Copying argument results as text would constitute another equally valid (but often unconsidered) solution to the problem of getting the exact data you need, in the format you need, quickly. In this case, I need/want these row results from the aggregate function all in one cell - (I can find replace the #Div/0!'s to "") – David Wooley - AST Oct 28 '18 at 19:03
  • I just need to be reminded how to do it (freeze/capture the formula arguments and paste as text elsewhere) !! (I did it once , a year ago, but forgot & lost the process which I had saved). This is why I asked the question. Becasue I recall - despite some people saying it cant be done - it can be done, and is very powerful/nifty. – David Wooley - AST Oct 28 '18 at 19:13
  • & I hate using more than is necessary columns. I think that;s quite a heathy thing to hate.! lol. Anyway. Ill be quiet now and I sincerely hope for a workable answer to freezing/copy-pasting/parsing formula arguments (when they can be so) out from the formula cell and into adjacent cell (or even the same cell) the formula is present in. – David Wooley - AST Oct 28 '18 at 19:18
  • Is this what you're asking about? https://support.office.com/en-ie/article/evaluate-a-nested-formula-one-step-at-a-time-59a201ae-d1dc-4b15-8586-a70aa409b8a7 – Tim Williams Oct 29 '18 at 04:24
  • No. Unfortunately. It wasn't and isn't that, What I did 12 months ago was 'stupendous' . It involved something lile going into File> Options > Formula's and/or File>Options>Advanced and changing one or two of the settings, The evaluated portions of the formula could be seen in the cells in all the the cells the formula resided in ...I had to re-set it back to use excel normally again. This is what I imagine I did. There was also a deliberate use of {} or [ ] to fool excel to see the formula's differently. So much has happened since that my memories mixed up,hazy, but definately wasnt that link – David Wooley - AST Oct 29 '18 at 09:47
  • But thank you anyway. It wasnt hard, but involved 2 fiddly steps. Once I had the evaluated portions I was able to copy paste these as text and work with the data as I wanted it/needed it. As a whole I wish I was proactive in a. not loosing my work/laptop and b. saving that excel workbook that I did this for somewhere else. It's my biggest heartbreak & I feel loss. So much of my work was lost (never - but thought I did - back anything up). I would have done it by now. – David Wooley - AST Oct 29 '18 at 09:53
  • Sorry to go all personal, but the tragic thing is loosing one thing after another, & when youve lost your 6th person to cancer/isis/terrorism ... and you rebuild your life, and that get nicked too, due to a peadofiles visit to your home which no one does anything about & you have a row and your laptop gets stolen behind your back ... well what can I say then?Had to buy another one the next day to continue work. Got most of my macros back & stuff - but this ask was the most valauble of the lot & supersceeded any macro I could think of.It was my hidden gem, to make me superuser & "own" excel – David Wooley - AST Oct 29 '18 at 10:18
  • ... as I felt I always did. moral of the story, beware of theifs of all guises. Sorry. Shafted. Its been 12 months. I was trying to remember what i did for this job (which I did do!) and all this stuff came back to me. Gosh. Have a good day. – David Wooley - AST Oct 29 '18 at 10:23
  • Im getting close to alternatives. This looks like to be a billion$ question in excel. Certianly felt so for me when "I racall" doing it. Though have doubts now, as I spoted UDF contact (chandoo) which I used to work on similar lines. [dont ask! though you can] . https://stackoverflow.com/questions/11320626/does-excel-have-a-built-in-method-for-parsing-formulas-ie-to-obtain-a-list-of & https://sourceforge.net/projects/opensolver/files/. Im sure if I worked in the office someone might know....? Tho ppl keep quiet about what they know. I trust no one.RIP Chip Pearson, 2018. Precidents !! – David Wooley - AST Oct 29 '18 at 16:22
  • If we could get F9 to work on all cells highlighted and freeze the text - that would be a welcome (if complicated) addition/soluton to excel. – David Wooley - AST Oct 29 '18 at 16:24
  • For my purposes, ive worked a solution to this. Step A. Download or copy UDF macro http://www.cpearson.com/excel/stringconcatenation.aspx/ Step B.Apply UDF as so: =StringConcat("|",IF(Sheet2!$E$7:$E$51=V41,ROW(Sheet2!$E$7:$E$51),"")) . So Results of row#'s the value V41 is found in range appear in 1 (One) cell concatenated. – David Wooley - AST Oct 30 '18 at 20:02
  • Entered as CSA (array forumula) – David Wooley - AST Oct 30 '18 at 20:03

0 Answers0