find data in a row based on column value

[ajith]

[TABLE="class: grid, width: 500, align: right"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]name[/TD]
[TD]age[/TD]
[TD]complaint[/TD]
[TD]response[/TD]
[TD]days[/TD]
[TD]drug[/TD]
[TD]symp[/TD]
[TD]repeat[/TD]
[TD]R.resp[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD="align: center"]Shyam[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"]pain[/TD]
[TD="align: center"]good[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]ss[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]good[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: center"]Sunil[/TD]
[TD="align: center"]24[/TD]
[TD="align: center"]fever[/TD]
[TD="align: center"]good[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]x[/TD]
[TD="align: center"]aa[/TD]
[TD="align: center"]n[/TD]
[TD="align: center"]bad[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: center"]Aji[/TD]
[TD="align: center"]46[/TD]
[TD="align: center"]colic[/TD]
[TD="align: center"]good[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]f[/TD]
[TD="align: center"]ss[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]bad[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: center"]Tom[/TD]
[TD="align: center"]34[/TD]
[TD="align: center"]fever[/TD]
[TD="align: center"]bad[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]g[/TD]
[TD="align: center"]dd[/TD]
[TD="align: center"]y[/TD]
[TD="align: center"]bad[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: center"]Mathew[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]fever[/TD]
[TD="align: center"]good[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]c[/TD]
[TD="align: center"]ab[/TD]
[TD="align: center"]n[/TD]
[TD="align: center"]good[/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: center"]Sucy[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]pain[/TD]
[TD="align: center"]bad[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]ss[/TD]
[TD="align: center"]n[/TD]
[TD="align: center"]bad[/TD]
[/TR]
</tbody>[/TABLE]
sir,
Please suggest an excel formula to extract the data in the entire row of those which have "fever" in column F. ie we should paste the data in row no. 13, 15 and 16 in another table in Sheet2 having the same headings. Thank you

 

[ImranMalek]

Hi ,

Please try to apply advance filter on data like

https://ibb.co/kkzAyk

 

[Aladin Akyurek]

Sheet2 (the processing sheet)

A1: fever

In A2 just enter:

=COUNTIFS(Sheet1!F12:F17,A1)

A3: Idx, B3:J3 replicates the headers in D11:L11 of Sheet1.

In A4 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$4:A4)>$A$3,"",SMALL(IF(Sheet1!$F$12:$F$17=$A$1,ROW(Sheet1!$F$12:$F$17)-ROW(Sheet1!$F$12)+1),ROWS($A$4:A4)))

In B4 just enter, copy across, and down:

=IF$A4="","",INDEX(Sheet1!$A$12:$L$17,$A4,MATCH(B$3,Sheet1!$A$11:$L$11,0)))

 

[ajith]

Thanks Aladin. But what is A3: Idx
I did as what you have given but none of the cells are showing any value. All are empty except A2 where it is 3.

 

[Aladin Akyurek]

Thanks Aladin. But what is A3: Idx
I did as what you have given but none of the cells are showing any value. All are empty except A2 where it is 3.

A3 houses the label Idx; B3:J3 houses copies of the headers in D11:L11 of Sheet1.

The formula that goes in A4 must be confirmed with control+shift+enter (not just with enter) and copied down:

=IF(ROWS($A$4:A4)>$A$2,"",SMALL(IF(Sheet1!$F$12:$F$17=$A$1,ROW(Sheet1!$F$12:$F$17)-ROW(Sheet1!$F$12)+1),ROWS($A$4:A4)))

The formula that goes in B4 must be confirmed with just enter, copied across, and down:

=IF$A4="","",INDEX(Sheet1!$A$12:$L$17,$A4,MATCH(B$3,Sheet1!$A$11:$L$11,0)))

​

 

[ajith]

Sir,
I changed it to $A$2. Now I am getting 3 in A4 and from B3 to J3 #N/A. All cells in the table are empty.

 

[Aladin Akyurek]

Sir,
I changed it to $A$2. Now I am getting 3 in A4 and from B3 to J3 #N/A. All cells in the table are empty.

Did you also edit the IF bit of the B4 formula?...

=IF($A4="","",INDEX(Sheet1!$A$12:$L$17,$A4,MATCH(B$3,Sheet1!$A$11:$L$11,0)))

​

 

[ajith]

Yes I did it

 

[Aladin Akyurek]

Yes I did it

Control+shift+enter means: Press down the shift and the control keys at the same time while you hit the enter key. If done successfully, Excel itself puts a pair of { and } around the formula in recognition.

As you know, the formula in A4 must be confirmed control+shift+enter, then copied down.

 

[ajith]

Thank you sir,
I got it. I made a mistake in row number. Ok It is perfect. Thank you