2

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.

query

1 Answer 1

3

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)

enter image description here

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:

3
  • Thanks for the quick response. It's very neat. However, my situation is that the destination names are already there, and the list of names are longer than the list of names in the source. Is there a way to keep the destination names, just finding and copying from the source by names? Or, is there a way to find the new names in the destination names and add them to the original list?
    – sofname
    Feb 24, 2021 at 15:17
  • New info, new solution. Maybe you can accept this answer and post a new one. In any case, please read how to and share a test sheet so as you can be easier helped. Feb 24, 2021 at 15:28
  • The updated version of the question is webapps.stackexchange.com/questions/151217/… .
    – sofname
    Feb 24, 2021 at 18:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.