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:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
Try this formula in A4 of source_ data:
Excel Formula: 
=FILTER(HSTACK(raw_data!A2:A1000,REPLACE(raw_data!C2:C1000,1,4,""),raw_data!D2:F1000),NOT(ISERROR(SEARCH(C2,raw_data!C2:C1000))))
 
Upvote 0
myall_blues said:
Try this formula in A4 of source_ data:
Excel Formula: 
=FILTER(HSTACK(raw_data!A2:A1000,REPLACE(raw_data!C2:C1000,1,4,""),raw_data!D2:F1000),NOT(ISERROR(SEARCH(C2,raw_data!C2:C1000))))
Click to expand...
Hi sir, working good.. but before apply this i have to convert table to range, otherwise Spill# error was occurring. Secondly i forgot to mention in my raw data as you seen below in Qty Col. D2:D17 have some blank cells, which i want to delete complete row also .. that's why in previous example i was using =IFERROR(INDEX(INT(FILTER(raw_data!A$2:A$1000,raw_data!D$2:D$1000<>"")),ROW()-ROW(my_data[#Headers])),"") this for eliminate . So in this your new formula search is working now, but how to add this eliminate row function formula also.. Thanks for reply..

Shares Search Filter TEST.xlsx
ABCDEFG
1DateUsernameParticularQtyBuy/SellPriceAverage Price
206/08/2024NSE MICROSOFT25BUY135.7142.048
306/08/2024NSE SAMSUNG5BUY643.5649.3
406/08/2024NSE NETFLIX11BUY3347.70
506/08/2024NSE APPLE50BUY287299.714
606/08/2024NSE MICROSOFT5SELL49899.950558.022
706/08/2024NSE SAMSUNG1BUY5187.75287.173
806/08/2024Brokerage Amount : 356.781
906/08/2024NSE APPLE25BUY287.8300.418
1006/08/2024NSE MICROSOFT11BUY1610.650
1106/08/2024NSE SAMSUNG4BUY2651.62663.875
1205/08/2024Brokerage Amount : 25
1305/08/2024NSE APPLE1SELL475.5512.695
1405/08/2024NSE MICROSOFT25BUY649.30
1505/08/2024NSE SAMSUNG1BUY476.85512.9
1604/08/2024NSE NETFLIX25BUY800.3835.402
1704/08/2024Opening Balance
raw_data
 
Last edited:
Upvote 0
This should fix it.
Excel Formula: 
=FILTER(HSTACK(raw_data!A2:A1000,REPLACE(raw_data!C2:C1000,1,4,""),raw_data!D2:F1000),(NOT(ISERROR(SEARCH(C2,raw_data!C2:C1000))))*(raw_data!D2:D1000<>""))
 
Upvote 1
myall_blues said:
This should fix it.
Excel Formula: 
=FILTER(HSTACK(raw_data!A2:A1000,REPLACE(raw_data!C2:C1000,1,4,""),raw_data!D2:F1000),(NOT(ISERROR(SEARCH(C2,raw_data!C2:C1000))))*(raw_data!D2:D1000<>""))
Click to expand...
it,s a WoW.. working exactly what i want... Thank you soooo much :love:(y)
 
Upvote 0
myall_blues said:
You're welcome - glad we could help.
Click to expand...
As i working , can i get also results based on 3 search boxes , already search C2 is working , can we work on D2 and E2, This will work like filter.. how to do that in your existing formulas.. is this possible, is this will work with multiple search option ?
here below sheet..

Shares Search Filter TEST - Copy.xlsx
ABCDEF
1ParticularDateBuy/Sell
2SearchSAM05/08/2024BUY
3
4DateParticularQtyBuy/SellPriceTotals
506/08/2024SAMSUNG5BUY643.503217.50
606/08/2024SAMSUNG1BUY5187.705187.70
706/08/2024SAMSUNG4BUY2651.6010606.40
805/08/2024SAMSUNG1BUY476.85476.85
source_data
Cell Formulas
RangeFormula
A5:E8A5=FILTER(HSTACK(DATEVALUE(TEXT(raw_data!A2:A1000,"dd/mm/yyyy")),REPLACE(raw_data!C2:C1000,1,4,""),raw_data!D2:F1000),(NOT(ISERROR(SEARCH(C2,raw_data!C2:C1000))))*(raw_data!D2:D1000<>""))
F5:F8F5=C5*E5
Dynamic array formulas.
Named Ranges
NameRefers ToCells
all=raw_data!$A$2:$K$1000A5
 
Last edited:
Upvote 0
Yes it will work - you can just add more conditions (by adding multiplications) to the filter.

