Posts by FlameRetired

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.

LOOKUP(1E+306,--RIGHT(LOOKUP("zzzz",$C$1:C$1),ROW($1:$10)))

.

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?

Quote from Basil Javed

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"]

[/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

=IF(ROWS(E$3:E3)>SUM(Units),"",1)

And in G3 and filled down

=IF(ROWS(E$3:E3)>SUM(Units),"",ROUND(INDEX(Total_Cost,MATCH(1,INDEX(--(ROWS($1:1)<=
SUBTOTAL(9,OFFSET(INDEX(Units,1),,,ROW(Units)-MIN(ROW(Units))+1))),0),0))/
INDEX(Units,MATCH(1,INDEX(--(ROWS($1:1)<=SUBTOTAL(9,OFFSET(INDEX(Units,1),,,ROW(Units)-MIN(ROW(Units))+1))),0),0)),2))

Does this have to be VBA if it can be done by formula?

I have a formula solution.

Quote from zopfan

[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

EL3

EL1

PL1

HVAC1

EL2

PL2

HVAC2

EL3

PL3

HVAC3

EL4

PL4

HVAC4

EL5

PL5

HVAC5

EL6

PL6

HVAC6

EL7

PL7

HVAC7

EL8

PL8

HVAC8

EL9

PL9

HVAC9

EL10

PL10

HVAC10

[/TABLE]

Re: Subtracting a number of months to a date

Have you tried EDATE?

Syntax is

=EDATE(start_date, months)

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: How To Extract Certain substring within a cell of Strings

If it doesn't have to be VBA this formula works on the example.

Assuming string is in A1.

=TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1," ",REPT(" ",256),1),":",REPT(" ",256)),256,256))

Re: Formatting dates

Quote from S O;771743

I 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;771722

...............
..........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]

[/tr]

[/TABLE]

Display More

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"]

[/TD]
[TD="bgcolor: #DCE6F1"]

[/TD]

[TD="bgcolor: #DCE6F1"]

[/TD]

[/TABLE]

Re: Align several columns into 1 columns

This would be another formula way without a helper column.

=IF(ROWS($5:5)>COUNTA($A$1:$C$3),"",INDEX($A$1:$C$3,MOD(ROWS($5:5),-3)+3,CEILING(ROWS($5:5)/3,1)))

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(ROWS($5:5)>COUNTA($A$1:$C$3),"",SMALL(IF($A$1:$C$3<>"",COLUMN($A$1:$C$3)),ROWS($5:5)))

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.

=IF(B5="","",INDEX($A$1:$C$3,COUNTIF($B$5:B5,B5),B5))

[Table="class: grid"]

[td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

[/td][td="bgcolor: #DCE6F1"]

[/td][td="bgcolor: #DCE6F1"]

[/td]

[td="bgcolor: #DCE6F1"]

[/td]

[td="bgcolor: #DCE6F1"]

[/td]

[td="bgcolor: #DCE6F1"]

[/td]

[td="bgcolor: #DCE6F1"]

[/td]

[td="bgcolor: #DCE6F1"]

[/td]

[td="bgcolor: #DCE6F1"]

[/td]

[td="bgcolor: #DCE6F1"]

[/td]

[td="bgcolor: #DCE6F1"]

[/td]

[td="bgcolor: #DCE6F1"]

[/td]

[td="bgcolor: #DCE6F1"]

[/td]

[td="bgcolor: #DCE6F1"]

[/td]

[td="bgcolor: #DCE6F1"]

[/td]

[td="bgcolor: #DCE6F1"]

[/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;771542

This 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

=INDEX($A$1:$E$1,,MAX(IF(B16=$A$2:$E$13,COLUMN($A:$E)-MIN(COLUMN($A:$E))+1)))

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.

=MAX(FREQUENCY(IF(D$15=$C$16:$C$35,ROW($16:$35)),IF(D$15<>$C$16:$C$35,ROW($16:$35))))

[TABLE="class: grid"]

Row\Col

Row 1:

Row 2:

Row 3:

Row 4:

Row 5:

Row 6:

Row 7:

Row 8:

Row 9:

Row 10:

Row 11:

Row 12:

Row 13:

Row 14:

Row 15:

Row 16:

Row 17:

Row 18:

Row 19:

Row 20:

[/TABLE]