[Solved] Find the All balance Value of 'Ali' and display it in the 'Result' column?

[Ammar]

How to use Excel formula to find the All balance Value of 'Ali' and display it in the 'Result' column?

RSO	Balance	Number	Root
Umair Ahmed	1000​	345323208​	City		Result
Ali	3000​	345323209​	Out		3000​
zad	1500​	345323210​	Sanghi		900​
sami	1300​	345323211​	Ghitki		7500​
bilal	1400​	345323212​	SHAR		7000​
Ali	900​	345323213​	Bazar		30030​
Mian	850​	345323214​	Ghith
Ali	7500​	345323215​	Village
sami	32000​	345323216​	Dalo
Ali	30030​	345323217​	Alo
Amm Ali	6000​	345323218​	Banl
Ali	7000​	345324523​	Pnl

 

[Seemanto Saha]

Dear Ammar,

Thanks for reaching us. I understand that you want to use an Excel formula to find the all balance values of "Ali" and display it in the "Result" column. There are two ideal solutions for this problem. Here, I have taken the filter value “Ali” in Cell E2 to accommodate my calculations.

Solution:1
We can use the Excel FILTER Function to extract all the balance values of “Ali”. For your given dataset, we can apply the following formula in Cell F2, using the FILTER Function.

=FILTER(B2:B13,A2:A13=E2)

​

However, the FILTER function is only available in Office 365. So, if you haven’t installed Office 365 yet, you can use the following alternative solution.

Solution:2
Another way to extract all balance values of “Ali” is to use a combination of TEXTSPLIT, TEXTJOIN, and IF functions. For your provided dataset, we can use the following formula:

=TEXTSPLIT(TEXTJOIN(",",TRUE,IF(A2:A13=E2,B2:B13,"")),,",")

​

I hope these solutions will be sufficient for your requirements. Let us know your feedback.

Regards,
Seemanto Saha
ExcelDemy