Map a table, replacing values based on other cell

Question

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

Answer 1

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

Answer 2

Use QUERY

First of all, if I understood you correctly, you just want to use formulas which are relatively simple or readable. With this, I suggest you try and use the QUERY function. However, it does remove all empty cells (based on the price column) due to the query SELECT * WHERE Col1 IS NOT NULL.

Solutions

1. WRAPROWS + LET + QUERY

(NOTE: this solution does not include the headers while the data are arranged differently since the data pairs were shifted to fill in the empty cells)

Formula:

=LET(a; WRAPROWS(TOCOL(A2:F9);2);
     b; QUERY(a;"SELECT * WHERE Col1 IS NOT NULL");
     IFNA(WRAPROWS(TOCOL(b);6)))

Output:

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

2. LET + QUERY

Formula:

=LET(a; QUERY(A1:B9;"SELECT * WHERE Col1 IS NOT NULL");
     b; QUERY(C1:D9;"SELECT * WHERE Col1 IS NOT NULL");
     c; QUERY(E1:F9;"SELECT * WHERE Col1 IS NOT NULL");
     IFNA(HSTACK(a;b;c)))

Output:

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

References

• QUERY
• LET