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)

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.

Mergin duplicates from list but adding their values

5 Replies

HansVogelaar
MVP
Aug 07, 2020
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.
- LouRene
  Copper Contributor
  Aug 08, 2020
  So I tried to use Pivot Table and it is exactly what I needed, thanks very much HansVogelaar !
  
  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)
  - HansVogelaar
    MVP
    Aug 08, 2020
    LouRene
    
    Does it help if you change the formula in Q13 to
    
    =--INDEX(H8:J39,MATCH(N13,H8:H39,0),3)
- LouRene
  Copper Contributor
  Aug 07, 2020
  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 HansVogelaar, as soon I try that, I'll come back with review.

Forum Discussion

Mergin duplicates from list but adding their values