3

I don't know or remember the technical name of what I'm looking for but I think an example will be enough for you to understand exactly what I'm looking for.

Given table A

a   x1
b   x2
c   x1

and Table B

x1  x
x1  y
x1  z
x2  p
x2  z

I Need Table C

a   x
a   y
a   z
b   p
b   z
c   x
c   y
c   z

I'm looking for a formula or a set of them to get table C

I guess just need to add an extra row on the C table with each value of the first column on the table A for each corresponding value of TableA!Column2 to TableB!Column1 But I can't find how

I think this is a simple SQL Inner Join.

player0
  • 124,011
  • 12
  • 67
  • 124
White_King
  • 748
  • 7
  • 21

3 Answers3

2

try:

=ARRAYFORMULA({TRIM(FLATTEN(SPLIT(QUERY(REPT(IF($A2:A="",,$A2:A&"♦"), IFNA(VLOOKUP($B2:$B, QUERY({$D2:$D}, 
 "select Col1,count(Col1) where Col1 is not null group by Col1"), 2, 0))),,9^9), "♦"))), IFNA(VLOOKUP(
 TRIM(FLATTEN(SPLIT(QUERY(REPT(IF($A2:A="",,$B2:B&"♦"), IFNA(VLOOKUP($B2:$B, QUERY({$D2:$D}, 
 "select Col1,count(Col1) where Col1 is not null group by Col1"), 2, 0))),,9^9), "♦")))&COUNTIFS(
 TRIM(FLATTEN(SPLIT(QUERY(REPT(IF($A2:A="",,$A2:A&"♦"), IFNA(VLOOKUP($B2:$B, QUERY({$D2:$D}, 
 "select Col1,count(Col1) where Col1 is not null group by Col1"), 2, 0))),,9^9), "♦"))), 
 TRIM(FLATTEN(SPLIT(QUERY(REPT(IF($A2:A="",,$A2:A&"♦"), IFNA(VLOOKUP($B2:$B, QUERY({$D2:$D}, 
 "select Col1,count(Col1) where Col1 is not null group by Col1"), 2, 0))),,9^9), "♦"))), ROW(INDIRECT("O1:O"&COUNTA(
 TRIM(FLATTEN(SPLIT(QUERY(REPT(IF($A2:A="",,$B2:B&"♦"), IFNA(VLOOKUP($B2:$B, QUERY({$D2:$D}, 
 "select Col1,count(Col1) where Col1 is not null group by Col1"), 2, 0))),,9^9), "♦")))))), "<="&ROW(INDIRECT("O1:O"&COUNTA(
 TRIM(FLATTEN(SPLIT(QUERY(REPT(IF($A2:A="",,$B2:B&"♦"), IFNA(VLOOKUP($B2:$B, QUERY({$D2:$D}, 
 "select Col1,count(Col1) where Col1 is not null group by Col1"), 2, 0))),,9^9), "♦"))))))), 
 {D2:D&COUNTIFS(D2:D, D2:D, ROW(O2:O), "<="&ROW(O2:O)), E2:E}, 2, 0))})

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • I'm sorry but I have duplicated data over column2 in table A, I thought this was clear. And this is not working for duplicates – White_King Jan 20 '21 at 13:01
  • Check out the Sheet Problem Definition on this Spreadsheet Sample :) https://docs.google.com/spreadsheets/d/1vsCO18TAbwaVAAQZLPol1jCE1cPX1n-L2LroK649CSc/edit#gid=192736100 – White_King Jan 20 '21 at 20:43
  • 1
    Wow, it seems to work, but the level of knowledge you have to have for this thing is amazing. I'm just gonna use it. Thank you so much! – White_King Jan 20 '21 at 22:37
  • Be sure all ranks have the same size. – White_King Jan 20 '21 at 23:03
1

What you are trying to do is a JOIN in SQL, which duplicates values. Inner, left, right or full defines the behavior when there are no matches on both sides.

The easiest way I can think to do this is by using Google Apps Script to make a custom function to use in a formula:

function JOINRANGES(range1, index1, range2, index2) {
 const result = []
 for(let row1 of range1) {
   for (let row2 of range2) {
     if (row1[index1] == row2[index2]) {
       const r = [...row1, ...row2]
       // Remove the keys themselves
       r.splice(row1.length+index2, 1)
       r.splice(index1, 1)
       result.push(r)
     }
   }
 }
 return result
}

Then you may use it in a sheets formula on the top-left column:

=ARRAYFORMULA(JOINRANGES(A1:B3, 1, D1:E6, 0))

The first and the third arguments are the ranges, and the second and the fourth arguments are the index (starting with 0) of the column inside the range.

The final result:

Screenshot of the result

If you don’t know about Google Apps Script, the about page in the google-apps-script tag has a lot of guides and documentation that is very useful.

Martí
  • 2,651
  • 1
  • 4
  • 11
0

Las answer won't work with duplicates on TableA!column1. Instructions Showing the key formulas:

Just add an intermediate column

JOIN(",",FILTER(E$2:E,D$2:D=L2))

so you can grab the data from them

INDEX(TRANSPOSE(SPLIT(JOIN(",",FILTER(M$2:M,K$2:K=O2)),",")),COUNTIF(O$1:O1,"="&O2)+1) 

into a multiplied column

ArrayFormula(TRIM(FLATTEN(SPLIT(QUERY(REPT(IF(A2:A="",,A2:A&"♦"), IFNA(VLOOKUP(B2:B, QUERY({D2:D}, "select Col1,count(Col1) where Col1 is not null group by Col1"), 2, 0))),,9^9), "♦"))))

here is an example and a Picture. enter image description here

White_King
  • 748
  • 7
  • 21
  • After seeing @player0 work I'm sure this can be done more efficiently and compact, but it is as hard as I can give in this matter, and this is a basic topic for QUERYs on Google Spreadsheet, which I have never seen an answer on the web :S – White_King Jan 21 '21 at 06:45