Google Sheets – How to Use Query to Find Row of Same Name and Copy Value

formulasgoogle sheetsgoogle-sheets-arraysgoogle-sheets-querysorting

I am trying to use a query to copy values from one column to another.
Both source and destination columns have a name on their left columns, and the name must match. The source names could be duplicated, and in that case, I would like to have the entry on the bottom. There could be empty rows as well.

Please see the picture.

Best Answer

Please use the following
(Adjust ranges to your needs)

=SORTN(QUERY({INDEX(ROW(D2:D)),D2:E}, 
                  "select Col2, Col3 
                   where Col2 is not null 
                   order by Col2, Col1 desc"),
     9^9,2,1,1)

How the formula works

We add a virtual column based on each row to our query and order the query.
We then -in our SORTN function- use a very large number like 9^9 so as to include any future references of column D

Functions used:

Related Solutions

Google Sheets – How to Transpose Ranges Repeated by Row

Nowadays there is a simpler way.

=ARRAYFORMULA(SPLIT(FLATTEN(<row headers>&"|"&<column headers>&"|"&<values area>),"|"))

For the OP case, 101 names , 365 days

=ARRAYFORMULA(SPLIT(FLATTEN(Sheet1!A2:A101&"|"&Sheet1!A1:MA1"&"|"&Sheet1!B2:MA101),"|"))

Just add the headers above the formula.

Ref. answer to How do you create a “reverse pivot” in Google Sheets?

=Sheet1!A:A I can bring in the name from the source list, but I have to copy it down for the number of dates. How can I have it match the number of dates?

If you are looking for a formula-based solution the following formula will work for the first "name" row

(for testing purposes, it only includes the first 31 days, but you could easily change AF by ZZ)

=ArrayFormula(SPLIT(TRANSPOSE(Sheet1!B1:AF1)&"|"&TRANSPOSE(Sheet1!A2&"|"&Sheet1!B2:AF2),"|"))

If you are able to work with large formulas you could create an array repeating the SPLIT function and its arguments, once by each "name" row, in the following way:

=ArrayFormula({ SPLIT(TRANSPOSE(Sheet1!B1:AF1)&"|"&TRANSPOSE(Sheet1!A2&"|"&Sheet1!B2:AF2),"|"); SPLIT(TRANSPOSE(Sheet1!B1:AF1)&"|"&TRANSPOSE(Sheet1!A3&"|"&Sheet1!B3:AF3),"|") })

Another alternative is to add the formula every 366 rows. In order to avoid to have to edit the formula manually you could use INDIRECT, ROW and ROUNDDOWN

=ArrayFormula(SPLIT(TRANSPOSE(Sheet1!$B$1:$AF$1)&"|"&TRANSPOSE(INDIRECT("Sheet1!$A"&ROUNDDOWN((ROW()-2)/31)+2,TRUE)&"|"&INDIRECT("Sheet1!$B"&ROUNDDOWN((ROW()-2)/31)+2&":AF"&ROUNDDOWN((ROW()-2)/31)+2,TRUE)),"|"))

NOTE: Replace 31 by 365

=TRANSPOSE(Sheet1!$C$1:$Z$1) I can use transpose to get the dates/values, but it's fixed range. How can I make it dynamic to import dates as they are added?

If the number of dates isn't fixed, the formula complexity increases. To help us, the following named ranges should be created on an auxiliary sheet

Columns =COUNTIF(Sheet1!1:1,"<>")
DateHeaders ="Sheet1!R1C2:R1C"&Columns

The resulting formula is:

=ArrayFormula(SPLIT(TRANSPOSE(INDIRECT(DateHeaders,FALSE))&"|"&TRANSPOSE(Sheet1!A2&"|"&INDIRECT("Sheet1!R2C2:R2C"&Columns,FALSE)),"|"))

The above formula makes use of INDIRECT and R1C1 notation and it works only for the first "name" row.

As was mentioned previously, one way is to make an array with SPLIT and its arguments for each row.

=ArrayFormula({ SPLIT(TRANSPOSE(INDIRECT(DateHeaders,FALSE))&"|"&TRANSPOSE(Sheet1!A2&"|"&INDIRECT("Sheet1!R2C2:R2C"&Columns,FALSE)),"|"); SPLIT(TRANSPOSE(INDIRECT(DateHeaders,FALSE))&"|"&TRANSPOSE(Sheet1!A3&"|"&INDIRECT("Sheet1!R3C2:R3C"&Columns,FALSE)),"|") })

If we don't want to use an array, then we could repeat the following formula manually

First we should add an additional named range

DatesCount =Columns-1

=ArrayFormula(SPLIT(TRANSPOSE(INDIRECT(DateHeaders,FALSE))&"|"&TRANSPOSE(INDIRECT("Sheet1!$A"&ROUNDDOWN((ROW()-2)/DatesCount)+2,TRUE)&"|"&INDIRECT("Sheet1!R"&ROUNDDOWN((ROW()-2)/DatesCount)+2&"C2:R"&ROUNDDOWN((ROW()-2)/DatesCount)+2&"C"&Columns,FALSE)),"|"))

But the above formula implies to move or delete and add again the above formula to do every time that a date is added which could be very tedious and prone to error so it's very likely that the best approach is to use an array.

Another alternative is to use an script. Below are some links to Q&A about "unpivot data"

NOTE: The built in QUERY function relies on Google QUERY Language which doesn't include an UNPIVOT function.

Google Sheets – Use Multiply Line Results from QUERY or FILTER

=SUM(ARRAYFORMULA({QUERY('Customer Orders'!$A$2:$C, 
                         "select B where C ='"&A2&"'", 0)} *
                  {ARRAYFORMULA(VLOOKUP(
                   QUERY('Customer Orders'!$A$2:$C, 
                         "select A where C ='"&A2&"'", 0),
                   Database!$A$2:$B,2,0))}))

Best Answer

Related Solutions

Google Sheets – How to Transpose Ranges Repeated by Row

Google Sheets – Use Multiply Line Results from QUERY or FILTER

Related Topic