• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Transfer data from one sheet to another based on cell criteria

mayhem

New Member
Greetings

I have 2 sheets and need to transfer the Students Taken in 2018 information from SiteTotals.csv sheet into Schools for CRM matching the schools.

Please help, as I', unsure what is the simplest way to achieve this without manually copying each cell and pasting when working with a sheet of 3000 schools.

Thank you
 

Attachments

  • SiteTotals.csv
    383 bytes · Views: 5
  • Schools for CRM.xlsx
    48.2 KB · Views: 6
I would try Power Query for this purpose.

Open a new Excel document and first open a connection to the xlsx file. Then do the same with the text file, saving each as a connection only within the new document.

The key step is a merge query as a left outer join based on the school names (the unique school number would be better but one doesn't seem to exist within the files) which will bring in the student number data as a new column. Expand the column to extract the numbers you need and either replace the original numbers column or add the update to the original as appropriate.
 

Attachments

  • Schools for CRM 2018.xlsx
    52.4 KB · Views: 13
I would try Power Query for this purpose.

Open a new Excel document and first open a connection to the xlsx file. Then do the same with the text file, saving each as a connection only within the new document.

The key step is a merge query as a left outer join based on the school names (the unique school number would be better but one doesn't seem to exist within the files) which will bring in the student number data as a new column. Expand the column to extract the numbers you need and either replace the original numbers column or add the update to the original as appropriate.

Thank you so much Peter, what a simple and effective solution, I'd been racking my brain with iF and VLOOKUP functions to no avail.

Have a great day
 
Back
Top