Copy column values from another sheet but want to exclude some specific row name completely..

P

pankajgrover

Board Regular
Joined
Oct 27, 2022
Messages
157
Office Version
  1. 2021
Platform
  1. Windows
Copy column values from another sheet but want to exclude some specific row name completely . in example in B2 i want to exclude complete row name "Samsung" & "Netflix". how to do that ?

Shares Search Filter APPLE.xlsx
ABCDEF
1DateParticularQtyBuy/SellPriceTotals
206/08/2024 MICROSOFT25BUY135.73392.5
306/08/2024 SAMSUNG5BUY643.53217.5
406/08/2024 NETFLIX11BUY3347.736824.7
506/08/2024 APPLE50BUY28714350
606/08/2024 MICROSOFT5SELL49899.9249499.5
706/08/2024 SAMSUNG1BUY5187.75187.7
806/08/2024 NETFLIX25BUY135.843396
906/08/2024 APPLE25BUY287.87195
1006/08/2024 MICROSOFT11BUY1610.6517717.15
1106/08/2024 SAMSUNG4BUY2651.610606.4
1206/08/2024 NETFLIX1BUY2650.752650.75
1306/08/2024 APPLE1SELL475.5475.5
1405/08/2024 MICROSOFT25BUY649.316232.5
1505/08/2024 SAMSUNG1BUY476.85476.85
1605/08/2024 NETFLIX25BUY800.320007.5
1704/08/2024 APPLE5BUY6536.3532681.75
source_data
Cell Formulas
RangeFormula
A2:A17A2=IF(raw_data!A2="","",DATEVALUE(TEXT(raw_data!A2,"dd/mm/yyyy")))
B2:B17B2=IF(raw_data!C2="","",REPLACE(raw_data!C2,1,3,""))
C2:E17C2=IF(raw_data!D2="", "",raw_data!D2)
F2:F17F2=IFERROR([@Qty]*[@Price],"")
Named Ranges
NameRefers ToCells
all=raw_data!$A$2:$G$989A2
 
Sort by date Sort by votes
Hi @Peter_SSs
Thanks for Reply... here below 2 my excel sheets for your reference...

Source Data:
Shares Search Filter1.xlsx
ABCDEF
1DateUsernameStocksQtyBuy/SellPrice
206/08/2024NSE MICROSOFT25BUY135.7
306/08/2024NSE SAMSUNG5BUY643.5
406/08/2024NSE NETFLIX11BUY3347.7
506/08/2024NSE APPLE50BUY287
606/08/2024NSE MICROSOFT5SELL49899.9
706/08/2024NSE SAMSUNG1BUY5187.7
806/08/2024NSE NETFLIX25BUY135.84
906/08/2024NSE APPLE25BUY287.8
1006/08/2024NSE MICROSOFT11BUY1610.65
1106/08/2024NSE SAMSUNG4BUY2651.6
1206/08/2024NSE NETFLIX1BUY2650.75
1306/08/2024NSE APPLE1SELL475.5
1405/08/2024NSE MICROSOFT25BUY649.3
1505/08/2024NSE SAMSUNG1BUY476.85
1605/08/2024NSE NETFLIX25BUY800.3
1704/08/2024NSE APPLE5BUY6536.35
Data


And finally Search Sheet:
Shares Search Filter1.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
22 
Search
Cell Formulas
RangeFormula
C4C4=SUBTOTAL(9,C6:C1000)
E4E4=F4/C4
A6:E21A6=FILTER(HSTACK((INT(Table1[Date])),REPLACE(Table1[Stocks],1,4,""),Table1[[Qty]:[Price]]),(NOT(ISERROR(SEARCH(B2,Table1[Stocks])&(SEARCH(C2,(INT(Table1[Date])))&(SEARCH(D2,Table1[Buy/Sell]))))))*(INT(Table1[Date])>=IF(E2="",1/1/1900,E2))*(INT(Table1[Date])<=IF(H2="",TODAY(),H2))*((Table1[Price])<=IF(I2="","",I2))*((Table1[Price])>=IF(J2="",1,J2))*(Table1[Qty]<>""))
H6:H22H6=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

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Try this. Add another column (Fltr) to the table in 'Data' as shown below. The column can be hidden if you want

