Posts by bosco_yip

Try the formula solution of which the criteria were not in the same order of the source table.

In B2, formula copied down :

=COUNTIF(OFFSET(INDEX(Sheet2!A:A,MATCH(A2,Sheet2!A:A,0)),,,,1000),">0")

Try,

Select A2:A5 >> Conditional Formatting >> New rule >> Use a formula ......>>

>> The rule formula enter :

=OR(0+LEFT(A2,3)=MAX(IF(LEFT(A$2:A$5,3)<=LEFT(D$2,3),0+LEFT(A$2:A$5,3))),0+LEFT(A2,3)=MIN(IF(LEFT(A$2:A$5,3)>=LEFT(D$2,3),0+LEFT(A$2:A$5,3))))

>> OK

1] In B1 enter : 101

2] In B2, formula copied down :

=IF(MOD(ROW(A2),51)=0,"DT",MAX(B$1:B1)+1)

In C20, formula copied down :

=COUNTIFS($2:$2,"Saturday",4:4,"*")

Try

In E11, formula copied down :

=IF(C11="","",IFERROR(INDEX(F$2:F$4,LOOKUP(9,MATCH(B$1:B11,E$2:E$4,0)))*(C11-INDEX(I$2:I$4,LOOKUP(9,MATCH(B$1:B11,E$2:E$4,0)))),""))

This part : MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,32)

will return the current sheet name.

In each Sheet of ,"IBM", "Accenture"…... C2, formula copied down :

=IF(SUMIFS(Worksheet!$C:$C,Worksheet!$A:$A,MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,32),Worksheet!$B:$B,[@Name])=[@Value],[@Name],"N/A")

Try,

In "Worksheet" C2, formula copied down :

=VLOOKUP([@[Name (provided)]],INDIRECT("'"&[@[Account (provided)]]&"'!A:B"),2,0)

Quote from joePAIN

Thanks for the reply - that works nicely. One question, is there a limit for the range of data this can work on? I've got over 5000 lines of data, I tried it on a small selection and it worked perfectly but failed on the larger range.

Cheers

Joe

This is the limitation of formula solution, for large range of data you should switch to VBA solution or Power Query.

Try this formula solution for Excel 2019 and above.

1] Create 2 define names as per attachment

2] In "Intermate Table" H3, formula copied across down :

=IFERROR(IF(LEFT(H$2)="P",TableRgn/IndexRgn,TableRgn),"")

3] In "Output Table " B9, formula copies across and down :

=SUMIFS(I:I,$H:$H,$A9)

4] In "Output Table " B12, formula copies across :

=SUM(B9:B11)

1] In Sheet "A" C6, formula copied right to D6 and all copied down :

=INDEX(B!F:F,LOOKUP(1,0/FIND($A6,B!$A$6:$A$11),ROW(B!$A$6:$A$11)))

2] In Sheet "B" C6, formula copied down :

=TEXTJOIN(", ",,IFERROR(LOOKUP(0+MID(A6,{1,3},1),A!$A:$B),""))

In L2, formula copied down :

=LEFT(A5,FIND(" ",A5)+2)

Give few lines of example data and the expected result

In H2, formula copied down :

=IF(A2="","",COUNT(G2:G$37)-SUM(H3:H$37))

Maybe,

In D1, formula copied down :

=IFERROR(INDEX(A$1:A$7,MOD(ROW(A1)-1,7)+1)&"-"&INDEX(B$1:B$3,INT((ROW(A1)-1)/7)+1),"")

Quote from PrasadK

Thank you it's working perfectly

and one more help if cell have 3 or 4 mobile number how to extract with this formula

One post one question.

Please open a new post for your new question, together with few lines of example data and the expected result.

In G2, enter formula :

=SUMPRODUCT((E2:E17=F2)/COUNTIFS(E2:E17,E2:E17,A2:A17,A2:A17,C2:C17,C2:C17))

B2, copied across right and all copied down :

=MID($A2,FIND(B$1,$A2)+LEN(B$1)+2,10)

Then,

Change B1 formula to :

=0+SUBSTITUTE(SUBSTITUTE(LEFT(SUBSTITUTE(MID(A1,FIND("EPS",A1)+4,99)," ",REPT(" ",50)),50),"$",""),"c","%")

and copied down

Try,

In B1, formula copied down :

=0+SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("EPS",A1)+4,FIND("vs",A1)-FIND("EPS",A1)-5),"$",""),"c","%")