Mergin duplicates from list but adding their values

[LouRene]

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)

[#2 · Accepted 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.

[#3]

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.

[#4]

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.

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)

[#5]

@LouRene 

Does it help if you change the formula in Q13 to

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

[#6]

Waouh@Hans Vogelaar , it definitely does!!!

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