pankajgrover_1.xlsm
ABCDEFG
1DateUsernameStocksQtyBuy/SellPriceFltr
26/08/2024 03:24 PMNSE MICROSOFT25BUY135.70
36/08/2024 03:14 PMNSE SAMSUNG5BUY643.50
46/08/2024 03:13 PMNSE NETFLIX11BUY3347.70
56/08/2024 03:13 PMNSE APPLE50BUY2870
66/08/2024 03:12 PMNSE MICROSOFT5SELL49899.90
76/08/2024 03:11 PMNSE SAMSUNG1BUY5187.71
86/08/2024 03:11 PMNSE NETFLIX25BUY135.840
96/08/2024 03:10 PMNSE APPLE25BUY287.80
106/08/2024 03:08 PMNSE MICROSOFT11BUY1610.650
116/08/2024 03:05 PMNSE SAMSUNG4BUY2651.61
126/08/2024 03:05 PMNSE NETFLIX1BUY2650.750
136/08/2024 03:05 PMNSE APPLE1SELL475.50
145/08/2024 03:05 PMNSE MICROSOFT25BUY649.30
155/08/2024 03:03 PMNSE SAMSUNG1BUY476.850
165/08/2024 03:01 PMNSE NETFLIX25BUY800.30
174/08/2024 03:01 PMNSE APPLE5BUY6536.351
Data
Cell Formulas
RangeFormula
G2:G17G2=ISNUMBER(SEARCH(Search!B$2,MID([@Stocks],5,99)))*OR(Search!C$2="",Search!C$2=INT([@Date]))*ISNUMBER(SEARCH(Search!D$2,[@[Buy/Sell]]))*(Search!E$2<=INT([@Date]))*(OR(Search!H$2="",Search!H$2>=INT([@Date])))*OR(Search!I$2="",Search!I$2>=[@Price])*(Search!J$2<=[@Price])*([@Qty]<>"")


Use these three formulas in the 'Search' sheet.
BTW, I assume that your label in J1 of your Search sheet above is a typo and should be as shown in my sheet below.

pankajgrover_1.xlsm
ABCDEFGHIJ
1ParticularDateBuy/SellFrom DateTo DatePrice<=Price>=
2SEARCHABUY1000
3
5DateParticularQtyBuy/SellPrice
66/08/2024SAMSUNG1BUY5187.7
76/08/2024SAMSUNG4BUY2651.6
84/08/2024APPLE5BUY6536.35
9
10
Search
Cell Formulas
RangeFormula
A6:A8A6=FILTER(INT(Table1[Date]),Table1[Fltr],"")
B6:B8B6=FILTER(MID(Table1[Stocks],5,99),Table1[Fltr],"")
C6:E8C6=FILTER(Table1[[Qty]:[Price]],Table1[Fltr],"")
Dynamic array formulas.
 
Upvote 1
Solution
Hi @Peter_SSs sir.. superb ... working exactly as i want .. Thank you sooooo much. you solved my issue, this sheet is working in office 2021 now. New column at G Fltr is a fantastic idea.
And the beauty of the formula is when my all search boxes have no input data and besides this i can see my below all data visible, as i input data on search boxes and enter and data filter accordingly. Thanks for giving your valuable time .. (y) :love:
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
What will be formula on Search Box as i type data filter accordingly...
2 3
Replies
26
Views
605
pankajgrover
P
D
  • Question Question
How to Copy rows from one sheet to another based on the criteria in the second sheet
Replies
7
Views
63
JEC
JEC
P
  • Locked
  • Question Question
Below formula which works on excel 365 only, but also need to be work on excel 2021...
Replies
1
Views
166
Fluff
Fluff
F
  • Solved
Copy paste from one Excel workbook to another with same sheet names and update dates in range
Replies
8
Views
514
Skiier89
S
P
  • Question Question
How to obtain average values on columns filter
Replies
3
Views
209
StephenCrump
S

Forum statistics

Threads
1,223,933
Messages
6,175,473
Members
452,646
Latest member
tudou

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