+ Reply to Thread
Results 1 to 12 of 12

Formula to copy data from one sheet to another

  1. #1
    Forum Contributor
    Join Date
    02-14-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2013
    Posts
    269

    Formula to copy data from one sheet to another

    I'm trying to write a formula that will pull data from one sheet ('NewData') into another sheet ('Totals').

    I think possibly a =VLOOKUP would work.

    I would like the formula to go in Col B of 'Totals'.

    The formula should first look at Col A of sheet 'Totals' to find the cell's Name text.
    Then it should look at sheet 'NewData', find the LAST name in Col D that matches
    the name in Col A of sheet 'Totals' AND has data (numeric) in Col B.
    When this match is found, Copy that data value into Col B of 'Totals'.

    Any help would be appreciated!

    I should add that the names (of which there are hundreds) are all sorted alphabetically
    all the time and there are no gaps between names.
    Last edited by ChrisXcel; 06-27-2021 at 02:01 PM. Reason: Added last line...

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365
    Posts
    19,911

    Re: =VLOOKUP possibly?

    Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

  3. #3
    Forum Contributor
    Join Date
    02-14-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2013
    Posts
    269

    Re: =VLOOKUP possibly?

    Here is a visualization...
    Attached Files Attached Files

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,620

    Re: =VLOOKUP possibly?

    Administrative Note:

    We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

    Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

    Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

    (Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Forum Contributor
    Join Date
    02-14-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2013
    Posts
    269

    Re: =VLOOKUP possibly?

    Fulfilled.
    (Thank you Ali for helping me in my thread.)
    Last edited by AliGW; 06-28-2021 at 12:51 AM. Reason: PLEASE don't quote unnecessarily!

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,102

    Re: =VLOOKUP possibly?

    I don't know if you have a solution .....

    in B2

    =LOOKUP(2,1/(--(NewData!$D$2:$D$23=Totals!$A2)*--(NewData!$B$2:$B$23<>0)),NewData!$B$2:$B$23)

  7. #7
    Forum Contributor
    Join Date
    02-14-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2013
    Posts
    269

    Re: =VLOOKUP possibly?

    Wow, that's amazing!
    It works like a charm.

    Thanks very much John, I appreciate it!
    Last edited by AliGW; 06-28-2021 at 12:51 AM. Reason: PLEASE don't quote unnecessarily!

  8. #8
    Forum Contributor
    Join Date
    02-14-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2013
    Posts
    269

    Re: Formula to copy data from one sheet to another

    One more thing that I'd like to do with this data that's very important, is sort it.

    I'd like to sort columns A, B and C in the Totals sheet, sorting by Col. B.

    Currently, using the formula above in Col. B, when I try to sort Col B, everything
    gets messed up (sort is not correct).

    Will I need to copy the data (values) from the columns to another area first and then sort them there?
    Last edited by ChrisXcel; 06-27-2021 at 11:51 PM. Reason: Added last line...

  9. #9
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,102

    Re: Formula to copy data from one sheet to another

    Use "Customer Sort":

    Select columns A:C==>Sort==>"Custom Sort" ==> Data (or column B) ==> OK
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    02-14-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2013
    Posts
    269

    Re: Formula to copy data from one sheet to another

    Quote Originally Posted by JohnTopley View Post
    Use "Customer Sort":

    Select columns A:C==>Sort==>"Custom Sort" ==> Data (or column B) ==> OK
    Thanks again John.

    I'm wondering how you got your B column data as values on the Totals sheet?
    I assume you had your formulae there initially?


    When I try to do the sort with my worksheet it still fails.
    The first 30 or so rows are sorted correctly, but then the next
    170 or so rows don't sort properly.
    Interspersed are some rows with #N/A values in column B.
    I don't know if the #N/A is causing any problems with the sort?

    When I copy the 3 columns to another part of the sheet (as values)
    I am able to sort the rows there. The #N/A rows are simply grouped
    at the top of the sort.

  11. #11
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    19,102

    Re: Formula to copy data from one sheet to another

    Apologies as I didn't check the file I sorted had formulae - it had values: you are correct that it does not sort correctly so the answer is to copy/paste values in column B then sort.

  12. #12
    Forum Contributor
    Join Date
    02-14-2014
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2013
    Posts
    269

    Re: Formula to copy data from one sheet to another

    Quote Originally Posted by JohnTopley View Post
    Apologies as I didn't check the file I sorted had formulae - it had values: you are correct that it does not sort correctly so the answer is to copy/paste values in column B then sort.
    Thanks again John for your help.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Possibly need multiple VLOOKUP, or Index w VLOOKUP?
    By endavidson in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-27-2021, 04:07 PM
  2. [SOLVED] Vlookup (possibly) help
    By benog in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 11-23-2020, 07:23 AM
  3. formula =VLOOKUP(date,table,col,FALSE) to pull data from multiple colums and rows
    By sorensenbrad in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-23-2019, 11:39 AM
  4. If or possibly VLookup formula help
    By LRGy in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-11-2016, 08:58 AM
  5. Vlookup possibly
    By lian.cragg in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-20-2011, 02:50 PM
  6. Vlookup across sheets, nested Vlookup possibly?
    By paid2mkgrlspanic in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 06-09-2009, 05:10 PM
  7. Possibly vlookup
    By Carleilam in forum Excel General
    Replies: 2
    Last Post: 02-03-2008, 06:03 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1