I'd like to create a many-to-many table using unique values from multiple columns

Question

I'm not sure if I got the term right, but I have lists of unique values and I want to replicate each values from a lists to correspond to every value from the rest of the lists.

From the following column consisting of unique values.....

column1	column2	column3	column4
a1	b1	c1	d1
a2	b2	c2	d2
a3

I want to create a table like this.

column1	column2	column3	column4
a1	b1	c1	d1
a1	b1	c1	d2
a1	b1	c2	d1
a1	b1	c2	d2
a1	b2	c1	d1
a1	b2	c1	d2
a1	b2	c2	d1
a1	b2	c2	d2
a2	b1	c1	d1
a2	b1	c1	d2
a2	b1	c2	d1
a2	b1	c2	d2
a2	b2	c1	d1
a2	b2	c1	d2
a2	b2	c2	d1
a2	b2	c2	d2
a3	b1	c1	d1
a3	b1	c1	d2
a3	b1	c2	d1
a3	b1	c2	d2
a3	b2	c1	d1
a3	b2	c1	d2
a3	b2	c2	d1
a3	b2	c2	d2

Is there a way to do this in excel, with the use of base functions, without manually replicating each value using copy/paste?

Answer 1

With Office 365 we can use:

=LET(
 rng,A1:D3,
 arr,INDEX(rng,MID(BASE(SEQUENCE(ROWS(rng)^4,,0),ROWS(rng),4),SEQUENCE(,4),1)+1,{1,2,3,4}),
 FILTER(arr,(INDEX(arr,0,1)<>0)*(INDEX(arr,0,2)<>0)*(INDEX(arr,0,3)<>0)*(INDEX(arr,0,4)<>0))
 )

We can also change the formula a bit to not assume 4 columns:

=LET(
    rng,A1:D3,
    clm,COLUMNS(rng),
    sclm,SEQUENCE(,clm),
    rw,ROWS(rng),
    arr,INDEX(rng,MID(BASE(SEQUENCE(rw^clm,,0),rw,clm),sclm,1)+1,sclm),
    FILTER(arr,BYROW(arr,LAMBDA(a,COUNT(--a)=0))))

This will do any number of columns (within reason).