You can use embedded arrays to join the two data sets for the purpose of plotting. For example, if your data is in columns A and B of two sheets, and has a header row, the following will join and sort the two sets:
={Sheet1!A1:B1; sort({filter(Sheet1!A2:B, len(Sheet1!A2:A)); filter(Sheet2!A2:B, len(Sheet2!A2:A))}, 1, true)}
Explanation:
Sheet1!A1:B1
grabs the header row. The semicolon after it works as row separator in embedded arrays.
filter(Sheet1!A2:B, len(Sheet1!A2:A))
takes all A-B cells from Sheet1 with nonempty A entry (i.e., filters out blank rows)
filter(Sheet2!A2:B, len(Sheet2!A2:A))
does the same for Sheet2. Separated by ; these get stacked one over the other.
sort({...}, 1, true)
sorts by the first column, ascending.
You can also combine non-consecutive columns in this way, like E and J. The embedded array would have the structure
{Sheet1!E, Sheet1!J; Sheet2!E, Sheet2!J}
where commas separate columns and semicolon separates rows. Specifically.
={filter(Sheet1!E2:E, len(Sheet1!E2:E)), filter(Sheet1!J2:J, len(Sheet1!E2:E)); filter(Sheet2!E2:E, len(Sheet2!E2:E)), filter(Sheet2!J2:J, len(Sheet2!E2:E))}
(and then apply sort
, as before).
Note that both columns must be filtered by the same criterion (whatever it is) to make sure their contents align.
=ARRAYFORMULA(TRANSPOSE(SPLIT(CONCATENATE(IFERROR(IF((IF(row(A1:A50)=TRANSPOSE(ROW(A1:A50)),ROW(A1:A50),TRANSPOSE(ROW(A1:A50))))<=A1:A4,(IF(row(A1:A50)=TRANSPOSE(ROW(A1:A50)),ROW(A1:A50),TRANSPOSE(ROW(A1:A50))))& "🍭",))),"🍭")))
- Will work upto max(a:a)<=50 and count(a:a)<=50; Will work more than that with modifications below
- Change all
A50
to A250
,If max(A:A) or count(a:a)=250.
How it works
Creates a virtual 1,2,3...
matrix with this formula:(Here,5 is the max(A:A))
ARRAYFORMULA(IF(row(A1:A5)=TRANSPOSE(ROW(A1:A5)),ROW(A1:A5),TRANSPOSE(ROW(A1:A5))))
If the matrix is lesser than given A1:A4
values, Take only those values with a lollipop.
concatenate the whole matrix ,split the lollipop and transpose to give your array.
Best Answer
You can create a Cartesian product like the one you describe with string concatenation like this:
=arrayformula( flatten( transpose(A2:A4) & " " & B2:B4 ) )