• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Multiply and Sum each other columns

Hi,

I have a sheet where each row is an order.
Each order may have since 1 product until n products.
The export file of this data order present a pattern of data that repeats every each 14 columns for each product
Those 14 columns are data of just 1 product of 1 order.
I need to multiply 2 columns of each product and then sum with another multiplication of the next 2 columns of the further product.

In order to facilitate comprehension, i uploaded an example sheet. I put just one row that represent one order. There is a set of 14 columns that repeat in this row. I put just 4 set of 14 columns. Remember that each 14 columns represent one product of this order (row). I need to multiply the 4 th column with 12 th column.
If there is just one product, that is simple like the ordinary formula i put in the outline in the file. But if there are several products in the order, i need to multiply the 4 th with 12 th column for each product and after sum all. The number of products data (each 14 columns in the row) are variable for each order (row)

I need a formula to recognize such pattern and make this calculation.

I hope you can help me. Please dont hesitate to ask if you find explanation confusing.

Best Regards
 

Attachments

p45cal

Well-Known Member
That is a truly awful format for output.
I presume you've imported this data into Excel from a data file of some sort?
If so can you attach a copy of that file?
 
P45cal, it is a weird format. More weird is that those columns are extracted from just one cell. I use a macro to extract information. See attached.

I would appreciate a lot if you could help to solve this.
 

Attachments

Last edited:

p45cal

Well-Known Member
Are you really receiving this information as an Excel file (SAMPLE.xlsx)?!
Where does it come from? (It looks JSON-like)
It's very likely that this data can be imported (a) more robustly and (b) in a more friendly way (you're running a version of Excel that will handle this).
I'd like to see that data in its original format.
 
Hi,

Our ecommerce is hosted in a cloud plataform. The only format they offer to export order data is attached. I kept just 3 original rows for security reason. With this file in hand i use the macro in the SAMPLE.xlsx to split single cell data (Column AT) in several columns in a different workbook.

Now i need to work with some data to calculate some costs. That is the reason of this thread.

Best regards.
 

Attachments

bosco_yip

Excel Ninja
P45cal, it is a weird format. More weird is that those columns are extracted from just one cell. I use a macro to extract information. See attached.

I would appreciate a lot if you could help to solve this.
In the attached file is a formula solution based on your file extracted data in one cell.

In B2 formula copied down :

=IFERROR(SUMPRODUCT(FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE($A2,"quantidade"":","<r/>"),",""","</b><b>")&"</b></a>","//b[r]")*FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE($A2,"preco_custo"":","<r/>"),",""","</b><b>")&"</b></a>","//b[r]")),"")

72895
 

Attachments

Please try

=SUMPRODUCT(FILTERXML("<x>"&SUBSTITUTE(SUBSTITUTE(A1,"dade"":","<m>"),",""preco_cheio","</m>")&"</x>","//m"),FILTERXML("<x>"&SUBSTITUTE(SUBSTITUTE(A1,"custo"":","<m>"),",""produto","</m>")&"</x>","//m"))

or Power Query


Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    AddedTotal = Table.AddColumn(Source, "Total", each List.Sum(Table.AddColumn(Table.FromRecords(Json.Document([Column1])) ,"T",each [quantidade]*[preco_custo])[T]) )
in
    AddedTotal
 

Attachments

Excel Wizard, thanks for both solutions in the Sample.xlsx and also for Power Query code (straightforward approach). Best Regards.
 

Peter Bartholomew

Well-Known Member
I have Excel365 (beta channel) and took a look at the new functionality including LAMBDA functions and, slightly older, LET.
It may be years before I really get a feel for how the new functionality is best deployed!
First I wrote a formula using LET to return the "quantidade" from a particular cell. Then I wrapped it with a LAMBDA function to allow me to give the cell containing the compound string and the property to extract as parameters to the Lambda function EXTRACTVALUE.
The Lambda function is defined by
Code:
= LAMBDA(string0,fieldName,
   LET(
      string, SUBSTITUTE(string0, "}]", ","),
      label, SEARCH(fieldName, string),
      start, label+2+LEN(fieldName),
      end, SEARCH(",", string, start),
      VALUE( MID(string, start, end-start) ) )
   )
The price and quantity are then returned by the formula
Code:
= EXTRACTVALUE(record, "preco_custo")
= EXTRACTVALUE(record, "quantidade")
or, if the values were required as an array,
Code:
EXTRACTVALUE(record, {"quantidade";"preco_custo"})
I don't know about anyone else but I have trouble even thinking of this as a spreadsheet.
It may share the same familiar functions but it doesn't look like end-user computing.
 
Top