Transferring a Subset of data

rwmill9716 · Jan 15, 2025

A subset of my data is shown in my Data Full image. Using the symbols in Column A, I need a macro that takes up to five rows on that data's symbol (starting with AMZN) and copies those rows beginning in Column U. The new table would look like Data Subset where there are 5 rows of AMZN, 3 rows of APP (only 3 entries in Column A), 5 rows of COST, 3 rows of DAL, and 4 rows of DASH. The entire data set will contain more symbols, but each symbol will be grouped, as they are in Data Full.

Fuji · Thursday at 12:40 AM

try

Code:

Sub test()
    [t2] = "=countif(a$2:a2,a2)<=5"
    Range("a1", Range("a" & Rows.Count).End(xlUp)).Resize(, 19).AdvancedFilter 2, [t1:t2], [u1]
    [t2].Clear
End Sub

rwmill9716 · Monday at 4:56 PM

Fuji said:

try

Code:

Sub test()
    [t2] = "=countif(a$2:a2,a2)<=5"
    Range("a1", Range("a" & Rows.Count).End(xlUp)).Resize(, 19).AdvancedFilter 2, [t1:t2], [u1]
    [t2].Clear
End Sub

Fugi, thanks for working on this. When I run this subroutine, I get "True" in cell T2, and it stops there. Note, I call the subroutine ICSort. What does the t1:t2 do?

Fuji · Monday at 6:09 PM

1) How many rows do you have?
2) [t1:t2] is equivalent to Range("t1:t2") and used as criteria range in advanced filter, so T1 must be blank.

Any error? or just stopped?

Anyway, working with the picture is too hard, so it will help if you use
XL2BB - Excel Range to BBCode
or upload your workbook somewhere like Dropbox.

rwmill9716 · 2025-01-21T09:07:02-0500

I have ~1,000 rows with up to 40 symbols. No error message; it prints "True" om cell T2 and then stops. I tried to step through the macro, and it just highlights the 3rd row in yellow. I assume that it works for you if you create a dummy table. Again, thanks for your help. As it stands, I'm doing this by hand.

rwmill9716 · 2025-01-21T15:00:00-0500

There is an error message.

rwmill9716 · 2025-01-21T15:33:19-0500

Fuji said:
1) How many rows do you have?
2) [t1:t2] is equivalent to Range("t1:t2") and used as criteria range in advanced filter, so T1 must be blank.

Any error? or just stopped?

Anyway, working with the picture is too hard, so it will help if you use
XL2BB - Excel Range to BBCode
or upload your workbook somewhere like Dropbox.

There are ~1,000 rows using ~40 symbols (both vary). Prints "TRUE" in cell T2, and there is a

TEST.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

1

TSM Iron Condors

L Put

S Put

S Call

L Call

1/21/2025

2:31 PM

CT

2

Symbol

Price~

Exp Date

Leg1

Ask1

Leg2

Bid2

Leg3

Bid3

Leg4

Ask4

BE+

BE-

Max Profit

Max Loss

Avg IV

Risk/ Reward%

BE Prob

Days to Expire

3

AMZN

231.31

1/24/25

22500.00%

0.56

217.5

0.14

224.58

0.42

708.00%

0.06

16.86 to 1

0.862

0.8463

3

4

AMZN

231.31

1/24/25

22750.00%

1.03

217.5

0.14

226.61

0.89

911.00%

0.10

10.24 to 1

0.776

0.7301

3

5

AMZN

231.31

1/24/25

23000.00%

1.82

217.5

0.14

228.32

1.68

1082.00%

0.16

6.44 to 1

0.684

0.583

3

6

AMZN

231.31

1/24/25

22750.00%

1.03

220

0.20

226.67

0.83

667.00%

0.12

8.04 to 1

0.773

0.7301

3

7

AMZN

231.31

1/24/25

23000.00%

1.82

220

0.20

228.38

1.62

838.00%

0.19

5.17 to 1

0.681

0.583

3

8

AMZN

231.31

1/24/25

22750.00%

1.03

