Assistance on Getting Values from raw data

[Sandeep Singh]

Hi All Excel Experts,

Hope all are doing good !!!

I need your assistance on getting values from the raw data pulled from an xxx application. The values can be pulled through a formula or small VBA code.

Below is the raw data format.



A B C D E F G H

I have three scenarios as to how the data should be picked from raw data.
1. I want that amount as per the broker and code number as below. (It should search the entire data and as per the broker number and code number given it should give the amount.

2. I want the aggregate value of CSHADJ which is highlighted in green. It should search the entire data and take the aggregate value of CSHADJ.

3. Its similar to second scenario, want the aggregate value of Offset Rec ACAT fail rev and Offset Dec ACAT fail Rev. It should give the aggregate value if we have multiple same descriptions. And it can be anywhere within the raw data.

Thinking that an excel array formula should work on this and I trying at my end too. Thanks in advance for your support !!!

Regards,
Sandeep

 

[AhoyNC]

What version of Excel are you using?
Will the Amount for the Broker always be 2 rows above in column G from row B with the Broker Desc.?

 

[Sandeep Singh]

Thanks AhoyNc for your revert.

I am using EXCEL 2013.
Yes broker amount will always be in same column.

 

[AhoyNC]

Try the following:
Also, it would help if you could use XL2BB to post your examples (see green square at the end of the ribbon). Most members don't want to have to retype your data to test a solution.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1		O. Desc.					Amount
2							1			scenario 1
3										b1	1
4		b1								b2	2
5							2			b3	3
6
7		b2								scenario 2
8							3			CSHADJ	248950.58
9
10		b3								scenario 3
11		D CSHADJ IS PART0161					128692.15			OFFSET REC ACAT FAIL REV	-673680.24
12		C CSHADJ ID PART0161					-2700.00			OFFSET DEC ACAT FAIL REV	8222.94
13		D CSHADJ 2D PART0161					122958.43
14
15		OFFSET SEC NO OOOOO
16		OFFSET REC ACAT FAIL REV					-24680.24
17		OFFSET DEC ACAT FAIL REV					6062.94
18		OFFSET REC ACAT FAIL REV					-649000.00
19		OFFSET DEC ACAT FAIL REV					2160.00
Sheet1

Cell Formulas
Range	Formula
K3:K5	K3	=INDEX($G$2:$G$10,MATCH(J3,$B$2:$B$10,0)-2)
K8	K8	=SUMIF($B$2:$B$20,"*"&J8&"*",$G$2:$G$20)
K11:K12	K11	=SUMIF($B$2:$B$19,J11,$G$2:$G$19)