google-sheetsgoogle-sheets-formula

Map a table, replacing values based on other cell


I would like to map a table replacing some data based on data from another cell..

In my below example I would like to remove data from the "Status" column if the cell to the left on the "price" column is empty.

I know it is possible to do it very simply duplicating the table and having a simple =IF formula manually placed where needed, here I try to see how I can achieve this in only one single formula; I managed to do it but my solution is quite complex

Start table

Price Status Price Status Price Status
12 Paid 23 Forecasted Forecasted
Paid 22 Forecasted 7 Forecasted
30 Paid 10 Forecasted 15 Forecasted
5 Paid 7 Forecasted 30 Forecasted
10 Paid 40 Forecasted 6 Forecasted
10 Paid 5 Forecasted 8 Forecasted
2 Paid Forecasted 9 Forecasted
4 Paid Forecasted Forecasted

Desired output

As you can see I want to remove the "Paid" "Forecasted" values when its corresponding left cell is empty too

Price Status Price Status Price Status
12 Paid 23 Forecasted
22 Forecasted 7 Forecasted
30 Paid 10 Forecasted 15 Forecasted
5 Paid 7 Forecasted 30 Forecasted
10 Paid 40 Forecasted 6 Forecasted
10 Paid 5 Forecasted 8 Forecasted
2 Paid 9 Forecasted
4 Paid

I managed to do it with the following function: =WRAPROWS(TOCOL(BYROW(WRAPROWS(TOCOL(A2:F9;0;0);2);LAMBDA(x;if(left(join("/";x);1)="/";"";x)));0;0);6) but as stated above.. this may be over complex, there is surely a more simple way. I wanted to use a =MAP formula but could not figure out how to manage the empty or not condition from left cell and apply it to the right cell

Thanks very much


Solution

  • An IF formula can be used surgically with OFFSET to manipulate the data as needed:

    =ARRAYFORMULA(HSTACK(A1:A9,IF(A1:E9<>"",B1:F9,)))
    

    If A1:F9 is the data set, we get A1:E9 with the last column removed. If that is not empty, We use B1:F9 as the result, with the first column removed from the range. By using IF this way, we have the desired result except the first column, which we Horizontally STACK it as is later.

    Price Status Price Status Price Status
    $12.00 Paid 23 Forecasted
    22 Forecasted 7 Forecasted
    $30.00 Paid 10 Forecasted 15 Forecasted
    $5.00 Paid 7 Forecasted 30 Forecasted
    $10.00 Paid 40 Forecasted 6 Forecasted
    $10.00 Paid 5 Forecasted 8 Forecasted
    $2.00 Paid 9 Forecasted
    $4.00 Paid