+ Reply to Thread
Results 1 to 9 of 9

Auto copy fill based cell match?

  1. #1
    Registered User
    Join Date
    07-05-2012
    Location
    Florida US
    MS-Off Ver
    Excel 2007
    Posts
    96

    Auto copy fill based cell match?

    HI,
    In sheet 1, there is an edited schools' list - hundreds of schools -
    with ID numbers in column B
    and a "grade" in column C - but some grades are wrong.

    In sheet 2, there is an unedited schools' list -thousands of schools -
    with ID numbers in column B
    with corrected "grades" in column C.

    How can I cause the correct grade to appear in sheet 1 from sheet 2 on column C based on matching the ID numbers in column C?

    Thanks,
    Synthia
    (sheet is too big to attach)

  2. #2
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Auto copy fill based cell match?

    You could try something like this (assumes ALL ID #'s are unique, and assuming actual data starts in row 2 of each sheet)
    In C2 on 'Sheet1':
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    change the 10000 to what the actual range for second sheet should be and drag down as far as needed

    That's about the best I can offer without seeing something

    Hope this helps

    EDIT_
    Missed the last argument on the formula above, it SHOULD read as:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Last edited by dredwolf; 03-30-2013 at 09:25 PM.
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  3. #3
    Registered User
    Join Date
    07-05-2012
    Location
    Florida US
    MS-Off Ver
    Excel 2007
    Posts
    96

    Re: Auto copy fill based cell match?

    Thanks,
    I modified the file to be able to upload it; I figured out how to ask the question.

    In the attached file, sheet 1 is the edited "schools" list, with missing or incorrect data, in many columns:
    Sheet 2 has all the schools, and the correct and complete data for all the schools in the same columns.

    I need Sheet 1 to have all the correct data from Sheet 2, but only for the matching rows.(only for the edited schools list)

    I know that changes things a lot, but in trying to insert your solution and trying to figure out how to reduce the file size to be able to attach the sheet, I realized the basic problem:
    I need all the data from sheet 2 in one place but ONLY for the schools that are on the list on sheet 1, in whatever is the easiest/quickest way to do it...?

    Thanks for your help.
    Synthia
    Attached Files Attached Files

  4. #4
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Auto copy fill based cell match?

    Try this Array Formula (Cntrl+Shift+Enter, not just Enter) in H2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Drag down and across

    The formula uses the School # and the District Name to get the matching Schools data, as there are multiple occurrences of the School # alone (the first one had 26 possible matches )
    I used Conditional Formatting to make 'Blank' grades appear 'Blank' rather than as '0'

    Hope this helps
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-05-2012
    Location
    Florida US
    MS-Off Ver
    Excel 2007
    Posts
    96

    Re: Auto copy fill based cell match?

    Hi,
    Little glitch of some kind? I get a #VALUE! error....
    Thanks,
    Synthia

  6. #6
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Auto copy fill based cell match?

    where?, I just downloade what I attached and it works fine here ?

  7. #7
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Auto copy fill based cell match?

    Did you ARRAY ENTER the formula? (Cntrl+Shift+Enter, no just Enter)
    you have to do this in the formula bar, and you will know it is correct when the '{' & '}' appear at the start and end of the formula

  8. #8
    Registered User
    Join Date
    07-05-2012
    Location
    Florida US
    MS-Off Ver
    Excel 2007
    Posts
    96

    Re: Auto copy fill based cell match?

    yes, that was the problem, my mistake, when I entered it correctly and drag down and across it worked correctly, perfectly, thank you so very much! I appreciate your help AND patience!!
    Gratefully,
    Synthia

  9. #9
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Auto copy fill based cell match?

    You are welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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