excelexcel-formula

How to do consecutive subtraction of adjacent columns and return the result concatenated in cell with comma separator?


I have following data which has the Product ID and their order dates in each column in descending order. Product ID and order dates

I want to subtract column D from column C then return the result in column B, then subtract column E from column D and return result in column B with previous result separated by comma. And this will be repeated till last filled column. Once that's done i can pull the formula down for the rest of the rows.

I did the manual calculation for couple of rows. But I am trying to do this for larger data. The result should like this:

Frequency of ordering of a product

Each arrow color belongs the result of subtraction in column B. For example, for red arrow, column D is subtracted from column C and the result(8) is highlighted by color Red in column B.

Thank you.


Solution

  • Using TEXTJOIN,FILTER and DROP:

    =LET(rng,C2:M2,flt,FILTER(rng,rng<>""),TEXTJOIN(",",,DROP(flt,,-1)-DROP(flt,,1)))
    

    ![enter image description here