Forum Discussion

Norman_McIlwaine's avatar
Norman_McIlwaine
Brass Contributor
Aug 18, 2023
Solved

Need help to resolve Excel Formula using Let,Take,index,Sort,Unique,HSTACK,MMULT,TOROW,MONTH.

I am trying to add 5 additional columns to the function but can get only a $value:   Function :   =LET(r,B1:G8,m,6,a,TAKE(r,,1),b,INDEX(r,,2),c,INDEX(r,,3),d,INDEX(r,,4),e,INDEX(r,,5),f,INDEX(r,,...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Sep 07, 2023

    Norman_McIlwaine 

    Thank you. Do I understand correctly that csv62719 is you source file. If to avoid Power Query we copy/paste data from it into the sheet in main file, here by formula create massaged range and based on it and month/year/monthid parameters create aggregated result. Workflow is correct?

Norman_McIlwaine's avatar
Norman_McIlwaine
Brass Contributor
Sep 05, 2023

Sorry, for the misunderstanding. It is the Cells in column F which are blank that I am trying to populate from the corresponding cell in column G.

Source data

Source Massaged Data

 

 

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Sep 05, 2023

Norman_McIlwaine 

I see, thank you. In general that's job for Power Query if you consider such option. It was created to transform data. If you initially get data from csv file or other source, you only need to say Power Query from which file/folder to take data, the rest could be done within query and loaded into the grid, includes result of the formula we discussed. Once done, the rest of the job to click Refresh All updating with new data.

If with formula, we may take entire data from one place, apply formulae and put result into another place. The question is to you need to edit something additionally within resulting range or not.

If massaged data is needed only to generate in bit another way result of the current formula, it's better to adjust that formula.

To replace data in place it could couple of options - OfficeScript if it is available in your subscription ( if so it shall be Automate tab on ribbon).

Or VBA programming, but that's not my territory.

 

So, all depends on goals and tools you have in hand. What will be desirable option?

Resources