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
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
Would this be useful:

Excel Formula: 
=FILTER(A2:F17,(TRIM(B2:B17)<>"SAMSUNG")*(TRIM(B2:B17)<>"NETFLIX"))
 
Upvote 0
Could you post say 15 rows of the 'raw_data' sheet with XL2BB?
 
Upvote 0
Peter_SSs said:
Could you post say 15 rows of the 'raw_data' sheet with XL2BB?
Click to expand...
Here is data you want :

Shares Search Filter APPLE.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.02
706/08/2024NSE SAMSUNG1BUY5187.75287.173
806/08/2024NSE NETFLIX25BUY135.84142.254
906/08/2024NSE APPLE25BUY287.8300.418
1006/08/2024NSE MICROSOFT11BUY1610.650
1106/08/2024NSE SAMSUNG4BUY2651.62663.875
1206/08/2024NSE NETFLIX1BUY2650.752666.5
1306/08/2024NSE APPLE1SELL475.5512.695
1405/08/2024NSE MICROSOFT25BUY649.30
1505/08/2024NSE SAMSUNG1BUY476.85512.9
1605/08/2024NSE NETFLIX25BUY800.3835.402
1704/08/2024NSE APPLE5BUY6536.356703.889
raw_data
 
Upvote 0
pankajgrover said:
Here is data you want :
Click to expand...
Thanks. See if this meets your requirements.

pankajgrover.xlsm
ABCDE
1DateParticularQtyBuy/SellPrice
26/08/2024MICROSOFT25BUY135.7
36/08/2024APPLE50BUY287
46/08/2024MICROSOFT5SELL49899.9
56/08/2024APPLE25BUY287.8
66/08/2024MICROSOFT11BUY1610.65
76/08/2024APPLE1SELL475.5
85/08/2024MICROSOFT25BUY649.3
94/08/2024APPLE5BUY6536.35
10
source_data
Cell Formulas
RangeFormula
A2:E9A2=LET(d,raw_data!A2:F200,p,REPLACE(INDEX(d,0,3),1,4,""),f,FILTER(d,(p<>"Samsung")*(p<>"netflix")*(TAKE(d,,1)<>"")),HSTACK(INT(TAKE(f,,1)),REPLACE(CHOOSECOLS(f,3),1,4,""),TAKE(f,,-3)))
Dynamic array formulas.
 
Upvote 0
Peter_SSs said:
Thanks. See if this meets your requirements.

pankajgrover.xlsm
ABCDE
1DateParticularQtyBuy/SellPrice
26/08/2024MICROSOFT25BUY135.7
36/08/2024APPLE50BUY287
46/08/2024MICROSOFT5SELL49899.9
56/08/2024APPLE25BUY287.8
66/08/2024MICROSOFT11BUY1610.65
76/08/2024APPLE1SELL475.5
85/08/2024MICROSOFT25BUY649.3
94/08/2024APPLE5BUY6536.35
10
source_data
Cell Formulas
RangeFormula
A2:E9A2=LET(d,raw_data!A2:F200,p,REPLACE(INDEX(d,0,3),1,4,""),f,FILTER(d,(p<>"Samsung")*(p<>"netflix")*(TAKE(d,,1)<>"")),HSTACK(INT(TAKE(f,,1)),REPLACE(CHOOSECOLS(f,3),1,4,""),TAKE(f,,-3)))
Dynamic array formulas.
Click to expand...
Brilliant work (y). happy to interact with you and learn with you.. Thanks
 
Upvote 0
Here i need more help. Kindly look this excel. Here some rows which have blank values in Col. D. i want any blank values appears In Col. D rows should be deleted through filter option.
this time samsung and netflix rows not delete , instead of only blank values appears In Col. D eliminate. In example below There are 3 Rows which have blank value Row No. 8, 12 and 17, How will edit this command below for this purpose ?
=LET(d,raw_data!A2:F200,p,REPLACE(INDEX(d,0,3),1,4,""),f,FILTER(d,(p<>"Samsung")*(p<>"netflix")*(TAKE(d,,1)<>"")),HSTACK(INT(TAKE(f,,1)),REPLACE(CHOOSECOLS(f,3),1,4,""),TAKE(f,,-3)))

Thanks

Shares Search Filter APPLE.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.02
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
 
Upvote 0
Is this what you mean? If not please post XL2BB of the expected results for that new sample data.

Excel Formula: 
=LET(d,raw_data!A2:F200,f,FILTER(d,(INDEX(d,0,4)<>"")),HSTACK(INT(TAKE(f,,1)),REPLACE(CHOOSECOLS(f,3),1,4,""),TAKE(f,,-3)))
 
Upvote 0
Peter_SSs said:
Is this what you mean? If not please post XL2BB of the expected results for that new sample data.