Add the date:
Excel Formula: 
=FILTER(HSTACK(DATEVALUE(TEXT(raw_data!A2:A1000,"dd/mm/yyyy")),REPLACE(raw_data!C2:C1000,1,4,""),raw_data!D2:F1000),(NOT(ISERROR(SEARCH(C2,raw_data!C2:C1000))))*(raw_data!D2:D1000<>"")*(raw_data!A2:A1000=D2))
Add buy/sell
Excel Formula: 
=FILTER(HSTACK(DATEVALUE(TEXT(raw_data!A2:A1000,"dd/mm/yyyy")),REPLACE(raw_data!C2:C1000,1,4,""),raw_data!D2:F1000),(NOT(ISERROR(SEARCH(C2,raw_data!C2:C1000))))*(raw_data!D2:D1000<>"")*(raw_data!A2:A1000=D2)*(raw_data!E2:E1000=E2))
 
Upvote 1
myall_blues said:
Yes it will work - you can just add more conditions (by adding multiplications) to the filter.

Add the date:
Excel Formula: 
=FILTER(HSTACK(DATEVALUE(TEXT(raw_data!A2:A1000,"dd/mm/yyyy")),REPLACE(raw_data!C2:C1000,1,4,""),raw_data!D2:F1000),(NOT(ISERROR(SEARCH(C2,raw_data!C2:C1000))))*(raw_data!D2:D1000<>"")*(raw_data!A2:A1000=D2))
Add buy/sell
Excel Formula: 
=FILTER(HSTACK(DATEVALUE(TEXT(raw_data!A2:A1000,"dd/mm/yyyy")),REPLACE(raw_data!C2:C1000,1,4,""),raw_data!D2:F1000),(NOT(ISERROR(SEARCH(C2,raw_data!C2:C1000))))*(raw_data!D2:D1000<>"")*(raw_data!A2:A1000=D2)*(raw_data!E2:E1000=E2))
Click to expand...

this error is coming .. #CALC! after i copy this:

Excel Formula: 
=FILTER(HSTACK(DATEVALUE(TEXT(raw_data!A2:A1000,"dd/mm/yyyy")),REPLACE(raw_data!C2:C1000,1,4,""),raw_data!D2:F1000),(NOT(ISERROR(SEARCH(C2,raw_data!C2:C1000))))*(raw_data!D2:D1000<>"")*(raw_data!A2:A1000=D2)*(raw_data!E2:E1000=E2))


Shares Search Filter TEST - Copy.xlsx
ABCDE
1ParticularDateBuy/Sell
2Search
3
4DateParticularQtyBuy/SellPrice
5#CALC!
6
7
8
9
10
11
12
13
14
15
16
17
source_data
Cell Formulas
RangeFormula
A5A5=FILTER(HSTACK(DATEVALUE(TEXT(raw_data!A2:A1000,"dd/mm/yyyy")),REPLACE(raw_data!C2:C1000,1,4,""),raw_data!D2:F1000),(NOT(ISERROR(SEARCH(C2,raw_data!C2:C1000))))*(raw_data!D2:D1000<>"")*(raw_data!A2:A1000=D2)*(raw_data!E2:E1000=E2))
Named Ranges
NameRefers ToCells
all=raw_data!$A$2:$K$1000A5
 
Upvote 0
Hi, @myall_blues I found where was the error, actually raw_data!A2:A1000, date has this format DD/MM/YYY hh:mm, thats why i add this lines in your formula DATEVALUE(TEXT(raw_data!A2:A1000,"dd/mm/yyyy")) to extract only date from raw_data , but when its match with search cell D2 , it does not match due to this.. how to solve this issue so that it will match with D2..
here is the sample sheet:

Shares Search Filter TEST - Copy 1.xlsx
ABCDEF
1ParticularDateBuy/Sell
2SearchSAMSUNG06/08/2024BUY
3
4DateParticularQtyBuy/SellPriceTotals
5#CALC!
6
7
8
9
10
11
12
13
14
15
16
17
source_data
Cell Formulas
RangeFormula
A5A5=FILTER(HSTACK(DATEVALUE(TEXT(raw_data!A2:A1000,"dd/mm/yyyy")),REPLACE(raw_data!C2:C1000,1,4,""),raw_data!D2:F1000),(NOT(ISERROR(SEARCH(C2,raw_data!C2:C1000))))*(raw_data!D2:D1000<>"")*(raw_data!A2:A1000=D2)*(raw_data!E2:E1000=E2))
Named Ranges
NameRefers ToCells
all=raw_data!$A$2:$K$1000A5
 
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