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
Please do not start a new thread as that would be a duplicate of this thread.
Also if you need a formula that works in non 365 versions of Excel you need to clearly state that at the start.
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Fluff said:
Please do not start a new thread as that would be a duplicate of this thread.
Also if you need a formula that works in non 365 versions of Excel you need to clearly state that at the start.
Click to expand...
Apologies @Fluff I thought (mistakenly) that was the preferred course of action.
 
Upvote 0
Hi @Fluff i just saw this #21 post just now "Please do not start a new thread". Before i check email send by myall in afternoon and so i start a new thread.. so i sorry for that.. i will keep in mind next time to ask for formula that works in non 365 versions also... i also don't want to duplicate or similar posts creation. Now @myall_blues have given us proper solution, so thanks to him.. but you please also guide me where i can get help of this formula to be work in excel 2021.. Thanks
 
Upvote 0
Hi @myall_blues , last night i spend 2 hrs to look alternate hstack which works in excel 2021 but i did not succeed. Who have good knowledge of excel can only help me. Now my all formula is complete but works only on 365 , but i have office 2021. i hope you can solve this issue.. Thanks....

Shares Search Filter.xlsx
ABCDEHIJ
1ParticularDateBuy/SellFrom DateTo DatePrice<=Price<=
2SEARCH
3
40AVG#DIV/0!
5DateParticularQtyBuy/SellPriceBalance Qty
6#NAME? 
7 
8 
9 
10 
Search
Cell Formulas
RangeFormula
C4C4=SUBTOTAL(9,C6:C1000)
E4E4=F4/C4
A6A6=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<>""))
H6:H10H6=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)
 
Upvote 0
Despite previous comments I still suggest a new thread as I have gotten very busy at work and won’t have ability to attempt a solution for another week or so.
 
Upvote 0
myall_blues said:
Despite previous comments I still suggest a new thread as I have gotten very busy at work and won’t have ability to attempt a solution for another week or so.
Click to expand...
Hi sir, thanks for reply. Firstly so much thanks for your efforts. No problem you take your time.. if i have solution before i will tell you otherwise when you have available free time, you can look it.. Regarding new thread , as you know admin fluff will not allow to do this.. I learn so much from you ... so thanks again for your valuable time and for your efforts... :love:
 
Upvote 0
Hi @myall_blues good afternoon... Finally my problem solved and sheet working in excel 2021 also. yesterday i asked to Peter_SSs for any solution and they give me exactly i want. so thanks again for your valuable time and for your efforts... have a good day (y) :love:
 
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