SOLVED

Mergin duplicates from list but adding their values

%3CLINGO-SUB%20id%3D%22lingo-sub-1572684%22%20slang%3D%22en-US%22%3EMergin%20duplicates%20from%20list%20but%20adding%20their%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1572684%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20would%20like%20to%20merge%20a%20few%20lists%20together%20to%20get%20total%20of%20each%20resources%20needed.%20Is%20anybody%20have%20an%20idea%20or%20any%20suggestion%20which%20could%20help%20me%20into%20it%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3E(Thanks%20in%20advance%2C%20and%20sorry%20if%20my%20english%20isn't%20great)%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22merged%20lists%20and%20values.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F210967i33DD37282D92323F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22merged%20lists%20and%20values.PNG%22%20alt%3D%22merged%20lists%20and%20values.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1572684%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-1572731%22%20slang%3D%22en-US%22%3ERe%3A%20Mergin%20duplicates%20from%20list%20but%20adding%20their%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1572731%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F752439%22%20target%3D%22_blank%22%3E%40LouRene%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20would%20create%20a%20pivot%20table%20based%20on%20the%20data.%3C%2FP%3E%0A%3CP%3EAdd%20the%20Resources%20field%20to%20the%20Rows%20area%20and%20the%20Need%20field%20to%20the%20Values%20area%3B%20Excel%20should%20automatically%20use%20SUM%20as%20aggregate%20function.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1572808%22%20slang%3D%22en-US%22%3ERe%3A%20Mergin%20duplicates%20from%20list%20but%20adding%20their%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1572808%22%20slang%3D%22en-US%22%3E%3CP%3EFab!%20I%20will%20have%20a%20look%20on%20this%20feature%20I%20am%20not%20familiar%20with%20as%20soon%20I'm%20off%20tonight%2C%20but%20it%20seems%20very%20solid!%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20very%20much%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%2C%20as%20soon%20I%20try%20that%2C%20I'll%20come%20back%20with%20review.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1575155%22%20slang%3D%22en-US%22%3ERe%3A%20Mergin%20duplicates%20from%20list%20but%20adding%20their%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1575155%22%20slang%3D%22en-US%22%3E%3CP%3ESo%20I%20tried%20to%20use%20Pivot%20Table%20and%20it%20is%20exactly%20what%20I%20needed%2C%20thanks%20very%20much%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%20!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUnfortunately%2C%20it's%20not%20working%20yet%20as%20the%20values%20from%20%22Resources%22%20are%20already%20results%20from%20the%20sheet%201.%20If%20I%20type%20the%20datas%20and%20do%20a%20pivot%20table%20then%2C%20it%20works%20perfectly.%20But%20if%20the%20datas%20are%20coming%20from%20functions%20the%20SUM%20is%20not%20working.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Sum%20not%20working.PNG%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F211127i4EE785D239F31B45%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Sum%20not%20working.PNG%22%20alt%3D%22Sum%20not%20working.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJust%20in%20case%20it's%20relevant%20to%20share%20more%20details%3A%3C%2FP%3E%3CP%3EIt%20is%20basically%20a%20tool.%20In%20%22Sheet%201%22%20I%20paste%20datas%20from%20a%20database%20which%20included%20useless%20informations.%3C%2FP%3E%3CP%3EThey%20come%20like%20this%20%3A%3C%2FP%3E%3CTABLE%20border%3D%221%22%20width%3D%22100%25%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2250%25%22%3E%3CDIV%20class%3D%22ak-front%22%3E%3CFONT%20size%3D%222%22%3E3%20x%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%20class%3D%22ak-front%22%3E%3CFONT%20size%3D%222%22%3EResource%20A%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%20class%3D%22ak-main%22%3E%3CDIV%20class%3D%22ak-main-content%20%22%3E%3CDIV%20class%3D%22ak-content%22%3E%3CDIV%20class%3D%22ak-text%22%3E%3CFONT%20size%3D%222%22%3EType%3C%2FFONT%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22ak-aside%22%3E%3CFONT%20size%3D%222%22%3EOther%20attribute%3C%2FFONT%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FTD%3E%3CTD%20width%3D%2250%25%22%3E%3CDIV%20class%3D%22ak-title%22%3E%3CFONT%20size%3D%222%22%3E60%20x%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%20class%3D%22ak-title%22%3E%3CFONT%20size%3D%222%22%3EResource%20B%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%20class%3D%22ak-main%22%3E%3CDIV%20class%3D%22ak-main-content%20%22%3E%3CDIV%20class%3D%22ak-content%22%3E%3CDIV%20class%3D%22ak-text%22%3E%3CFONT%20size%3D%222%22%3EType%3C%2FFONT%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22ak-aside%22%3E%3CFONT%20size%3D%222%22%3EAttribute%3C%2FFONT%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CDIV%20class%3D%22ak-column%20ak-container%20col-xs-12%20col-sm-6%22%3E%3CDIV%20class%3D%22ak-list-element%22%3E%3CDIV%20class%3D%22ak-main%22%3E%3CDIV%20class%3D%22ak-main-content%20%22%3E%3CDIV%20class%3D%22ak-aside%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3CDIV%20class%3D%22ak-column%20ak-container%20col-xs-12%20col-sm-6%22%3E%3CDIV%20class%3D%22ak-list-element%22%3E%3CDIV%20class%3D%22ak-main%22%3E%3CDIV%20class%3D%22ak-main-content%20%22%3E%3CDIV%20class%3D%22ak-aside%22%3ESo%20into%20the%20%3CSTRONG%3Esheet%201%3C%2FSTRONG%3E%20I%20need%20a%20few%20step%20to%20trim%2C%20then%20to%20join%2C%20and%20finally%20to%20rebuild%20my%20column%20%3A%3C%2FDIV%3E%3CDIV%20class%3D%22ak-aside%22%3EStep%201%3A%20%3CSTRONG%3E%3CFONT%20color%3D%22%23339966%22%3E%5BH9%5D%3C%2FFONT%3E%3C%2FSTRONG%3E%20%3CFONT%20size%3D%222%22%20color%3D%22%23800080%22%3E%3DIFERROR(IF(AND(E9%3DFALSE%2CF9%3DFALSE%2CG9%3DTRUE)%2CTRIM(CLEAN(D9))%2C%22%22)%2C%22%22)%3C%2FFONT%3E%26nbsp%3B%20%7BRESOURCES%7D%3C%2FDIV%3E%3CDIV%20class%3D%22ak-aside%20lia-indent-padding-left-60px%22%3E%3CSTRONG%3E%3CFONT%20color%3D%22%23339966%22%3E%5BJ9%5D%3C%2FFONT%3E%3C%2FSTRONG%3E%20%3CFONT%20size%3D%222%22%20color%3D%22%23800080%22%3E%3DIF(AND(E9%3DFALSE%2CF9%3DFALSE%2CG9%3DTRUE%2CD9%26lt%3B%26gt%3B%22%22)%2CLEFT(D8%2CLEN(D8)-1)%2C%22%22)%20%3C%2FFONT%3E%7BNEEDS%7D%3C%2FDIV%3E%3CDIV%20class%3D%22ak-aside%22%3EStep%202%3A%20%3CFONT%20color%3D%22%23339966%22%3E%3CSTRONG%3E%5BK6%5D%3C%2FSTRONG%3E%3C%2FFONT%3E%20%3CFONT%20size%3D%222%22%20color%3D%22%23993366%22%3E%3DTEXTJOIN(%22%2C%20%22%2CTRUE%2CH8%3AH39)%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%20class%3D%22ak-aside%22%3EStep%203%3A%20%3CEM%3E%3CSTRONG%3E%3CFONT%20color%3D%22%230000FF%22%3EText%20to%20Column%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FEM%3E%3C%2FDIV%3E%3CDIV%20class%3D%22ak-aside%22%3EStep%204%3A%20%3CSTRONG%3E%3CFONT%20color%3D%22%23339966%22%3E%5BQ13%5D%3C%2FFONT%3E%3C%2FSTRONG%3E%20%3CFONT%20size%3D%222%22%20color%3D%22%23993366%22%3E%3DINDEX(H8%3AJ39%2CMATCH(N13%2CH8%3AH39%2C0)%2C3)%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%20class%3D%22ak-aside%22%3E%3CFONT%20size%3D%222%22%20color%3D%22%23993366%22%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22sheet1.jpg%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F211126i5BF7D6B0053E9C8A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22sheet1.jpg%22%20alt%3D%22sheet1.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FDIV%3E%3CDIV%20class%3D%22ak-aside%22%3E%26nbsp%3B%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1575178%22%20slang%3D%22en-US%22%3ERe%3A%20Mergin%20duplicates%20from%20list%20but%20adding%20their%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1575178%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F752439%22%20target%3D%22_blank%22%3E%40LouRene%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDoes%20it%20help%20if%20you%20change%20the%20formula%20in%20Q13%20to%3C%2FP%3E%0A%3CP%3E%3CFONT%20size%3D%222%22%20color%3D%22%23993366%22%3E%3D--INDEX(H8%3AJ39%2CMATCH(N13%2CH8%3AH39%2C0)%2C3)%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1575232%22%20slang%3D%22en-US%22%3ERe%3A%20Mergin%20duplicates%20from%20list%20but%20adding%20their%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1575232%22%20slang%3D%22en-US%22%3E%3CP%3EWaouh%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%20%2C%20it%20definitely%20does!!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!!%20Actually%2C%20I%20don't%20know%20how%20to%20show%20how%20grateful%20I%20am%2C%20thank%20you%20so%20much!!%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

I would like to merge a few lists together to get total of each resources needed. Is anybody have an idea or any suggestion which could help me into it?

 

(Thanks in advance, and sorry if my english isn't great)

 

merged lists and values.PNG

5 Replies
Best Response confirmed by LouRene (New Contributor)
Solution

@LouRene 

I would create a pivot table based on the data.

Add the Resources field to the Rows area and the Need field to the Values area; Excel should automatically use SUM as aggregate function.

Fab! I will have a look on this feature I am not familiar with as soon I'm off tonight, but it seems very solid!

Thanks very much @Hans Vogelaar, as soon I try that, I'll come back with review.

So I tried to use Pivot Table and it is exactly what I needed, thanks very much @Hans Vogelaar !

 

Unfortunately, it's not working yet as the values from "Resources" are already results from the sheet 1. If I type the datas and do a pivot table then, it works perfectly. But if the datas are coming from functions the SUM is not working.

Sum not working.PNG

 

 

Just in case it's relevant to share more details:

It is basically a tool. In "Sheet 1" I paste datas from a database which included useless informations.

They come like this :

3 x
Resource A
Type
Other attribute
60 x
Resource B
Type
Attribute
 
So into the sheet 1 I need a few step to trim, then to join, and finally to rebuild my column :
Step 1: [H9] =IFERROR(IF(AND(E9=FALSE,F9=FALSE,G9=TRUE),TRIM(CLEAN(D9)),""),"")  {RESOURCES}
[J9] =IF(AND(E9=FALSE,F9=FALSE,G9=TRUE,D9<>""),LEFT(D8,LEN(D8)-1),"") {NEEDS}
Step 2: [K6] =TEXTJOIN(", ",TRUE,H8:H39)
Step 3: Text to Column
Step 4: [Q13] =INDEX(H8:J39,MATCH(N13,H8:H39,0),3)
sheet1.jpg
 

@LouRene 

Does it help if you change the formula in Q13 to

=--INDEX(H8:J39,MATCH(N13,H8:H39,0),3)

Waouh@Hans Vogelaar , it definitely does!!!

 

Thank you!! Actually, I don't know how to show how grateful I am, thank you so much!!