Sorry, I have been gone a while, and had lost sight of this thread. My bad.
It sounds like the condition you speak of is IFERROR(long formula,""). That would trigger errors. Try IFERROR(long formula,[SIZE=14px]0[/SIZE])
Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.
Sorry, I have been gone a while, and had lost sight of this thread. My bad.
It sounds like the condition you speak of is IFERROR(long formula,""). That would trigger errors. Try IFERROR(long formula,[SIZE=14px]0[/SIZE])
This alternative might simpler to follow on the Phase #s extraction part.
=IFERROR(SUMPRODUCT(INDEX(Data!$A$2:$BG$100,,MATCH(Summary!$A3,Data!$A$1:$BG$1,0))*
(INDEX(Data!$A$2:$BG$100,,MATCH($B$2,Data!$A$1:$BG$1,0))=$B3)*(INDEX(Data!$A$2:$BG$100,,MATCH("Type",Data!$A$1:$BG$1,0))=C$2)*
(INDEX(Data!$A$2:$BG$100,,MATCH("Phases",Data!$A$1:$BG$1,0))=[COLOR=#FF0000][B]--TRIM(RIGHT(SUBSTITUTE(LOOKUP("zzzzz",$C$1:C$1)," ",REPT(" ",99)),99))[/B][/COLOR])),0)
Before starting I noticed there is a "Key" designation in 'Summary' B1. I saw no indications in examples that you wish to include those (from column 'Data' BG). If you do you will need to include additional criteria. Hopefully the enclosed can serve as a guide on how to do that.
The formula filled down and across
=IFERROR(SUMPRODUCT(INDEX(Data!$A$2:$BG$100,,MATCH(Summary!$A3,Data!$A$1:$BG$1,0))*
(INDEX(Data!$A$2:$BG$100,,MATCH($B$2,Data!$A$1:$BG$1,0))=$B3)*(INDEX(Data!$A$2:$BG$100,,MATCH("Type",Data!$A$1:$BG$1,0))=C$2)*
(INDEX(Data!$A$2:$BG$100,,MATCH("Phases",Data!$A$1:$BG$1,0))=LOOKUP(1E+306,--RIGHT(LOOKUP("zzzz",$C$1:C$1),ROW($1:$10))))),0)
There are merged cells in 'Summary,' and the Phases numbers in 'Data' suggest there could be multiple digit phase #s. If you are not aware of it merged cells cause havoc for formulas. In consideration of both find this portion of the formula that extracts Phase #s. It is reliable but a bit involved. If you would like help analyzing it please let us know.
.
Good deal! Thanks for the feedback and thanks for posting. I learned something new with this puzzle.
If you have room for a helper column I have a work around for the volatility issue.
I have to agree with Mumps. A sample is worth thousands of words.
Without a representative data set that reflects the dilemma I can't say. However it sounds like it would be a simple matter of changing the relative column ranges in the formula. If the header labels are exactly the same I wouldn't think that would be a problem.
With the exception of the column of 1's all the formulas are driven by the formula component:
MATCH(1,INDEX(--(ROWS(E$3:E3)<=SUBTOTAL(9,OFFSET(INDEX(Units,1),,,ROW(Units)-MIN(ROW(Units))+1))),0),0)
It always returns each item in the 'Fruit', 'Units' and 'Total Cost' n number of Units times.
However:
Quote... the data set is large
You hadn't mentioned that before. That would be the only potential problem as I see from the description. As OFFSET is a volatile function too many of them can slow a workbook down. Up to about 1000 cells this is usually not a problem.
In numbers ... rows and columns ... how large is the anticipated output range?
Sure if a formula works that just as good, it does not have to be VBA
Find the following dynamic named ranges in Name Manager:
[Table="class: grid"]
Fruit
=Sheet1!$A$3:INDEX(Sheet1!$A:$A,MATCH("zzzzzz",Sheet1!$A:$A,1))
Total_Cost
=Sheet1!$C$3:INDEX(Sheet1!$C:$C,MATCH(1E+306,Sheet1!$C:$C,1))
Units
=Sheet1!$B$3:INDEX(Sheet1!$B:$B,MATCH(1E+306,Sheet1!$B:$B,1))
[/table]
Then in E3 and filled down
=IF(ROWS(E$3:E3)>SUM(Units),"",INDEX(Fruit,MATCH(1,INDEX(--(ROWS($1:1)<=SUBTOTAL(9,OFFSET(INDEX(Units,1),,,ROW(Units)-MIN(ROW(Units))+1))),0),0)))
In F3 and filled down
And in G3 and filled down
Does this have to be VBA if it can be done by formula?
I have a formula solution.
[SIZE=14px]I think F5 (or Ctrl+G) is the most useful [/SIZE][SIZE=14px]work around[/SIZE][SIZE=14px] to go to the Name box directly in Excel [/SIZE][SIZE=14px]till[/SIZE][SIZE=14px] Ver 2016.[/SIZE]
Did something change in Ver 2016? If so please enlighten me. Thanks.
Re: Lookup Values in any column
Edit Never mind. This won't work. There are duplicate numbers in Post #1, and if one of those numbers is entered in A1 it errors.
This is one way to do it.
This formula in B1
=INDEX($A$2:$F$11,MATCH(1,MMULT(--(A1=$A$2:$F$11),{1;1;1;1;1;1}),0),MATCH(1,MMULT({1,1,1,1,1,1,1,1,1,1},--(A1=$A$2:$F$11)),0)+1)
[TABLE="class: grid"]
Row\Col
A
B
C
D
E
F
1
EL3
220
2
EL1
73
PL1
55
HVAC1
73
3
EL2
146
PL2
110
HVAC2
146
4
EL3
220
PL3
165
HVAC3
220
5
EL4
293
PL4
220
HVAC4
293
6
EL5
366
PL5
275
HVAC5
366
7
EL6
439
PL6
330
HVAC6
439
8
EL7
512
PL7
385
HVAC7
512
9
EL8
585
PL8
440
HVAC8
585
10
EL9
659
PL9
495
HVAC9
659
11
EL10
732
PL10
550
HVAC10
732
[/TABLE]
Re: Subtracting a number of months to a date
Have you tried EDATE?
Syntax is
I don't know how you would reference values in TextBox6 unless it is the name of a cell, but you can add (or subtract) number of months to / from the start date.
I couldn't tell from the upload.
Did this help?
Re: Formatting dates
Quote from S O;771743I think the logic for the date is a constant "1" followed by the date formatted as YYMMDD:
="1"&TEXT(A1,"YYMMDD") obviously a bit more complex in the actual solution, just showing as an example
other than that, brilliant formula
Good catch. I lost the "6" in the "brush". Thanks for the backup.
Re: Formatting dates
Quote from pragov;771722Display More[tr]
...............
..........How do I do this in one single function or in three steps using VBA?
[TABLE="width: 547"][/tr]
[tr]
[/TABLE]
[TABLE="width: 124"][/tr]
[TD="class: xl82"]Thanks,
pragov
[/TD]
[/TABLE]
Try this formula.
=11&TEXT(MID(A1,FIND("-",A1)-4,99),"mmdd")&" - Pdate "&TEXT(INT(MOD(--MID(A1,FIND("-",A1)-4,99),1)*24),"0")&" - PHour "&TEXT(INT(MOD(MOD(--MID(A1,FIND("-",A1)-4,99),1)*24,1)*60),"0")&" - Pmin"
[TABLE="class: grid"]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]
A
[/TD]
[TD="bgcolor: #DCE6F1"]
B
[/TD]
[TD="bgcolor: #DCE6F1"]
1
[/TD]
ProcessDate as 2016-05-18 19:27:40
110518 - Pdate 19 - PHour 27 - Pmin
[/TABLE]
Re: Align several columns into 1 columns
Another way with formulas.
A helper column in B with this array-entered formula in B5 and filled down until you get blanks.
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter. (CSE for short.)
Then this regular formula in A5 filled down until you get blanks.
[Table="class: grid"]
[td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A
[/td][td="bgcolor: #DCE6F1"]
B
[/td][td="bgcolor: #DCE6F1"]
C
[/td]
[td="bgcolor: #DCE6F1"]
1
[/td]
a
D
g
[td="bgcolor: #DCE6F1"]
2
[/td]
b
E
h
[td="bgcolor: #DCE6F1"]
3
[/td]
c
F
[td="bgcolor: #DCE6F1"]
4
[/td]
[td="bgcolor: #DCE6F1"]
5
[/td]
a
1
[td="bgcolor: #DCE6F1"]
6
[/td]
b
1
[td="bgcolor: #DCE6F1"]
7
[/td]
c
1
[td="bgcolor: #DCE6F1"]
8
[/td]
D
2
[td="bgcolor: #DCE6F1"]
9
[/td]
E
2
[td="bgcolor: #DCE6F1"]
10
[/td]
F
2
[td="bgcolor: #DCE6F1"]
11
[/td]
g
3
[td="bgcolor: #DCE6F1"]
12
[/td]
h
3
[td="bgcolor: #DCE6F1"]
13
[/td]
[/table]
Re: Count the values by ignoring cells which contain NA
Hi ravs_1006, and Welcome.
It would be helpful if you could upload a sample Excel workbook of what you describe. Live data often clarifies at a glance.
How do I attach a file to a post?
To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'. To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.
On this page, below the message box, you will find a button labelled 'Manage Attachments'. Clicking this button will open a new window for uploading attachments. You can upload an attachment either
from your computer or from another URL by using the appropriate box on this page. Alternatively you can click the Attachment Icon to open this page.
To upload a file from your computer, click the 'Browse' button and locate the file. To upload a file from another URL, enter the full URL for the file in the second box on this page. Once you have
completed one of the boxes, click 'Upload'.
Once the upload is completed the file name will appear below the input boxes in this window. You can then close the window to return to the new post screen.
Re: Combine if statement with 3 different return
Quote from S O;771542This should do what you need:
=IF(OR(C5="",I5=""),"",IF(I5="F",7,SWITCH(WEEKDAY(C5),7,6,1,6,5)))
Is SWITCH a new 2016 function?
Re: Counting numbers that appear in succession over different columns
Another way with data in A1:E13.
With this array-entered formula in C16 as in the below and filled down
If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter. (CSE for short.)
And then this array-entered formula in D16 and filled across.
[TABLE="class: grid"]
Row\Col
A
B
C
D
E
F
G
H
1
A
B
C
D
E
2
1
2
3
37
38
3
4
5
6
4
7
8
9
5
10
11
12
6
13
14
15
7
16
17
18
8
19
20
21
9
22
23
24
10
25
26
27
11
28
29
30
12
31
32
33
13
34
35
36
14
15
A
B
C
D
E
16
Row 1:
6
C
3
2
4
0
1
17
Row 2:
7
A
18
Row 3:
2
B
19
Row 4:
34
A
20
Row 5:
15
C
21
Row 6:
36
C
22
Row 7:
21
C
23
Row 8:
12
C
24
Row 9:
38
E
25
Row 10:
33
C
26
Row 11:
1
A
27
Row 12:
7
A
28
Row 13:
4
A
29
Row 14:
18
C
30
Row 15:
15
C
31
Row 16:
28
A
32
Row 17:
29
B
33
Row 18:
23
B
34
Row 19:
4
A
35
Row 20:
26
B
[/TABLE]