What will be formula on Search Box as i type data filter accordingly...

P

pankajgrover

Board Regular
Joined
Oct 27, 2022
Messages
157
Office Version
  1. 2021
Platform
  1. Windows
Here below my excel sheet i have search box cell C2 in source_data sheet , i want if i type sams, then all samsung data appears. so What will be formula on Search Box as i type, data filter accordingly in A4.
here both sheets below Thanks..
source_data :

Shares Search Filter.xlsx
ABCDEF
1
2SearchSAM
3DateParticularQtyBuy/SellPriceTotals
406/08/2024MICROSOFT25BUY135.73392.5
506/08/2024SAMSUNG5BUY643.53217.5
606/08/2024NETFLIX11BUY3347.736824.7
706/08/2024APPLE50BUY28714350
806/08/2024MICROSOFT5SELL49899.9249499.5
906/08/2024SAMSUNG1BUY5187.75187.7
1006/08/2024NETFLIX25BUY135.843396
1106/08/2024APPLE25BUY287.87195
1206/08/2024MICROSOFT11BUY1610.6517717.15
1306/08/2024SAMSUNG4BUY2651.610606.4
1406/08/2024NETFLIX1BUY2650.752650.75
1506/08/2024APPLE1SELL475.5475.5
1605/08/2024MICROSOFT25BUY649.316232.5
1705/08/2024SAMSUNG1BUY476.85476.85
1805/08/2024NETFLIX25BUY800.320007.5
1904/08/2024APPLE5BUY6536.3532681.75
20      
source_data
Cell Formulas
RangeFormula
A4:A20A4=IFERROR(INDEX(INT(FILTER(raw_data!A$2:A$1000,raw_data!D$2:D$1000<>"")),ROW()-ROW(my_data[#Headers])),"")
B4:B20B4=IFERROR(REPLACE(INDEX(FILTER(raw_data!C$2:C$1000,raw_data!D$2:D$1000<>""),ROW()-ROW(my_data[#Headers])),1,4,""),"")
C4:C20C4=IFERROR(INDEX(FILTER(raw_data!D$2:D$1000,raw_data!D$2:D$1000<>""),ROW()-ROW(my_data[#Headers])),"")
D4:D20D4=IFERROR(INDEX(FILTER(raw_data!E$2:E$1000,raw_data!D$2:D$1000<>""),ROW()-ROW(my_data[#Headers])),"")
E4:E20E4=IFERROR(INDEX(FILTER(raw_data!F$2:F$1000,raw_data!D$2:D$1000<>""),ROW()-ROW(my_data[#Headers])),"")
F4:F20F4=IFERROR([@Qty]*[@Price],"")
Named Ranges
NameRefers ToCells
all=raw_data!$A$2:$K$1000A4:A20
Z_9B6B12FD_715E_4E0E_ADF9_6DE861B5043A_.wvu.FilterData=source_data!$A$3:$F$994A4:E20


raw_data for reference:
Shares Search Filter.xlsx
ABCDEFG
1DateUsernameParticularQtyBuy/SellPriceAverage Price
206/08/2024PAN100NSE MICROSOFT25BUY135.7142.048
306/08/2024PAN100NSE SAMSUNG5BUY643.5649.3
406/08/2024PAN100NSE NETFLIX11BUY3347.70
506/08/2024PAN100NSE APPLE50BUY287299.714
606/08/2024PAN100NSE MICROSOFT5SELL49899.950558.02
706/08/2024PAN100NSE SAMSUNG1BUY5187.75287.173
806/08/2024PAN100NSE NETFLIX25BUY135.84142.254
906/08/2024PAN100NSE APPLE25BUY287.8300.418
1006/08/2024PAN100NSE MICROSOFT11BUY1610.650
1106/08/2024PAN100NSE SAMSUNG4BUY2651.62663.875
1206/08/2024PAN100NSE NETFLIX1BUY2650.752666.5
1306/08/2024PAN100NSE APPLE1SELL475.5512.695
1405/08/2024PAN100NSE MICROSOFT25BUY649.30
1505/08/2024PAN100NSE SAMSUNG1BUY476.85512.9
1605/08/2024PAN100NSE NETFLIX25BUY800.3835.402
1704/08/2024PAN100NSE APPLE5BUY6536.356703.889
raw_data
 
Last edited:
Sort by date Sort by votes
Try this. The formula hasn't changed much but I put it inside a LET to make it more readable. The piece that fixes the problem with the time is INT(dates)=D2. Taking the integer of a date/time value just gives the date on its own.
Excel Formula: 
=LET(
dates,raw_data!A2:A1000,
particular,raw_data!C2:C1000,
qty,raw_data!D2:D1000,
buysell,raw_data!E2:E1000,
price,raw_data!F2:F1000,
inputdata,HSTACK(DATEVALUE(TEXT(dates,"dd/mm/yyyy")),REPLACE(particular,1,4,""),qty,buysell,price),
FILTER(inputdata,(NOT(ISERROR(SEARCH(C2,particular))))*(qty<>"")*(INT(dates)=D2)*(buysell=E2)))
 
Upvote 1

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
myall_blues said:
Try this. The formula hasn't changed much but I put it inside a LET to make it more readable. The piece that fixes the problem with the time is INT(dates)=D2. Taking the integer of a date/time value just gives the date on its own.
Excel Formula: 
=LET(
dates,raw_data!A2:A1000,
particular,raw_data!C2:C1000,
qty,raw_data!D2:D1000,
buysell,raw_data!E2:E1000,
price,raw_data!F2:F1000,
inputdata,HSTACK(DATEVALUE(TEXT(dates,"dd/mm/yyyy")),REPLACE(particular,1,4,""),qty,buysell,price),
FILTER(inputdata,(NOT(ISERROR(SEARCH(C2,particular))))*(qty<>"")*(INT(dates)=D2)*(buysell=E2)))
Click to expand...
Yes problem solved.. INT(dates)=D2, this formula is much easier then as compare to DATEVALUE(TEXT(dates,"dd/mm/yyyy").. thanks again :love:
 
Upvote 0
@myall_blues Hi, i add also search formula in D2 and E2, [&(SEARCH(D2,(INT(raw_data!A2:A1000))&(SEARCH(E2,raw_data!E2:E1000))))] so that when no data input in search boxes , data below displayed fully and will not occur any #calc! error. I lot a learn from you.. Thanks. This formula now i am using :
Excel Formula: 
=FILTER(HSTACK((INT(raw_data!A2:A1000)),REPLACE(raw_data!C2:C1000,1,4,""),raw_data!D2:F1000),(NOT(ISERROR(SEARCH(C2,raw_data!C2:C1000)&(SEARCH(D2,(INT(raw_data!A2:A1000))&(SEARCH(E2,raw_data!E2:E1000))))*(raw_data!D2:D1000<>"")))))
 
Upvote 0
Hi @myall_blues , as i am using my excel sheet , i need to create 2 more search columns E2 and H2 , means i want search between two dates my shares price.. here i created below sheet but not work between 2 days. As i put date E2 and H2 , Cacl# error comes...kindly correct my wrong formula.. Thanks

Shares Search Filter NEW.xlsx
ABCDEHI
1ParticularDateBuy/SellFrom DateTo Date
2SEARCHAPPBUY
3
481AVG675.35
5DateParticularQtyBuy/SellPrice
606/08/2024APPLE50BUY287.00
706/08/2024APPLE25BUY287.80
805/08/2024APPLE1BUY476.85
904/08/2024APPLE5BUY6536.35
10
11
12
13
14
15
Search
Cell Formulas
RangeFormula
C4C4=SUBTOTAL(9,C6:C1000)
E4E4=F4/C4
A6:E9A6=FILTER(HSTACK((INT(Data!A2:A1000)),REPLACE(Data!C2:C1000,1,4,""),Data!D2:F1000),(NOT(ISERROR(SEARCH(B2,Data!C2:C1000)&(SEARCH(C2,(INT(Data!A2:A1000))&(SEARCH(D2,Data!E2:E1000))*(SEARCH(E2,(INT(Data!A2:A1000>=E2))&(SEARCH(H2,(INT(Data!A2:A1000<=H2))))*(Data!D2:D1000<>"")))))))))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
all=Data!$A$2:$J$1000A6
 
Upvote 0
Excel Formula: 
=LET(
dates,raw_data!A2:A1000,
datefrom,IF(E2="",1/1/1900,E2),
dateto, IF(H2="",TODAY(),H2),
particular,raw_data!C2:C1000,
qty,raw_data!D2:D1000,
buysell,raw_data!E2:E1000,
price,raw_data!F2:F1000,
inputdata,HSTACK(DATEVALUE(TEXT(dates,"dd/mm/yyyy")),REPLACE(particular,1,4,""),qty,buysell,price),
FILTER(inputdata,(NOT(ISERROR(SEARCH(B2,particular))))*(qty<>"")*(INT(dates)>=datefrom)*(INT(dates)<=dateto)*(buysell=D2)))
 
Upvote 1
Solution
myall_blues said:
Excel Formula: 
=LET(
dates,raw_data!A2:A1000,
datefrom,IF(E2="",1/1/1900,E2),
dateto, IF(H2="",TODAY(),H2),
particular,raw_data!C2:C1000,
qty,raw_data!D2:D1000,
buysell,raw_data!E2:E1000,
price,raw_data!F2:F1000,
inputdata,HSTACK(DATEVALUE(TEXT(dates,"dd/mm/yyyy")),REPLACE(particular,1,4,""),qty,buysell,price),
FILTER(inputdata,(NOT(ISERROR(SEARCH(B2,particular))))*(qty<>"")*(INT(dates)>=datefrom)*(INT(dates)<=dateto)*(buysell=D2)))
Click to expand...
yes working now between dates... Thanks.. this below idea was brilliant,
datefrom,IF(E2="",1/1/1900,E2),
dateto, IF(H2="",TODAY(),H2),

because exactly i want, if my search boxes like B2, C2, D2, E2, and H2 cells are blank then below onwards A6, my full all data should be displayed, if i write something on above cells data filter accordingly... and that's exactly working.. Thanks

Also I have another pc which have excel 2021, this workbook not working there Name# error appears, may be HStack not supported ? hv any alternate command which supports this workbook in excel 2021 also.. Thanks

With search Boxes Blank:
Shares Search Filter NEW.xlsx
ABCDEH
1ParticularDateBuy/SellFrom DateTo Date
2SEARCH
3
4221AVG1920.31
5DateParticularQtyBuy/SellPrice
606/08/2024MICROSOFT25BUY135.70
706/08/2024SAMSUNG5BUY643.50
806/08/2024NETFLIX11BUY3347.70
906/08/2024APPLE50BUY287.00
1006/08/2024MICROSOFT5SELL49899.90
1106/08/2024SAMSUNG1BUY5187.70
1206/08/2024NETFLIX25BUY135.84
1306/08/2024APPLE25BUY287.80
1406/08/2024MICROSOFT11BUY1610.65
1506/08/2024SAMSUNG4BUY2651.60
1606/08/2024NETFLIX1BUY2650.75
1706/08/2024APPLE1SELL475.50
1805/08/2024MICROSOFT25BUY649.30
1905/08/2024SAMSUNG1BUY476.85
2005/08/2024NETFLIX25BUY800.30
2104/08/2024APPLE5BUY6536.35
2204/08/2024SAMSUNG1BUY476.85
Search
Cell Formulas
RangeFormula
C4C4=SUBTOTAL(9,C6:C1000)
E4E4=F4/C4
A6:E22A6=FILTER(HSTACK((INT(Data!A2:A1000)),REPLACE(Data!C2:C1000,1,4,""),Data!D2:F1000),(NOT(ISERROR(SEARCH(B2,Data!C2:C1000)&(SEARCH(D2,Data!E2:E1000)))))*(INT(Data!A2:A1000)>=IF(E2="",1/1/1900,E2))*(INT(Data!A2:A1000)<=IF(H2="",TODAY(),H2))*(Data!D2:D1000<>""))
Dynamic array formulas.


With Search Boxes Fill:

Shares Search Filter NEW.xlsx
ABCDEH
1ParticularDateBuy/SellFrom DateTo Date
2SEARCHSAMBUY04/08/202406/08/2024
3
412AVG1663.78
5DateParticularQtyBuy/SellPrice
606/08/2024SAMSUNG5BUY643.50
706/08/2024SAMSUNG1BUY5187.70
806/08/2024SAMSUNG4BUY2651.60
905/08/2024SAMSUNG1BUY476.85
1004/08/2024SAMSUNG1BUY476.85
Search
Cell Formulas
RangeFormula
C4C4=SUBTOTAL(9,C6:C1000)
E4E4=F4/C4
A6:E10A6=FILTER(HSTACK((INT(Data!A2:A1000)),REPLACE(Data!C2:C1000,1,4,""),Data!D2:F1000),(NOT(ISERROR(SEARCH(B2,Data!C2:C1000)&(SEARCH(D2,Data!E2:E1000)))))*(INT(Data!A2:A1000)>=IF(E2="",1/1/1900,E2))*(INT(Data!A2:A1000)<=IF(H2="",TODAY(),H2))*(Data!D2:D1000<>""))
Dynamic array formulas.
 
Upvote 0
Hi @myall_blues , trading going good and filter doing properly. As we use this workbook new new requirements comes. Today i need of filter 2 col. more I2 and K2,
like Price Less than and Price more than.. For example i buy Samsung stock suppose 5 qty @100, 7qty @300 so if current price is 200 we can check if price less than 200 we can sale those quantity if i enter in price box >=200.. how to modify this formula for this purpose.. Thanks .... currently i am using this formula below:

Excel Formula: 
=FILTER(HSTACK((INT(Data!A2:A1000)),REPLACE(Data!C2:C1000,1,4,""),Data!D2:F1000),(NOT(ISERROR(SEARCH(B2,Data!C2:C1000)&(SEARCH(D2,Data!E2:E1000)))))*(INT(Data!A2:A1000)>=IF(E2="",1/1/1900,E2))*(INT(Data!A2:A1000)<=IF(H2="",TODAY(),H2))*(Data!D2:D1000<>""))
 
Upvote 0
Hi @myall_blues after i trying myself to add 2 col. more I2 and K2 and finally i succeeded to learn and look from your formula and be able to modify this , please check may be i not wrong in my below formula ..
Also please tell me how this magic happen , you set command in E2, if E2 is blank then 1/1/1900 but you can see my cell blank and still this 1/1/1900 not visible, i exactly want this , but how this happening.. how below cells are going invisible ?
datefrom,IF(E2="",1/1/1900,E2),
dateto, IF(H2="",TODAY(),H2),

Now everything is working just like i want , if you can modify below formula that will work also in 2021 excel, please guide me ..
Thankyou so much for giving your valuable time.. :love:

Excel Formula: 
=FILTER(HSTACK((INT(Data!A2:A1000)),REPLACE(Data!C2:C1000,1,4,""),Data!D2:F1000),(NOT(ISERROR(SEARCH(B2,Data!C2:C1000)&(SEARCH(C2,(INT(Data!A2:A1000)))&(SEARCH(D2,Data!E2:E1000))))))*(INT(Data!A2:A1000)>=IF(E2="",1/1/1900,E2))*(INT(Data!A2:A1000)<=IF(H2="",TODAY(),H2))*((Data!F2:F1000)<=IF(I2="","",I2))*((Data!F2:F1000)>=IF(J2="",1,J2))*(Data!D2:D1000<>""))

Shares Search Filter.xlsx
ABCDEHIJ
1ParticularDateBuy/SellFrom DateTo DatePrice<=Price<=
2SEARCH
3
4220AVG1926.87
5DateParticularQtyBuy/SellPriceBalance Qty
606-08-2024MICROSOFT25BUY135.7056
706-08-2024SAMSUNG5BUY643.5011
806-08-2024NETFLIX11BUY3347.7062
906-08-2024APPLE50BUY287.0079
1006-08-2024MICROSOFT5SELL49899.9056
1106-08-2024SAMSUNG1BUY5187.7011
1206-08-2024NETFLIX25BUY135.8462
1306-08-2024APPLE25BUY287.8079
1406-08-2024MICROSOFT11BUY1610.6556
1506-08-2024SAMSUNG4BUY2651.6011
1606-08-2024NETFLIX1BUY2650.7562
1706-08-2024APPLE1SELL475.5079
1805-08-2024MICROSOFT25BUY649.3056
1905-08-2024SAMSUNG1BUY476.8511
2005-08-2024NETFLIX25BUY800.3062
2104-08-2024APPLE5BUY6536.3579
Search
Cell Formulas
RangeFormula
C4C4=SUBTOTAL(9,C6:C1000)
E4E4=F4/C4
A6:E21A6=FILTER(HSTACK((INT(Data!A2:A999)),REPLACE(Data!C2:C999,1,4,""),Data!D2:F999),(NOT(ISERROR(SEARCH(B2,Data!C2:C999)&(SEARCH(C2,(INT(Data!A2:A999)))&(SEARCH(D2,Data!E2:E999))))))*(INT(Data!A2:A999)>=IF(E2="",1/1/1900,E2))*(INT(Data!A2:A999)<=IF(H2="",TODAY(),H2))*((Data!F2:F999)<=IF(I2="","",I2))*((Data!F2:F999)>=IF(J2="",1,J2))*(Data!D2:D999<>""))
H6:H21H6=SUMIFS(Search!$C$6:$C$1000,Search!$B$6:$B$1000,B6,Search!$D$6:$D$1000,"BUY",Search!$G$6:$G$1000,1) - SUMIFS(Search!$C$6:$C$1000,Search!$B$6:$B$1000,B6,Search!$D$6:$D$1000,"SELL",Search!$G$6:$G$1000,1)
Dynamic array formulas.
 
Upvote 0
Hi @myall_blues morning, i need help, this formula not working on excel 2021. Only working in office 365. Hstack not working in 2021, any alternate so can work on 2021 also.. thanks...
 
Upvote 0
Hi - sorry.

Generally you're better to start a new thread as most of us here stop watching once a question is marked as answered. I suggest you do that anyway as it will be complex (but not impossible) without the use of HSTACK.
Just mention you already have an answer for 365 (and provide it) and you are looking for a 2021 alternative.

Good luck
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Copy column values from another sheet but want to exclude some specific row name completely..
2 3
Replies
23
Views
516
Peter_SSs
Peter_SSs
P
  • Locked
  • Question
Below formula which works on excel 365 only, but also need to be work on excel 2021...
Replies
1
Views
120
Fluff
Fluff
P
  • Solved
SUMIFS function not filter values as i filter data from dropdown arrow
2 3
Replies
28
Views
539
pankajgrover
P
P
  • Solved
Not able to correct filter data date wise, Kindly correct my formula...
Replies
1
Views
141
Anthony47
A
P
  • Question
How to obtain average values on columns filter
Replies
3
Views
179
StephenCrump
S

Forum statistics

Threads
1,221,310
Messages
6,159,176
Members
451,543
Latest member
cesymcox

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top