222.5

0.33

226.8

0.70

430.00%

0.16

6.14 to 1

0.766

0.7301

3

9

AMZN

231.31

1/24/25

23000.00%

1.82

222.5

0.33

228.51

1.49

601.00%

0.25

4.03 to 1

0.673

0.583

3

10

ANF

125.43

1/24/25

12500.00%

2.25

120

0.75

123.5

1.50

350.00%

0.43

2.33 to 1

0.622

0.5275

3

11

CCL

26.07

1/31/25

2600.00%

0.68

24

0.13

25.45

0.55

145.00%

0.38

2.64 to 1

0.641

0.5161

10

12

DAL

68.32

1/24/25

6700.00%

0.72

65

0.27

66.55

0.45

155.00%

0.29

3.44 to 1

0.733

0.6784

3

13

DAL

68.32

1/24/25

6800.00%

1.04

65

0.27

67.23

0.77

223.00%

0.35

2.90 to 1

0.649

0.5444

3

14

DAL

68.32

1/24/25

6800.00%

1.04

66

0.46

67.42

0.58

142.00%

0.41

2.45 to 1

0.624

0.5444

3

15

DIS

109.16

1/24/25

10900.00%

0.80

105

0.08

108.28

0.72

328.00%

0.22

4.56 to 1

0.65

0.5278

3

16

DIS

109.16

1/24/25

10900.00%

0.80

106

0.14

108.34

0.66

234.00%

0.28

3.55 to 1

0.64

0.5278

3

17

DIS

109.16

1/24/25

10900.00%

0.80

107

0.26

108.46

0.54

146.00%

0.37

2.70 to 1

0.62

0.5278

3

18

DIS

109.16

1/31/25

10800.00%

0.93

105

0.30

107.37

0.63

237.00%

0.27

3.76 to 1

0.686

0.6232

10

19

GM

53.53

1/31/25

5300.00%

1.56

51

0.87

52.31

0.69

131.00%

0.53

1.90 to 1

0.601

0.5438

10

20

NVDA

139.26

1/24/25

13600.00%

0.67

129

0.11

135.44

0.56

644.00%

0.09

11.50 to 1

0.797

0.7606

3

21

NVDA

139.26

1/24/25

13700.00%

0.92

129

0.11

136.19

0.81

719.00%

0.11

8.88 to 1

0.747

0.6877

3

22

NVDA

139.26

1/24/25

13800.00%

1.25

129

0.11

136.86

1.14

786.00%

0.15

6.89 to 1

0.698

0.6071

3

23

NVDA

139.26

1/24/25

13900.00%

1.65

129

0.11

137.46

1.54

846.00%

0.18

5.49 to 1

0.651

0.5223

3

24

NVDA

139.26

1/24/25

13600.00%

0.67

130

0.13

135.46

0.54

546.00%

0.10

10.11 to 1

0.796

0.7606

3

25

NVDA

139.26

1/24/25

13700.00%

0.92

130

0.13

136.21

0.79

621.00%

0.13

7.86 to 1

0.746

0.6877

3

26

NVDA

139.26

1/24/25

13800.00%

1.25

130

0.13

136.88

1.12

688.00%

0.16

6.14 to 1

0.697

0.6071

3

27

NVDA

139.26

1/24/25

13900.00%

1.65

130

0.13

137.48

1.52

748.00%

0.20

4.92 to 1

0.65

0.5223

3

28

NVDA

139.26

1/24/25

13600.00%

0.67

131

0.16

135.49

0.51

449.00%

0.11

8.80 to 1

0.794

0.7606

3

29

NVDA

139.26

1/24/25

13700.00%

0.92

131

0.16

136.24

0.76

524.00%

0.15

6.89 to 1

0.744

0.6877

3

30

NVDA

139.26

1/31/25

13900.00%

3.20

138

2.78

138.58

0.42

58.00%

0.72

1.38 to 1

0.531

0.5119

10

31

SHOP

106

1/24/25

10500.00%

0.97

102

0.34

104.37

0.63

237.00%

