Solve the Query with Excel only not VBA

[narendra_1]

Respected seniors,

I have a query which needs to be resolve with Excel Formula only not VBA.

https://www.dropbox.com/s/tg4i0uob05n8ls5/Query for Excel.xlsx?dl=0


Kindly help as soon as possible.

Regards

Narendra

 

[Worf]

Hello

Plan2

	B	C	D	E	F	G	H	I	J

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:36px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]66[/TD]
[TD="align: center"]22[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]77[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]87[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]11[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]56[/TD]
[TD="align: center"]87[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: center"]88[/TD]
[TD="align: center"]77[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]55[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]63[/TD]
[TD="align: center"]88[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: center"]63[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]99[/TD]
[TD="align: center"]56[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]66[/TD]
[TD="align: center"]99[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]
[TD="align: center"]6[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]
[TD="align: center"]8[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]
[TD="align: center"]11[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]15[/TD]
[TD="align: center"]22[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]
[TD="align: center"]55[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]
[TD="align: center"]56[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]18[/TD]
[TD="align: center"]63[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]19[/TD]
[TD="align: center"]66[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]20[/TD]
[TD="align: center"]77[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]21[/TD]
[TD="align: center"]87[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]22[/TD]
[TD="align: center"]88[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]23[/TD]
[TD="align: center"]99[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula G2 {=B8:B11} H2 {=B12:B15} I2 {=B16:B19} J2 {=B20:B23} G3 {=B8:B11} H3 {=B12:B15} I3 {=B16:B19} J3 {=B20:B23} G4 {=B8:B11} H4 {=B12:B15} I4 {=B16:B19} J4 {=B20:B23} G5 {=B8:B11} H5 {=B12:B15} I5 {=B16:B19} J5 {=B20:B23} B8 {=INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B8))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), MATCH(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B8)), COUNTIF($B$2:$E$5, "<"&INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B8))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), , 1)), 0), 1)} B9 {=INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B9))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), MATCH(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B9)), COUNTIF($B$2:$E$5, "<"&INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B9))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), , 1)), 0), 1)} B10 {=INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B10))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), MATCH(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B10)), COUNTIF($B$2:$E$5, "<"&INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B10))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), , 1)), 0), 1)} B11 {=INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B11))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), MATCH(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B11)), COUNTIF($B$2:$E$5, "<"&INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B11))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), , 1)), 0), 1)} B12 {=INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B12))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), MATCH(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B12)), COUNTIF($B$2:$E$5, "<"&INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B12))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), , 1)), 0), 1)} B13 {=INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B13))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), MATCH(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B13)), COUNTIF($B$2:$E$5, "<"&INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B13))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), , 1)), 0), 1)} B14 {=INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B14))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), MATCH(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B14)), COUNTIF($B$2:$E$5, "<"&INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B14))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), , 1)), 0), 1)} B15 {=INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B15))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), MATCH(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B15)), COUNTIF($B$2:$E$5, "<"&INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B15))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), , 1)), 0), 1)} B16 {=INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B16))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), MATCH(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B16)), COUNTIF($B$2:$E$5, "<"&INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B16))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), , 1)), 0), 1)} B17 {=INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B17))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), MATCH(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B17)), COUNTIF($B$2:$E$5, "<"&INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B17))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), , 1)), 0), 1)} B18 {=INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B18))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), MATCH(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B18)), COUNTIF($B$2:$E$5, "<"&INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B18))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), , 1)), 0), 1)} B19 {=INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B19))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), MATCH(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B19)), COUNTIF($B$2:$E$5, "<"&INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B19))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), , 1)), 0), 1)} B20 {=INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B20))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), MATCH(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B20)), COUNTIF($B$2:$E$5, "<"&INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B20))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), , 1)), 0), 1)} B21 {=INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B21))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), MATCH(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B21)), COUNTIF($B$2:$E$5, "<"&INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B21))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), , 1)), 0), 1)} B22 {=INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B22))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), MATCH(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B22)), COUNTIF($B$2:$E$5, "<"&INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B22))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), , 1)), 0), 1)} B23 {=INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B23))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), MATCH(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B23)), COUNTIF($B$2:$E$5, "<"&INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROWS($B$8:B23))=COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), ROW($B$2:$E$5)-MIN(ROW($B$2:$E$5))+1)), , 1)), 0), 1)} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4