Home

Transfer data from one table to another table with different layout

%3CLINGO-SUB%20id%3D%22lingo-sub-741216%22%20slang%3D%22en-US%22%3ETransfer%20data%20from%20one%20table%20to%20another%20table%20with%20different%20layout%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-741216%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20transfer%20all%20the%20data%20from%20one%20table%20that%20has%20a%20lot%20of%20data%20in%20it%26nbsp%3B%20into%20another%20table%20that%20has%20a%20different%20layout%20but%20I%20don't%20know%20how%20to%20do%20it%20quickly%20without%20having%20to%20copy%20paste%20each%20entry%20individually.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20a%20spreadsheet%20showing%20the%20source%20and%20destination%20table%20and%20an%20example%20of%20what%20I%20want%20it%20too%20look%20like.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20someone%20can%20help%2C%20Thanks%20in%20Advance%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-741216%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-741314%22%20slang%3D%22en-US%22%3ERe%3A%20Transfer%20data%20from%20one%20table%20to%20another%20table%20with%20different%20layout%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-741314%22%20slang%3D%22en-US%22%3E%3CP%3EBut%20the%20track%20name%20you%20need%20to%20encode%20it%20first.%3C%2FP%3E%3CP%3Eput%20numbers%20to%20identify%20the%20column%20data%20to%20return%20in%20your%20vlookup%20formula(above%20your%20source)%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3E9%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3E11%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3E13%3C%2FTD%3E%3CTD%3E14%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CSTRONG%3E%3CU%3ETrack%20Name%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E%3CU%3ETime%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E%3CU%3ETrack%20%23%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E%3CU%3EArtist%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E%3CU%3EDisc%20%23%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E%3CU%3EAlbum%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E%3CU%3EAlbum%20Artist%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E%3CU%3ECodec%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E%3CU%3EDate%20Added%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E%3CU%3ENotes%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E%3CU%3EGenre%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E%3CU%3EYear%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E%3CU%3ECost%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3E%3CU%3EURL%3C%2FU%3E%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CSTRONG%3ETrack%20Name%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3Elet%20me%20go%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CSTRONG%3ECodec%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3EALAC%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EHello%20try%20pasting%20this%20formula%20on%20codec%20blank%20space%3C%2FP%3E%3CP%3E%3DIFERROR(VLOOKUP(%24C%2423%2Cb11%3A016%2C8%2C0)%2C%22%22)%3C%2FP%3E%3CP%3E%3CSPAN%3Etime%20is%20on%20column%208(above%20legend)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Ecopy%20on%20other%20blanks%3A%3C%2FP%3E%3CP%3ETime%20would%20be%20%3A%3D%3CSPAN%3EIFERROR(VLOOKUP(%24C%2423%2Cb11%3A016%2C2%3C%2FSPAN%3E%3CSPAN%3E%2C0)%2C%22%22)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Etime%20is%20on%20column%202(above%20legend)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThen%20on%20it%20goes%20you%20need%20to%20reformat%20cells%20to%20dates%20and%20time%20stuff%20after%20pasting%20formulas.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ENext%20code%20change%20address%20of%20the%20absolute%20cell%20to%20C27(press%20f4%20to%20turn%20to%20absolute)%2C%20hence%20new%20formula%20for%20codec%20is%3A%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CSPAN%3E%3DIFERROR(VLOOKUP(%24C%2427%2Cb11%3A016%2C8%2C0)%2C%22%22)%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E%3CSTRONG%3ETrack%20Name%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CSTRONG%3ECodec%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CSTRONG%3ECost%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20copy%20from%201st%20set%20above%20then%20just%20change%20the%20absolute%20cell%20to%20C7(press%20F4).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F372172%22%20target%3D%22_blank%22%3E%40ndg93%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-741329%22%20slang%3D%22en-US%22%3ERe%3A%20Transfer%20data%20from%20one%20table%20to%20another%20table%20with%20different%20layout%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-741329%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F372172%22%20target%3D%22_blank%22%3E%40ndg93%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20is%20my%20solution%3C%2FP%3E%3CP%3ENote%3A%20I%20changed%20the%20track%20time%20from%204%20hours%2037%20minutes%20to%204%20minutes%2037%20seconds%20and%20so%20on.%20I%20think%20that%20would%20be%20more%20realistic.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-741363%22%20slang%3D%22en-US%22%3ERe%3A%20Transfer%20data%20from%20one%20table%20to%20another%20table%20with%20different%20layout%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-741363%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365432%22%20target%3D%22_blank%22%3E%40ihatebills%3C%2FA%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%20Thanks%20for%20the%20reply%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20what%20you%20suggested%20with%20the%20vlookup%20but%20am%20not%20quite%20sure%20how%20to%20implement%20it%20in%20the%20spreadsheet%20(attached)%20I%20have%20the%20source%20sheets%20(2)%20and%20the%20destination%20sheets%20(2).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20the%20vlookup%20move%20the%20data%20from%20the%20source%20sheet%20to%20destination%20sheet%20or%20does%20it%20reference%20the%20source%20sheet%20in%20the%20destination%20document%20(so%20i%20would%20need%20to%20keep%20the%20source%20sheets%20in%20workbook%20either%20in%20plain%20view%20or%20hidden)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-753749%22%20slang%3D%22en-US%22%3ERe%3A%20Transfer%20data%20from%20one%20table%20to%20another%20table%20with%20different%20layout%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-753749%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F372172%22%20target%3D%22_blank%22%3E%40ndg93%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehello%20I%20have%20attached%20a%20file%20with%20steps%20to%20do%20to%20make%20things%20easier%3C%2FP%3E%3CP%3Eto%20answer%20your%20question%3A%3C%2FP%3E%3CP%3Evlookup%20just%20uses%20its%20formula%20and%20does%20nothing%20to%20your%20source%2C%20so%20it%20is%20safe%20and%20won't%20be%20moved.%3C%2FP%3E%3CP%3Enote%20if%20you%20move%20your%20data%20-%20source%20data%20to%20another%20sheet%20just%20make%20sure%20your%20vlookup%20formulas%20are%20refreshed%20or%20recoded%20because%20it%20may%20return%20an%20error%20message%20or%20wont%20work%20basically%20your%20source%20has%20changed%20destination.%3C%2FP%3E%3C%2FLINGO-BODY%3E
ndg93
New Contributor

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

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)

1234567891011121314
Track NameTimeTrack #ArtistDisc #AlbumAlbum ArtistCodecDate AddedNotesGenreYearCostURL

 

Track Namelet me go
CodecALAC

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),"")

 

2Track Name 
 Codec 
 Cost 

 

Then copy from 1st set above then just change the absolute cell to C7(press F4).

 

@ndg93 

@ndg93 

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.

 

@ihatebills@Detlef 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)

@ndg93 

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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
11 Replies