Transfer and changing data with two different Excel sheets

[scaffnull]

Hi,

What is the smartest way to transfer data from one spreadsheet to another but not take the whole information from one? I have one spreadsheet with information and need to transfer only some information to another one spreadsheet. Hope this makes sense. Thanks

[#2]

@scaffnull That's a broad question. "Transfer only some information to another spreadsheet". Would that be a particular range, or groups of cells spread out all over the place? Show us what you are dealing with and I'm sure you'll get help.

[#3]

Thank you for your help, and yes it was not good stated. But if I have work numbers (excelsheet1) on one of my spreadsheet, and every week I get new information from a new spreadsheet (excelsheet2) with hours worked on that work number. Instead of manually type in every hour for that specific work number, can I use IF statements to transfer the data? Simply IF# on (excelsheet2) match with (excelsheet1) then add hours of work to that work number.

Thank you for your reply.

[#4]

@scaffnull You could just create one file/table (cut and paste) with all weekly hours per job number and then summarize the lot with a pivot table. Or, if you want to avoid the manual copying and pasting, look into PowerQuery (PQ). Set up the correct routine(s) once and just add weekly data files to a particular folder on your system and refresh the query(ies). It is as easy as it sounds, BUT you need to learn some PQ basics first.

But, to be sure that these are indeed the smartest ways, please upload some sample data. Replace any confidential information with something that is made up.

[#5]

@Riny_van_Eekelen  Been trying this out for a week now with any success. Both trying to learn PowerQuery and testing it in Power BI. But I attached two files.
So to explain, I get Sheet1 with updated data every week, and Sheet2 is where I want to add/transfer/delete the data based on Sheet1. So, for the second row in Sheet1 the "WorkNr" matches a "WorkNr" in Sheet2, then I want to add the Hours Worked on to Sheet2, since the "WorkNr" matched. And that goes for row 5,8,11(from Sheet1) as well since the "WorkNr" match. But for row 14 there is a new WorkNr that doesn't match any WorkNr in Sheet2. I then want to add the new WorkNr on to Sheet2, with WorkNr, Hours Worked, Company, Place (the formatting I get from Sheet1 is not ideal, but I found a way in Power Query to extract that into a new column for Company, and Place). Then for row 14, and 23 (in Sheet1) they have matches with WorkNr,(twice job on that in one week) so all I want to do there is to add the total Work Hours to Sheet2. So in the end, if WorkNr match, only add hours, if WorkNr do not match, add a new row in Sheet2 with all the criteria. So when automated, total work hours with matching WorkNr adds up to the total hours from Sheet1 (this case 272).

Hope this makes some sense since I'm fairly new to the PowerQuery, PowerBI and Excel world.

Thanks!

[#6 · Accepted solution]

@scaffnull Combined both sheets into one to make it easier to follow through the entire process. The attached file contains a "dirty" PQ solution that need optimizing. Bur just to be sure that the Invoice table is something you had in mind. Perhaps it enables you to continue with what you developed earlier. If not, come back here.

[#7]

@Riny_van_Eekelen I truly appreciate your help. But to mention, the sheet2 originally consist of 2000 rows and sheet1 with around 300 rows. Therefore I believe it would be a messy job to go that route. And since I'm fairly beginner I need to learn more formulas using DAX, working with Power BI, so the goal is to automate the process where sheet1 gets applied to all the rules and formulas and after blend in with sheet2. But again, thanks for your help! And I'll get to work and learn the whole concept and trix to fix this :)

[#8]

@scaffnull 

you need to provide two versions of sheet1.xls, the first version that have original worknr with the existing worknr in sheet2 and the 2nd version that have worknr that does not exist in the first version (in order to test) and 1 version of sheet 2 or you can do the testing yourself since you already have the dataset

[#9]

@Yea_So So then when I have 2 versions of sheet1, do I delete duplicates? Or how will I proceed to blend the data together with sheet2? Thanks for your help.

[#10]

@scaffnull 

Never delete any business records unless you are absolutely sure that you do not need them anymore and one can only be sure of that is if when time passes maybe 15-30years as a general rule.

1. you combine both workbooks dynamically using power query

2. then you can decide which one you,re going to try first by formulas or by parameter queries

those are the two roads you can pick, choose one

[#11]

@Yea_So Would always save a spare copy :) appreciate the tips, but will have to dig deeper into this since I have no idea how to use maybe DAX to write a statement to have (if 2 numbers match, then delete one of them and add hours).

Thanks again!

[#12]

@scaffnull 

DAX is good for creating reports, but you have to have all dataset present to do calculations that is why you do not delete any of your dataset

[#13]

what data transfer one sheet to another url sheet using office script?

@Riny_van_Eekelen