Forum Discussion
Transfer data from one table to another table with different layout
I am trying to transfer all the data from one table that has a lot of data in it into another table that has a different layout but I don't know how to do it quickly without having to copy paste each entry individually.
I have attached a spreadsheet showing the source and destination table and an example of what I want it too look like.
I hope someone can help, Thanks in Advance
4 Replies
Here is my solution
Note: I changed the track time from 4 hours 37 minutes to 4 minutes 37 seconds and so on. I think that would be more realistic.
But the track name you need to encode it first.
put numbers to identify the column data to return in your vlookup formula(above your source)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 Track Name Time Track # Artist Disc # Album Album Artist Codec Date Added Notes Genre Year Cost URL Track Name let me go Codec ALAC Hello try pasting this formula on codec blank space
=IFERROR(VLOOKUP($C$23,b11:016,8,0),"")
time is on column 8(above legend)
copy on other blanks:
Time would be :=IFERROR(VLOOKUP($C$23,b11:016,2,0),"")
time is on column 2(above legend)
Then on it goes you need to reformat cells to dates and time stuff after pasting formulas.
Next code change address of the absolute cell to C27(press f4 to turn to absolute), hence new formula for codec is:
=IFERROR(VLOOKUP($C$27,b11:016,8,0),"")
2 Track Name Codec Cost Then copy from 1st set above then just change the absolute cell to C7(press F4).
ihatebillsDetlef_Lewin Thanks for the reply
I tried what you suggested with the vlookup but am not quite sure how to implement it in the spreadsheet (attached) I have the source sheets (2) and the destination sheets (2).
Does the vlookup move the data from the source sheet to destination sheet or does it reference the source sheet in the destination document (so i would need to keep the source sheets in workbook either in plain view or hidden)
hello I have attached a file with steps to do to make things easier
to answer your question:
vlookup just uses its formula and does nothing to your source, so it is safe and won't be moved.
note if you move your data - source data to another sheet just make sure your vlookup formulas are refreshed or recoded because it may return an error message or wont work basically your source has changed destination.