0.27

3.76 to 1

0.665

0.603

3

32

SHOP

106

1/24/25

10600.00%

1.40

102

0.34

104.94

1.06

294.00%

0.36

2.77 to 1

0.609

0.5

3

33

TSM

219.33

1/31/25

20500.00%

0.80

195

0.28

204.48

0.52

948.00%

0.05

18.23 to 1

0.884

0.8749

10

34

TSM

219.33

1/31/25

20750.00%

1.12

195

0.28

206.66

0.84

1166.00%

0.07

13.88 to 1

0.844

0.8273

10

35

TSM

219.33

1/31/25

21000.00%

1.56

195

0.28

208.72

1.28

1372.00%

0.09

10.72 to 1

0.801

0.7703

10

36

UAL

110.15

1/24/25

10600.00%

3.30

103

2.36

105.06

0.94

206.00%

0.46

2.19 to 1

0.657

0.629

3

37

UAL

110.15

1/24/25

10700.00%

3.70

103

2.36

105.66

1.34

266.00%

0.50

1.99 to 1

0.639

0.5982

3

38

UAL

110.15

1/24/25

10800.00%

4.15

103

2.36

106.21

1.79

321.00%

0.56

1.79 to 1

0.623

0.5671

3

39

UAL

110.15

1/24/25

10900.00%

4.60

103

2.36

106.76

2.24

376.00%

0.60

1.68 to 1

0.606

0.5358

3

40

UAL

110.15

1/24/25

11000.00%

5.10

103

2.36

107.26

2.74

426.00%

0.64

1.55 to 1

0.59

0.5047

3

41

UAL

110.15

1/24/25

10700.00%

3.70

105

3.05

106.35

0.65

135.00%

0.48

2.08 to 1

0.618

0.5982

3

42

UAL

110.15

1/24/25

10800.00%

4.15

105

3.05

106.9

1.10

190.00%

0.58

1.73 to 1

0.601

0.5671

3

43

UAL

110.15

1/24/25

10900.00%

4.60

105

3.05

107.45

1.55

245.00%

0.63

1.58 to 1

0.584

0.5358

3

44

UAL

110.15

1/24/25

11000.00%

5.10

105

3.05

107.95

2.05

295.00%

0.69

1.44 to 1

0.569

0.5047

3

45

UAL

110.15

1/24/25

10900.00%

4.60

107

3.80

108.2

0.80

120.00%

0.67

1.50 to 1

0.561

0.5358

3

46

UAL

110.15

1/24/25

11000.00%

5.10

107

3.80

108.7

1.30

170.00%

0.76

1.31 to 1

0.545

0.5047

3

47

UAL

110.15

1/24/25

11000.00%

5.10

108

4.25

109.15

0.85

115.00%

0.74

1.35 to 1

0.531

0.5047

3

Short Iron Condor

Cell Formulas
Range	Formula
Q1	Q1	=NOW()
R1	R1	=NOW()-TRUNC(NOW())
S3:S47	S3	=C3-TODAY()

n error message.

Fuji · 2025-01-21T23:58:34-0500

OOps, sorry I completely missed the first row....
try

Code:

Sub test()
    [u:am].CurrentRegion.Clear
    [t2] = "=countif(a$3:a3,a3)<=5"
    Range("a2", Range("a" & Rows.Count).End(xlUp)).Resize(, 19).AdvancedFilter 2, [t1:t2], [u2]
    [t2].Clear
End Sub

Transferring a Subset of data

rwmill9716

Well-known Member

Attachments

Excel Facts

Fuji

Active Member

rwmill9716

Well-known Member

Fuji

Active Member

rwmill9716

Well-known Member

rwmill9716

Well-known Member

Attachments

rwmill9716

Well-known Member

Attachments

Fuji

Active Member

Similar threads

Forum statistics

Share this page

Transferring a Subset of data

Well-known Member

Attachments

Excel Facts

Active Member

Well-known Member

Active Member

Well-known Member

Well-known Member

Attachments

Well-known Member

Attachments

Active Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock