Forum Discussion
Split cell and Calculate formula
Hi,
Little introduction:
I try build complex and clear excel too calculate servals aspect of game economy, for beeter play and learn much more about excel. Including all that causes me a problem active counting of production cubes
For now I use PowerQuery to collect acctual data from web, mainly buy, sell prices and other.
Next i have table with all recipe to construct various things. This recipe i write to one cell, and in second Power query i split this cell to rows and columns. Calculate this and send me information about cost and in thrird query group this prices.
This work good but is very unpleasand to use.
Problem:
If is possibile to split cell using only excel formulas and calculate this?
For now I was think about using LET formula to split cell, calculate materials and multiply by price finded buy Index(Match), but I don't know how split cell to use in Excel formulas formula
Example:
| Gravimetric Sensor Cluster | 2600 | Titanium Carbide/22 Nanotransistors/1 Hypersynaptic Fibers/2 |
create formula who work somelike like this (write pseudocode)
SPLITFORMULA( cell to array where you calculate like example below:
SUM(INDEX(price;match(Titanium Carbide from other sheet)*22*2600 +
INDEX(price;match(Nanotransistors from other sheet)*1*2600 +
INDEX(price;match(Hypersynaptic Fibers from other sheet)*2*2600)
If you see something what I can use?
I slowly think about type new formula in VBA.
Best Regards,
Noxam
7 Replies
You want to look the price of each item up in another sheet, then multiply by the quantity (after the /) and then multiply by another quantity in the adjacent cell (2600)?
Assuming your example is in Sheet1!A1:C1. Assuming your price list is at Sheet2!A1:B3 (description in Col A and price in Col B). And, assuming the individual items in cell C1 are separated by Alt+Enter (CHAR(10)), then
Select Sheet1, cell D1, then click name manager and enter:
Name = Eval
Scope= Select your worksheet
RefersTo= EVALUATE(TEXTJOIN("+",TRUE,SUBSTITUTE(SUBSTITUTE(FILTERXML("<L><I>"&SUBSTITUTE(Sheet1!C1,CHAR(10),"</I><I>")&"</I></L>","//I"),Sheet2!$A$1:$A$3,Sheet2!$B$1:$B$3),"/","*")))
Then, in cell D1 enter:
=B1*Eval
I also included my test workbook.
JMB17 , that's a great idea.
If translate your idea literally to avoid EVALUATE that will be
=LET( value,B1, text, C1, tableText,Sheet2!$A$1:$A$3, tableNumber, Sheet2!$B$1:$B$3, splitLines, FILTERXML("<L><I>"&SUBSTITUTE(C1,CHAR(10),"</I><I>")&"</I></L>","//I"), replaceTexts, SUBSTITUTE(splitLines,tableText,tableNumber), lookupNumbers, LEFT(replaceTexts,SEARCH("/",replaceTexts)-1), numbers, RIGHT(replaceTexts,LEN(replaceTexts)-SEARCH("/",replaceTexts)), Result, SUM(lookupNumbers*numbers)*value, Result)If the table with the data will be like
when we may use
=LET( value,B1, text, C1, splitLines, FILTERXML("<L><I>"&SUBSTITUTE(text,CHAR(10),"</I><I>")&"</I></L>","//I"), getTexts, LEFT(splitLines,SEARCH("/",splitLines)-1), getNumbers, INDEX(tblData[Value],XMATCH(getTexts,tblData[Name])), numbers, RIGHT(splitLines,LEN(splitLines)-SEARCH("/",splitLines)), Result, SUM(getNumbers*numbers)*value, Result)
Assuming the example values you showed are in cells A1:C1, give this formula a try...
=SUMPRODUCT(B1*IFERROR(TRIM(MID(SUBSTITUTE("/"&SUBSTITUTE(C1,CHAR(10),"/"),"/",REPT(" ",300)),2*ROW(INDIRECT("1:"&1+LEN(C1)-LEN(SUBSTITUTE(C1,CHAR(10),""))))*300,300)),0))
That will be 2600*(22+1+2), but not 2600*(22*(value found based on text before 22)+1*(value found based on text before 1)+2*(value found based on text before 2) )
I did not get the idea from the OP's formula that he was conditioning which numbers to use based on the text before the number. What it looked like to me was that the OP wanted to multiply each number in cell C1 by the value in cell B1 and then add them up. My code is equivalent to that. Based on your interpretation of what the OP wants, what value do you see as the desired result?
EDIT NOTE: Ignore the above response... I reread the OP's message again and see what you mean. My earlier posted formula does not do what I now see as the OP's question. Thanks for noting this for me.
Noxam, so far there is no TEXTSPLIT() formula in Excel. You may use FILTERXML() to split the text, but I don't think everything could be done in one cell, even with combining everything by LET().
First you shall split text lines, after that each line split on text and number which gives array in array; after that make some aggregation using arrays elements.
As variant you may make such particular aggregation in Power Query, but it depends on how the data is structured and what else you need to have in resulting table except mentioned data.
If speak about separate cells, perhaps for the converting text in one cell into the sum in another cell VBA will be better solution.
Better share the File with us, and let us examine the data to fix the issue. Seems you are trying to SUM value across workbook !!
