Excel extract unique values from multiple columns and find sum of values using index match

[Sumanmathew]

Dear Sir
Please help me solve this.
I am unable to capture the range through the Mini Sheet in XL2BB.
So I am sending a screenshot of the sheet.

 

[Peter_SSs]

I am unable to capture the range through the Mini Sheet in XL2BB.

Why?

Why aren't sam and annie also in the results?

 

[Sumanmathew]

Why?

Why aren't sam and annie also in the results?

All the unique names from List 1 and List 2 should come in result, eventhough I haven't shown all of them. Again the sum of the values corresponding to each unique name should also come in the result.
It would be great if you could help.

 

[Peter_SSs]

.. and what about the XL2BB sample data so helpers don't have to type it all out manually to test?

 

[Sumanmathew]

.. and what about the XL2BB sample data so helpers don't have to type it all out manually to test?

Yes , I know that it is difficult. But my range selection button is disabled. Now this mesage is shown. I disabled the macros, but still the button is inactive.

 

[Sumanmathew]

Yes , I know that it is difficult. But my range selection button is disabled. Now this mesage is shown. I disabled the macros, but still the button is inactive.

My range selection button is shown as disabled.

 

[Peter_SSs]

Yes , I know that it is difficult. But my range selection button is disabled. Now this mesage is shown. I disabled the macros, but still the button is inactive.

Review the first bullet point in "Known XL2BB issues" near the top of the instructions page.

 

[Sumanmathew]

Review the first bullet point in "Known XL2BB issues" near the top of the instructions page.

I tried everything as mentioned in the instructions page, but still my mini sheet range button is inactive.

Sir, I have made the items too short so that it is easy for you to enter, please can you check it and help me.

I need a formula (index match or something like that), to extract the unique names from these 2 lists and the sum of the values corresponding to those unique names.
please help.

 

[Peter_SSs]

still my mini sheet range button is inactive.

Sounds like you still do not have the Add-In file in a Trusted Location and/or have not added the 'Unblock' tick in the file properties as per post #3 here.

extract the unique names from these 2 lists and the sum of the values

Give this a try.

23 10 16.xlsm
	A	B	C	D	E	F	G	H	I
1
2		list 1	val 1		list 2	val 2		List	val
3		s	10		s	15		s	115
4					a	20		j	130
5		j	30		j	50		a	20
6		s	30
7		j	50		s	50
8		s	10
9
List & Sum

Cell Formulas
Range	Formula
H3:H5	H3	=UNIQUE(FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,B3:B8,E3:E7)&"</c></p>","//c"))
I3:I5	I3	=SUMIF(B3:B8,H3#,C3:C8)+SUMIF(E3:E7,H3#,F3:F7)
Dynamic array formulas.

 

[Sumanmathew]

Sounds like you still do not have the Add-In file in a Trusted Location and/or have not added the 'Unblock' tick in the file properties as per post #3 here.


Give this a try.

23 10 16.xlsm
	A	B	C	D	E	F	G	H	I
1
2		list 1	val 1		list 2	val 2		List	val
3		s	10		s	15		s	115
4					a	20		j	130
5		j	30		j	50		a	20
6		s	30
7		j	50		s	50
8		s	10
9
List & Sum

Cell Formulas
Range	Formula
H3:H5	H3	=UNIQUE(FILTERXML("<p><c>"&TEXTJOIN("</c><c>",1,B3:B8,E3:E7)&"</c></p>","//c"))
I3:I5	I3	=SUMIF(B3:B8,H3#,C3:C8)+SUMIF(E3:E7,H3#,F3:F7)
Dynamic array formulas.

Thankyou so much. Sorry for the inconvenience caused. I will check again and make sure if my file is in a trusted location, in case i missed the tick or something.Thanks a lot. God blesss.