The way to combine and add columns based on its value?

%3CLINGO-SUB%20id%3D%22lingo-sub-2832941%22%20slang%3D%22en-US%22%3EThe%20way%20to%20combine%20and%20add%20columns%20based%20on%20its%20value%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2832941%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHello%2C%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3Ehope%20you%20are%20doing%20great%20today!%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI'm%20looking%20for%20a%20way%20to%20merge%20a%20couple%20of%20thousands%20of%20rows%20-%2050-100k%2Fdocument.%20If%20the%20name%20(A1)%20is%20the%20same%2C%20then%20merge%20it%20with%20all%20other%20rows%20with%20that%20name%20and%20then%20add%20all%20of%20the%20values%20in%20B1%20of%20those%20rows.%20So%20basically%20(see%20the%20image)%2C%20start%20with%20the%20yellow%20sheet%20and%20end%20up%20with%20the%20green%20one.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Excel.png%22%20style%3D%22width%3A%20567px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F316455i026C4C2EBFF02300%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Excel.png%22%20alt%3D%22Excel.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2832941%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%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2832999%22%20slang%3D%22en-US%22%3ERe%3A%20The%20way%20to%20combine%20and%20add%20columns%20based%20on%20its%20value%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2832999%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1181710%22%20target%3D%22_blank%22%3E%40vitekprchal%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDid%20you%20try%20PivotTable%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUpdate%3A%3C%2FP%3E%3CP%3ESorry.%26nbsp%3B%20I%20forgot%20you%20have%20many%20documents.%26nbsp%3B%20PivotTable%20may%20not%20suit%20your%20needs.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2833033%22%20slang%3D%22en-US%22%3ERe%3A%20The%20way%20to%20combine%20and%20add%20columns%20based%20on%20its%20value%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2833033%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F97717%22%20target%3D%22_blank%22%3E%40Willy%20Lau%3C%2FA%3E%26nbsp%3B%20Hey%2C%20Thank%20you%20for%20the%20reply.%20Yep%2C%20pivot%20table%20will%20be%20too%20clunky%20for%2050-100k%20rows.%3CBR%20%2F%3E%3CBR%20%2F%3ERather%20considering%20an%20automated%20process%20to%20compile%20the%20files%20as%20I%20need.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20already%20tried%20this%3A%20%3CA%20href%3D%22https%3A%2F%2Fwww.extendoffice.com%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.extendoffice.com%2F%3C%2FA%3E%20%2C%20but%20it%20took%2015%20minutes%20to%20compile%201000%20rows%3B%20so%20I%20think%20its%20very%20GPU%2FCPU%20heavy.%3C%2FLINGO-BODY%3E
New Contributor

Hello,
hope you are doing great today!

I'm looking for a way to merge a couple of thousands of rows - 50-100k/document. If the name (A1) is the same, then merge it with all other rows with that name and then add all of the values in B1 of those rows. So basically (see the image), start with the yellow sheet and end up with the green one.

Excel.png

4 Replies

@vitekprchal 

Did you try PivotTable?

 

Update:

Sorry.  I forgot you have many documents.  PivotTable may not suit your needs.

@Willy Lau  Hey, Thank you for the reply. Yep, pivot table will be too clunky for 50-100k rows.

Rather considering an automated process to compile the files as I need.

I already tried this: https://www.extendoffice.com/ , but it took 15 minutes to compile 1000 rows; so I think its very GPU/CPU heavy.

@vitekprchal Sounds like something for PowerQuery. Connect to the data in the left table. Group by Name, Sum by Value. Shouldn't take too much time.