Excel Formula: 
=LET(d,raw_data!A2:F200,f,FILTER(d,(INDEX(d,0,4)<>"")),HSTACK(INT(TAKE(f,,1)),REPLACE(CHOOSECOLS(f,3),1,4,""),TAKE(f,,-3)))
Click to expand...
Yes its working exactly ... that's i want Thanks. your formula deleting rows which have any blank values in Col. D . (y) I am converting my raw data in another sheet... but firstly its give me #spill! error because my data was in table form and after i convert to range and no #spill! error at all. So this formula does not support table ? is it like that.. Thanks

With Table:
Shares Search Filter APPLE1.xlsx
ABCDEF
1DateParticularQtyBuy/SellPriceTotals
2#SPILL!
3#SPILL!
4#SPILL!
5#SPILL!
6#SPILL!
7#SPILL!
8#SPILL!
9#SPILL!
10#SPILL!
11#SPILL!
12#SPILL!
13#SPILL!
14#SPILL!
15#SPILL!
source_data
Cell Formulas
RangeFormula
A2:A15A2=LET(d,raw_data!A2:F200,f,FILTER(d,(INDEX(d,0,4)<>"")),HSTACK(INT(TAKE(f,,1)),REPLACE(CHOOSECOLS(f,3),1,4,""),TAKE(f,,-3)))
Named Ranges
NameRefers ToCells
all=raw_data!$A$2:$G$989A2

Without Table:
Shares Search Filter APPLE1.xlsx
ABCDE
1DateParticularQtyBuy/SellPrice
206/08/2024MICROSOFT25BUY135.7
306/08/2024SAMSUNG5BUY643.5
406/08/2024NETFLIX11BUY3347.7
506/08/2024APPLE50BUY287
606/08/2024MICROSOFT5SELL49899.9
706/08/2024SAMSUNG1BUY5187.7
806/08/2024APPLE25BUY287.8
906/08/2024MICROSOFT11BUY1610.65
1006/08/2024SAMSUNG4BUY2651.6
1105/08/2024APPLE1SELL475.5
1205/08/2024MICROSOFT25BUY649.3
1305/08/2024SAMSUNG1BUY476.85
1404/08/2024NETFLIX25BUY800.3
source_data
Cell Formulas
RangeFormula
A2:E14A2=LET(d,raw_data!A2:F200,f,FILTER(d,(INDEX(d,0,4)<>"")),HSTACK(INT(TAKE(f,,1)),REPLACE(CHOOSECOLS(f,3),1,4,""),TAKE(f,,-3)))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
all=raw_data!$A$2:$G$989A2
 
Upvote 0
pankajgrover said:
Yes its working exactly
Click to expand...
Good news. (y)

pankajgrover said:
but firstly its give me #spill! error because my data was in table form and after i convert to range and no #spill! error at all. So this formula does not support table ? is it like that..
Click to expand...
If you want the results in a formal table then there will need to be more formulas ..
(Check the table name in the formulas)

pankajgrover.xlsm
ABCDE
1DateParticularQtyBuy/SellPrice
26/08/2024MICROSOFT25BUY135.7
36/08/2024SAMSUNG5BUY643.5
46/08/2024NETFLIX11BUY3347.7
56/08/2024APPLE50BUY287
66/08/2024MICROSOFT5SELL49899.9
76/08/2024SAMSUNG1BUY5187.7
86/08/2024APPLE25BUY287.8
96/08/2024MICROSOFT11BUY1610.65
106/08/2024SAMSUNG4BUY2651.6
115/08/2024APPLE1SELL475.5
125/08/2024MICROSOFT25BUY649.3
135/08/2024SAMSUNG1BUY476.85
144/08/2024NETFLIX25BUY800.3
15     
16     
17     
18     
19
20
source_data
Cell Formulas
RangeFormula
A2:A18A2=IFERROR(INDEX(INT(FILTER(raw_data!A$2:A$200,raw_data!D$2:D$200<>"")),ROW()-ROW(Table1[#Headers])),"")
B2:B18B2=IFERROR(REPLACE(INDEX(FILTER(raw_data!C$2:C$200,raw_data!D$2:D$200<>""),ROW()-ROW(Table1[#Headers])),1,4,""),"")
C2:C18C2=IFERROR(INDEX(FILTER(raw_data!D$2:D$200,raw_data!D$2:D$200<>""),ROW()-ROW(Table1[#Headers])),"")
D2:D18D2=IFERROR(INDEX(FILTER(raw_data!E$2:E$200,raw_data!D$2:D$200<>""),ROW()-ROW(Table1[#Headers])),"")
E2:E18E2=IFERROR(INDEX(FILTER(raw_data!F$2:F$200,raw_data!D$2:D$200<>""),ROW()-ROW(Table1[#Headers])),"")
 
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
513
pankajgrover
P
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
F
  • Solved
Copy paste from one Excel workbook to another with same sheet names and update dates in range
Replies
8
Views
386
Skiier89
S
P
  • Question
How to obtain average values on columns filter
Replies
3
Views
179
StephenCrump
S
I
  • Question
VBA - Copy Sheet from a workbook saved as a date - paste to specifically named sheet in another workbook based on the date
Replies
2
Views
799
Insurance Dave
I

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