Merge and allocate in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1708080%22%20slang%3D%22en-US%22%3EMerge%20and%20allocate%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1708080%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3EInformation%3A%3C%2FP%3E%3CP%3EI'm%20using%20Excel%202010%2C%20there%20are%201000%2Brows.%3CBR%20%2F%3EI%20have%202%20excel%20sheets%20and%20I%20have%20reducted%20the%20needed%20Data%20down%20to%203%20different%20ones.%3C%2FP%3E%3CP%3EOn%20sheet%201%2C%20I%20have%20in%20column%20A%20all%20the%20product%20%22NAMES%22%20and%20in%20column%20B%20their%20respective%20%22QUALITY%22%3C%2FP%3E%3CP%3EIn%20sheet%202%2C%20I%20have%20in%20column%20A%20again%20all%20the%20%22NAMES%22%20and%20in%20column%20B%20their%20respective%20%22WEIGHT%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20do%202%20things%20in%20one%20go%3A%20Merge%20these%204%20columns%20onto%201%20sheet%20while%20allocating%20e.g.%20the%20%22WEIGHT%22%20onto%20the%20respective%20row%20where%20its%20counterpart%20%22NAME%22%20is%2C%20so%20I'm%20only%20left%20with%20%22NAME%22%2C%20%22QUALITY%22%20AND%20%22WEIGHT%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20order%20of%20the%20above%20mentioned%20data%20sets%20don't%20matter.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1708080%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Etraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1708146%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20and%20allocate%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1708146%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F584092%22%20target%3D%22_blank%22%3E%40Uguey%3C%2FA%3E%2C%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20your%20sheet1%20and%20sheet2%20have%20the%20exact%20same%20names%20in%20your%20%22NAMES%22%20column%2C%20how%20about%20you%20simply%20sort%20them%20in%20alphabetical%20order%2C%20copy%20the%20weight%20column%20from%20sheet2%20and%20paste%20it%20to%20sheet1%3F%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20will%20work%20fine%20if%20and%20only%20if%20you%20have%20the%20exact%20same%20NAMES%20column%20in%20both%20sheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1708167%22%20slang%3D%22en-US%22%3ERe%3A%20Merge%20and%20allocate%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1708167%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F584092%22%20target%3D%22_blank%22%3E%40Uguey%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlso%2C%20I%20suggest%20you%20upgrade%20from%20Office%202010%20to%20the%20latest.%26nbsp%3B%3C%2FP%3E%3CP%3EOffice%202010%20will%20reach%20end%20of%20support%20on%20October%2013%202020.%20Meaning%20it%20will%20not%20receive%20security%20patches%20anymore%20and%20will%20leave%20your%20system%20vulnerable.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi 

Information:

I'm using Excel 2010, there are 1000+rows.
I have 2 excel sheets and I have reducted the needed Data down to 3 different ones.

On sheet 1, I have in column A all the product "NAMES" and in column B their respective "QUALITY"

In sheet 2, I have in column A again all the "NAMES" and in column B their respective "WEIGHT"

 

I would like to do 2 things in one go: Merge these 4 columns onto 1 sheet while allocating e.g. the "WEIGHT" onto the respective row where its counterpart "NAME" is, so I'm only left with "NAME", "QUALITY" AND "WEIGHT".

 

The order of the above mentioned data sets don't matter.

 

Thank you in advance.

4 Replies

Hi @Uguey

If your sheet1 and sheet2 have the exact same names in your "NAMES" column, how about you simply sort them in alphabetical order, copy the weight column from sheet2 and paste it to sheet1? 

This will work fine if and only if you have the exact same NAMES column in both sheet.

@Uguey 

 

Also, I suggest you upgrade from Office 2010 to the latest. 

Office 2010 will reach end of support on October 13 2020. Meaning it will not receive security patches anymore and will leave your system vulnerable. 

Thanks, I forgot to mention that the NAMES are the same but some only appear only in sheet1 and some only in sheet2, that's why I'm asking for a possibility to auto-allocate.
I'm sorry for the missing information.

@Uguey,

 

In this case, can't you use vlookup?

=VLOOKUP(A2,Sheet2!A:B,2,0)

 

Bennadeau_0-1600954809352.png