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
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 |
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
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 H
orizontally 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 |