ebea
Active Member
- Joined
- Jul 12, 2008
- Messages
- 302
- Office Version
- 2021
- Platform
- Windows
Hi! I have a very large dataset (+100000 rows), where I should take out data, based on dates, and out of these, again list some of these data, in Columns.
this works fine, by using Array index formulas. But to try to speed up the proces, I set these same formulas, into a VBA solution.
But when I run this VBA, the results end up in a FALSE return (L3 and M3), but if you look at L3 and M4 the "normally" array formulas work.
So any of you in here, which have an idea on how to solve this, or maybe another idea, to speed up the process, list these Columns using VBA.
VBA kode, below here.
this works fine, by using Array index formulas. But to try to speed up the proces, I set these same formulas, into a VBA solution.
But when I run this VBA, the results end up in a FALSE return (L3 and M3), but if you look at L3 and M4 the "normally" array formulas work.
So any of you in here, which have an idea on how to solve this, or maybe another idea, to speed up the process, list these Columns using VBA.
test-index.xlsm | ||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | |||
1 | ||||||||||||||||||||||||||||
2 | ||||||||||||||||||||||||||||
3 | 21-07-2021 | 30 | 13703 | 14VM0002737 | 4,290078256 | 0 | 21 | FALSK | FALSK | 0 | 1 | 0 | 1 | 0 | 1 | 0 | 1 | 0 | 1 | 0 | 0 | |||||||
4 | 21-07-2021 | 30 | 13704 | 14VM0002737 | -0,057634882 | 1 | 14VM0002737 | 4,2900783 | -0,057635 | 1 | 4,6806388 | -0,254155 | 1 | 4,42868 | 5 | 3 | ||||||||||||
5 | 22-07-2021 | 30 | 13705 | 14VM0002737 | 4,680638751 | 0 | ||||||||||||||||||||||
6 | 22-07-2021 | 30 | 13706 | 14VM0003031 | -5,625878245 | 0 | ||||||||||||||||||||||
7 | 22-07-2021 | 30 | 13707 | 14VM0002735 | 6,333088848 | 0 | ||||||||||||||||||||||
8 | 22-07-2021 | 30 | 13708 | 14VM0002495 | 4,866441490 | 0 | ||||||||||||||||||||||
9 | 22-07-2021 | 30 | 13709 | 14VM0002623 | -0,241104024 | 1 | ||||||||||||||||||||||
10 | 22-07-2021 | 30 | 13710 | 14VM0002329 | 7,084553141 | 0 | ||||||||||||||||||||||
11 | 22-07-2021 | 30 | 13711 | 14VM0002718 | 2,275177043 | 1 | ||||||||||||||||||||||
12 | 22-07-2021 | 30 | 13712 | 14VM0003278 | 5,563642157 | 0 | ||||||||||||||||||||||
13 | 22-07-2021 | 30 | 13713 | 14VM0002702 | 5,587300185 | 0 | ||||||||||||||||||||||
14 | 22-07-2021 | 30 | 13714 | 14VM0002494 | 1,963967872 | 1 | ||||||||||||||||||||||
15 | 22-07-2021 | 30 | 13715 | 14VM0003025 | 1,067954037 | 1 | ||||||||||||||||||||||
16 | 22-07-2021 | 30 | 13716 | 14VM0002625 | -3,641699777 | 0 | ||||||||||||||||||||||
17 | 22-07-2021 | 30 | 13717 | 14VM0002414 | 1,381285221 | 1 | ||||||||||||||||||||||
18 | 22-07-2021 | 30 | 13718 | 14VM0002449 | 2,240493636 | 1 | ||||||||||||||||||||||
19 | 22-07-2021 | 30 | 13719 | 14VM0002446 | 2,386489200 | 1 | ||||||||||||||||||||||
20 | 22-07-2021 | 30 | 13720 | 14VM0002630 | 5,212646248 | 0 | ||||||||||||||||||||||
21 | 22-07-2021 | 30 | 13721 | 14VM0002076 | 1,739811762 | 1 | ||||||||||||||||||||||
22 | 22-07-2021 | 30 | 13722 | 14VM0002018 | 2,183079615 | 1 | ||||||||||||||||||||||
23 | 22-07-2021 | 30 | 13723 | 14VM0002737 | -0,254155390 | 1 | ||||||||||||||||||||||
24 | 22-07-2021 | 30 | 13725 | 14VM0002735 | -0,288815950 | 1 | ||||||||||||||||||||||
25 | 22-07-2021 | 30 | 13726 | 14VM0002495 | 0,211564776 | 1 | ||||||||||||||||||||||
26 | 22-07-2021 | 30 | 13727 | 14VM0002623 | -0,797395924 | 1 | ||||||||||||||||||||||
27 | 22-07-2021 | 30 | 13729 | 14VM0002718 | 0,238674721 | 1 | ||||||||||||||||||||||
28 | 22-07-2021 | 30 | 13730 | 14VM0003278 | 0,183337719 | 1 | ||||||||||||||||||||||
29 | 22-07-2021 | 30 | 13731 | 14VM0002702 | -0,031970625 | 1 | ||||||||||||||||||||||
30 | 22-07-2021 | 30 | 13732 | 14VM0002494 | -0,102351378 | 1 | ||||||||||||||||||||||
31 | 22-07-2021 | 30 | 13733 | 14VM0003025 | 0,237247021 | 1 | ||||||||||||||||||||||
32 | 22-07-2021 | 30 | 13734 | 14VM0002625 | 0,147276797 | 1 | ||||||||||||||||||||||
33 | 22-07-2021 | 30 | 13735 | 14VM0002414 | 0,188500177 | 1 | ||||||||||||||||||||||
34 | 22-07-2021 | 30 | 13736 | 14VM0002449 | 0,225308098 | 1 | ||||||||||||||||||||||
35 | 22-07-2021 | 30 | 13737 | 14VM0002446 | -0,424440454 | 1 | ||||||||||||||||||||||
36 | 22-07-2021 | 30 | 13738 | 14VM0002630 | 0,216864487 | 1 | ||||||||||||||||||||||
37 | 22-07-2021 | 30 | 13739 | 14VM0002076 | 0,314524464 | 1 | ||||||||||||||||||||||
38 | 22-07-2021 | 30 | 13740 | 14VM0002018 | 0,234780394 | 1 | ||||||||||||||||||||||
39 | 22-07-2021 | 30 | 13741 | 14VM0002737 | 4,428680012 | 0 | ||||||||||||||||||||||
40 | 22-07-2021 | 30 | 13742 | 14VM0002198 | 5,701634592 | 0 | ||||||||||||||||||||||
41 | 22-07-2021 | 30 | 13743 | 14VM0002735 | 6,051488293 | 0 | ||||||||||||||||||||||
42 | 22-07-2021 | 30 | 13744 | 14VM0002495 | 5,252221262 | 0 | ||||||||||||||||||||||
43 | 22-07-2021 | 30 | 13745 | 14VM0003210 | 5,761406658 | 0 | ||||||||||||||||||||||
44 | 22-07-2021 | 30 | 13746 | 14VM0003064 | -1,815352706 | 1 | ||||||||||||||||||||||
45 | 22-07-2021 | 30 | 13747 | 14VM0002718 | 2,463997073 | 1 | ||||||||||||||||||||||
46 | 22-07-2021 | 30 | 13748 | 14VM0003278 | 6,273371314 | 0 | ||||||||||||||||||||||
47 | 22-07-2021 | 30 | 13749 | 14VM0002702 | 5,695185959 | 0 | ||||||||||||||||||||||
48 | 22-07-2021 | 30 | 13750 | 14VM0002494 | 3,099040603 | 1 | ||||||||||||||||||||||
sep21 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
N3:N4,X3:X4,V3:V4,T3:T4,R3:R4,P3:P4 | N3 | =IFERROR(IF(M3="","",IF(AND(M3>-3.5,M3<3.5),"1",""))+0,"") |
O3:O4 | O3 | =IF($L3="","",IFERROR(INDEX($G$3:$G$9000,SMALL(IF($F$3:$F$9000=$L3,ROW($F$3:$F$9000)-ROW($F$3)+1),COLUMNS($M$3:N3))),"")) |
Q3:Q4 | Q3 | =IF($L3="","",IFERROR(INDEX($G$3:$G$9000,SMALL(IF($F$3:$F$9000=$L3,ROW($F$3:$F$9000)-ROW($F$3)+1),COLUMNS($M$3:O3))),"")) |
S3:S4 | S3 | =IF($L3="","",IFERROR(INDEX($G$3:$G$9000,SMALL(IF($F$3:$F$9000=$L3,ROW($F$3:$F$9000)-ROW($F$3)+1),COLUMNS($M$3:P3))),"")) |
U3:U4 | U3 | =IF($L3="","",IFERROR(INDEX($G$3:$G$9000,SMALL(IF($F$3:$F$9000=$L3,ROW($F$3:$F$9000)-ROW($F$3)+1),COLUMNS($M$3:Q3))),"")) |
W3:W4 | W3 | =IF($L3="","",IFERROR(INDEX($G$3:$G$9000,SMALL(IF($F$3:$F$9000=$L3,ROW($F$3:$F$9000)-ROW($F$3)+1),COLUMNS($M$3:R3))),"")) |
Y3:Y4 | Y3 | =IF(L3<="","",COUNTIF($F$3:$F$7000,L3)) |
Z3:Z4 | Z3 | =IF(L3>"",COUNTIFS($F$3:$F$7000,L3,$H$3:$H$7000,"=0"),"") |
L4 | L4 | =IFERROR(INDEX($F$3:$F$1000,MATCH(0,COUNTIF($L$2:L3,$F$3:$F$1000),0)),"") |
M4 | M4 | =IF(L4="","",IFERROR(INDEX($G$3:$G$9000,SMALL(IF($F$3:$F$9000=$L4,ROW($F$3:$F$9000)-ROW($F$3)+1),COLUMNS($M$3:M4))),"")) |
D3:D48 | D3 | =WEEKNUM(C3,2) |
H3:H48 | H3 | =IF(G3="","",IF(AND(G3>-3.5,G3<3.5),"1","0"))+0 |
Press CTRL+SHIFT+ENTER to enter array formulas. |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
Z3:Z4 | Cell Value | >0 | text | NO |
Z4 | Cell Value | =0 | text | NO |
Z3 | Cell Value | =0 | text | NO |
M3:X4 | Cell Value | <-3,5 | text | NO |
M3:X4 | Cell Value | >3,5 | text | NO |
I3:I4 | Cell Value | =TEGN(252) | text | NO |
I3:I4 | Cell Value | >TEGN(251) | text | NO |
J4 | Cell Value | =0 | text | NO |
VBA kode, below here.
VBA Code:
Sub Unique()
Dim rData As Range
With Sheets("sep21")
Set rData = .Range("F3", .Range("F" & Rows.Count).End(xlUp))
End With
Sheets("sep21").Range("K3").Value = _
Evaluate(Replace("=SUM(IF(FREQUENCY(IF(sep21!@<>"""",MATCH(sep21!@,sep21!@,0))," _
& "ROW(sep21!@)-ROW(sep21!F3)+1),1))", "@", rData.Address))
Sheets("sep21").Range("L3") = FormulaArray = "=IFERROR(INDEX($F$3:$F$" & Lastrow & ",MATCH(0,COUNTIF($L$2:L2,$F$3:$F$" & Lastrow & "),0)),"")"
Sheets("sep21").Range("M3") = FormulaArray = ("=IFERROR(INDEX($G$3:$G$" & Lastrow & ",SMALL(IF($F$3:$F$" & Lastrow & "=$L3,ROW($F$3:$F$" & Lastrow & ")-ROW($F$3)+1),COLUMNS($M$3:M3)),""))")
End Sub