Posts by cs566

• Need HELP with ARRAY Formula

Hi Glenn,

Hope you are still doing well. Praying for your Prime Minister...

I need help again. Hoping you might be able to assist.

I now need to calculate the MIN date and the formula you provide (with SUMPRODUCT) doesn't seem to work when trying to find MIN vs MAX.

On the attached:

RawData tab has new Column for A - "Calculated New Value"

This captures any value in the "New Value" column to show "eMotive" if equal to "Jennifer Reed", "App - Premium Offer", or "App - Non Premium Offer".

I now need to capture the FIRST date (or MIN date) of the "Edit Date" field if "Calculated New Value" = "eMotive".

The formula would be entered in the StatTbl tab/Column C (eMotive).

When the formula in StatTbl tab/Column C (eMotive) has:

=IF(SUMPRODUCT(MAX(--(RawData[Case Number]=[@[UniqueCase'#]])*--(RawData[Calculated New Value]="eMotive")*(RawData[Edit Date])))=0,"",SUMPRODUCT(MAX(--(RawData[Case Number]=[@[UniqueCase'#]])*--(RawData[Calculated New Value]="eMotive")*(RawData[Edit Date]))))

The eMotive date is populated.

If formula in StatTbl tab/Column C (eMotive) has:

=IF(SUMPRODUCT(MIN(--(RawData[Case Number]=[@[UniqueCase'#]])*--(RawData[Calculated New Value]="eMotive")*(RawData[Edit Date])))=0,"",SUMPRODUCT(MIN(--(RawData[Case Number]=[@[UniqueCase'#]])*--(RawData[Calculated New Value]="eMotive")*(RawData[Edit Date]))))

The eMotive date is BLANK

I have done some reading and read that the MIN and SUMPRODUCT don't always play nice. Everything I have tried does not work. Any ideas?

Take care.

see attached

Finding MIN Date.xlsx

• Need HELP with ARRAY Formula

Awesome, thanks so much. This is the first time I have used the double negative.

Take care and stay healthy!

best, cristy

• Need HELP with ARRAY Formula

Hi Glenn,

I have another question regarding the solution you provided.

In the formula

=SUMPRODUCT(MAX(--(RawData[Case Number]=[@[UniqueCase'#]])*--(RawData[New Value]="Tech Review")*(RawData[Edit Date])))

How would I revise the formula if I am looking for more than one value in the red text above. Meaning, the New Value could be "Tech Review", "Customer Related", "Not Customer Related" ?

Thanks in advance for any assistance.

Hope you and family still doing ok through this crazyness!

/cristy

• Need HELP with ARRAY Formula

Nevermind, just figured it out.

Thanks again and hope all is well with you and your family!

• Need HELP with ARRAY Formula

Good Afternoon,

Sorry for delay...

Yes, I did and it works. Thank You!

Now, when the formula finds doesn't find a match it provides a date of 1/0/1900. I want those to be blank but cannot get any IF statement to work.

Any ideas?

Thanks so much

• Need HELP with ARRAY Formula

Hi,

I am needing to find the 'MAX' date in the 'Edit Date' column when the 'New Value' column is equal to 'Tech Review'.

There are two case numbers that have two different dates for that scenario and I need to capture the latest or 'MAX' date vs the first date.

on my StatTbl tab I have the formula in column D:

{=INDEX(RawData[Edit Date],MATCH(1,("Tech Review"=RawData[New Value])*([@[UniqueCase'#]]=RawData[Case Number]),0))}

I cannot figure out how to grab the MAX of the value I am trying to populate in that cell.

Any help would be appreciated.

see attached

Finding MAX Date.xlsx

• Inserting Formulas with Macro

Wow... PERFECT!

Thank you so much!

• Inserting Formulas with Macro

Hi,

In the attached file, I need to create a macro that will do the following:

1.

Insert column between

Sales Stage (Column B) and Estimated Close Data (column C)

Name new Column 'Estimated Close Year'

enter =Year(D2)

Auto fill down (REGARDLESS OF NUMBER OF ROWS)

2.

Insert column between

Estimated Close Date (column D) and Quote (column E)

Name new column 'Qrt'

enter formula ="Q"&ROUNDUP(MONTH(D2)/3,0)

Auto fill down (REGARDLESS OF NUMBER OF ROWS)

The rows of this data will change, so the macro needs to capture ALL Rows.

Thanks in advance for any assistance.

/c

• Copy/Paste to bottom row

I have a macro that I recorded to format a dataset. Each time the dataset is generated, the bottom row of data will change.
My recorded macro has hard-coded the formula to the last row when the macro was recorded. I need help on re-writing this to capture the column from the 2nd row and down (regardless of last row number). I have tried a couple of revisions, but continue to get a debug error.

I have attached the data set and below is the Macro. The large, bold, italicized lines are the two selections that need revision.

• Time Stamp Formula help needed

You rock, once again!

Learned something new. Thanks so much!

• Time Stamp Formula help needed

Thank you. The Start Week formula is calculating properly. The End Week formula is reflecting same date/time.
Below represents column D with formula.
I am needing these to show a TIME of 0:00:00
[TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 135"]Midnight Saturday End Week[/TD]

[/tr]

[tr]

[td]

01/13/2019 0:00:00

[/td]

[/tr]

[tr]

[td]

01/13/2019 7:00:00

[/td]

[/tr]

[tr]

[td]

01/13/2019 7:00:00

[/td]

[/tr]

[tr]

[td]

01/13/2019 2:00:00

[/td]

[/tr]

[tr]

[td]

01/13/2019 7:00:00

[/td]

[/tr]

[tr]

[td]

01/13/2019 0:00:00

[/td]

[/tr]

[tr]

[td]

01/13/2019 7:00:00

[/td]

[/tr]

[tr]

[td]

01/13/2019 4:00:00

[/td]

[/tr]

[tr]

[td]

01/13/2019 0:29:00

[/td]

[/tr]

[tr]

[td]

01/13/2019 7:15:00

[/td]

[/tr]

[tr]

[td]

01/13/2019 6:30:00

[/td]

[/tr]

[/TABLE]

• Time Stamp Formula help needed

The data attached represent a users START and END Time. (A & C). These are systematic date/time stamps.

I am needing a way to calculate how to find MIDNIGHT of the SATURDAY following the Start Time (column B) and the previous MIDNIGHT of the SATURDAY of the End Time (column D).

Many of these shifts start one week and end on another. I am wanting to separate what is actually worked in each week.

Midnight Saturday (Start Week) - Start time (column E)

Midnight Saturday (End Week) + End Time (column F)

I can calculate total hours worked, but need to split these type shifts to 'bucket' hours in the respective week to another analysis.

Any help would be greatly appreciated.
Thanks

• HELP with an ARRAY formula

I am so sorry... you are correct. I didn't update the data source like I thought I had.
Everything good for now.
You rock!

It was a long day for me yesterday, so again, my apologies!
/cristy

• HELP with an ARRAY formula

this post has attached worksheet
-------------------
I am still having problems with the ARRAY formula.
It worked on the attachment I was sent on Monday, but now I have to go back and calculate previous months data using same table source and the formulas aren't working.

I am wanting to calculate the MIN and MAX weeks each job (column A) had via a WK number field (Columns F and G)
My formulas for column B (Minimum) and C(Maximum)are:

{=MIN(IF(\$J\$3:\$J\$5167=A3,IF(ISNUMBER(\$F\$3:\$F\$5167),\$F\$3:\$F\$5167)))} <-- currently in Column B
{=MAX(IF(\$J\$3:\$J\$5167=A3,IF(ISNUMBER(\$F\$3:\$F\$5167),\$F\$3:\$F\$5167)))} <-- currently in Column C

I am entering the formula with the CTRL+SHFT+ENTER to show it is an array.
Column A are unique Job numbers
Column J has repeated Job numbers
Column F and G have the week number affiliated with the Job # entry in column J

This is making me crazy Lucy....

Thanks in advance for the help!

• HELP with an ARRAY formula

I am still having problems with the ARRAY formula.
It worked on the attachment I was sent on Monday, but now I have to go back and calculate previous months data using same table source and the formulas aren't working.

I am wanting to calculate the MIN and MAX weeks each job (column A) had via a WK number field (Columns F and G)
My formulas for column B (Minimum) and C(Maximum)are:

{=MIN(IF(\$J\$3:\$J\$5167=A3,IF(ISNUMBER(\$F\$3:\$F\$5167),\$F\$3:\$F\$5167)))} <-- currently in Column B
{=MAX(IF(\$J\$3:\$J\$5167=A3,IF(ISNUMBER(\$F\$3:\$F\$5167),\$F\$3:\$F\$5167)))} <-- currently in Column C

I am entering the formula with the CTRL+SHFT+ENTER to show it is an array.
Column A are unique Job numbers
Column J has repeated Job numbers
Column F and G have the week number affiliated with the Job # entry in column J

This is making me crazy Lucy....

Thanks in advance for the help!

• HELP with an ARRAY formula

Hello,
THANK YOU, THANK YOU!
Finally was able to retrieve attachment.
I understand it now thanks to you. Much appreciated!

:flower::wink:

/cs

• HELP with an ARRAY formula

Hi,
I am not seeing your attachment.
Thanks so much for your help!

• HELP with an ARRAY formula

Hi,
I have a data sheet that I am wanting to calculate the MIN and MAX weeks each job had via a WK number field.
My formulas for column B (Minimum) is:

{=MIN(IF(\$J\$3:\$J\$5167=A3,\$F3:\$f\$5167))}

I am entering the formula with the CTRL+SHFT+ENTER to show it is an array.
Column A are the unique Job number
Column J has repeated Job numbers
Column F has the week number affiliated with the Job # entry in column J

See attached

• Sorting 2 Columns in specific Order

see above revision.
apologies on oversight