Extracting and arranging data

At sheet Data
Insert Row 1 for the header row
Name, Type, Code, Amount at E1:H1

E2
=LEFT(B2,FIND(F2,B2)-2)

F2
=TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,")",),"(",),"[",),"]",)," ",REPT(" ",9)),9))

then Insert Pivot table, just drag and drop
Name to Rows
Code , Type to Columns
Amount to Values


Or Formula at Expected Result C3:H7

=SUMIFS(Data!$H$2:$H$16,Data!$B$2:$B$16,$B3&"*",Data!$F$2:$F$16,"*"&C$2&"*",Data!$G$2:$G$16,LOOKUP("z",$C$1:C$1))

In C3 then copied across

Please Login or Register  to view this content.

IF (and it is a very big IF)... the formula in BLUE (C3, copied down) works for you, then this is a helper-free 3-formula solution that delivers everything. Also, IF it works, the formula in BLUE will be an array formula.

IF it works (change one of the names in raw data B1 to B15 to check), then I will need to do a little more tidying up to make it a bit more dynamic... but I am not confident that it will work in your Excel version. For me, it looks good.

Thanx for the response.

As per your post#4 attachment ,plz refer B15 data which is in braces(Gratuity) which is also to be pulled in row 2 of 'Expected Result' worksheet.This means that everything which is either in () or[ ] must be reflected in row 2 which has been manually mentioned.Rest seems to be fine.

Plz note that this is only sample data,in actual I have data at least 4200.
Plz do necessary tidying up to make up more dynamic in large data for 4200 rows.

Suppose every name set of 3 contiguous rows
Salary = Salary
GT=Gt or gt or Gratuity
PF=Pf or pf or pF
In C3:

Please Login or Register  to view this content.

Drag down and accross

Have you seen post #3

1. 3 Named ranges for Primary data, Names, Codes & amounts, all variants of this:

=Data!$B$1:INDEX(Data!$B:$B,MATCH("zzzz",Data!$B:$B))

These will auto-adjust the ranges to suit your data.


2. 1 Named Range to return the name portions from the name/payment composite:

=IFERROR(IFERROR(LEFT(Names,SEARCH("[",Names)-1),LEFT(Names,SEARCH("(",Names)-1)),"")



3. Array formula to return names, C2, copied down:

=IFERROR(INDEX(List,MATCH(1,INDEX(--ISNA(MATCH(List,C$2:C2,)),),)),"")


4. Formula to return codes in D1, copied across:

=IFERROR(INDEX(Codes,MATCH(1,INDEX(--ISNA(MATCH(Codes,$A$1:A1,)),),)),"")



5. Formula in D3 to do the maths, copied across and down:

=IF($C3="","",SUMPRODUCT(--(ISNUMBER(SEARCH($C3,Names))*--(ISNUMBER(SEARCH(D$2,Names))*(Codes=D$1)*Amounts))))


Refer to the file.

Helper column C used. In C1 then copy down

Please Login or Register  to view this content.

In B3 then copy down

Please Login or Register  to view this content.

Here in B3 and copy down for sorted list in

Please Login or Register  to view this content.

Pl see file.
Helper columns C and D are used. It can changed and suitably formulas should be edited.
Names are sorted.