Counting Days from Last Column that matches Criteria

[SteynfaardtD]

Greetings
Hope all is well,

I was hoping someone could assist me with this.

Sintek made sure that my next post includes as much details as possible with the best possible explanation.

Hope the below makes sense.

Attached is "Aging Sheet"
There is currently 7 Columns & 10 Rows in the Table ("Table1") from A1:G10 (Columns will be added daily and thus why a code is used to get last row in table
A1:G1 is Table1 Headers ( A1 = Reference for A:A, B:G Dates )
My reference Column is A
Each Row from 2 to 10 consist of text values "Online","Offline" and "-"

There are 2 Columns from I:H wich is used for "Status"(I1) & "Days"(J1)
Below is the code Sintek helped me with to determine the Status based on different Criterias

Please Login or Register  to view this content.

Now I now need to get the "Days" column to be populated as per attached.

I have achieved this with normal formulas but is not consistent.

Please Login or Register  to view this content.

I am trying to count the days the "Status" has been the same.

Each Row in (J2 to LastRow) count the columns from right in Table1 and use that columns header in a Networkdays Formula to determine what the Duration of "Status" is.

You will notice that the Formula does not look at the current Day due to:
me not being able to iterate what's in my head.

If G2 = I2 and F2 = I2 then
count from G2 to where *column <> I2(Backwards / Right to Left) -- Basically if the column does not match the current Status then it means it only needs to count to that column as the status for that Day(Column) is different from the "Status" in I2.

If G2 = I2 and F2 <> I2 Then
Count from F2 to where *column <> I2(Backwards / Right to Left) -- Basiccaly if the Last Column does Match I2 but Last Colum -1 Does not match I2 then count from the cell which does not match(F2 in this case)

Kind Regards,

thank you for the support.

[sintek]

Below is the code Sintek helped me with to determine the Status based on different Criterias

Nope...Not my code...Your code you strung together with snippets of misunderstood requirements...
I would like to see this one pan out so that I can finally see what your initial requirement was that I was trying to understand in the other post...

[SteynfaardtD]

Nope...Not my code...Your code you strung together with snippets of misunderstood requirements...
I would like to see this one pan out so that I can finally see what your initial requirement was that I was trying to understand in the other post...

Lol seeing that you are here, why not assist?

My explanation still not correct?

[SteynfaardtD]

Hi I have managed it this far but for some reason I am not able to run the loop in another loop

the "n" loop works fine as it reference each cell from last column to column 2

But when the "n" loop is finished it does not run the "i" loop again?

any suggestions?

Please Login or Register  to view this content.

[sintek]

88 views...this Post, 386 the other one...Duplicate posts by the way...Still wanting to achieve the initial request...

And yet, no solutions...You keep on trying to explain what you are doing with formulas and loops and yet...All that has been asked is...Explain in step by step procedure what it is you want to achieve...

Such as ... Look at sample file...In sample file I want to look at Columns x and y and z and ascertain this...
Then I want to look at Columns a and b and c and want to ascertain this... etc etc etc...

Put in your results and explain how you achieved these results...This way... someone might give you a simplistic solution...Which could have been offered in your other thread here in Post 4 or 5 already...

[SteynfaardtD]

Hi Sintek

I've said multiple times that I need a "AGEING" report.
Simply a report to tell me how many days a "Device" is in the state?
I mean really if it is so difficult to understand then I don't know.

My samples already have the expected results in them with formulas showing how it gets to that answer?

I NEED A REPORT TO COUNT FROM RIGHT TO LEFT IN A TABLE TAHT MATCHES A CRITERIA?

If that is not self explanatory then I don't know.
I was hoping that members on the Forum would understand the meaning of an "Ageing Report"?

Last try at this and if no help then I honestly will give up.

in the attached Sample("Ageing Sheet v4")

I want to use each of the below per line
Column L as my reference
Column J to B as my Range
Row 1 as my Table Headers

Now I need a VBA Script to

Match Column L with Column J in Row 2
If Column L and J is the same then loop to next Column(Which is Column I)
This "Loop" must run until it either finds a Column that does not match or ends loop.

Take row 2 as reference,

Row 2, Column L Indicates "Offline" -- Reference, Row 2
Row 2, Column J = "Offline"
Row 2, Column I = "Offline"
Row 2, Column H = "Online"

Now I need a Script that will Use Column L and match/count it within the rage when Column x, Row 2 does not equal Column L, Row 2

In this case Row 2 Column H does not match Row 2 Column L. And thus resulting in 2 "Offline"

Now knowing the Row number and Column Number where the last Successful match was, the code can grab the "Header" of that Column and will return ("01-Jun-21"). as that was the last column that matched the "Reference"

Take Row 3.

Row 3, Column L Indicates "Online" -- Reference, Row 3
Row 3, Column J = "Online"
Row 3, Column I = "Online"
Row 3, Column H = "Online"
Row 3, Column G = "Online"
Row 3, Column F = "Online"
Row 3, Column E = "Online"
Row 3, Column D = "Offline"

This the script needs to return value ("28-May-21") *** that was the last Column in Row 3 that matches the Column L, Row 3.

Then with Column M, Row x ("Days") I need to use the Value returned from Above ("28-May-21") and do a NETWORKDAYS formula in order to determine the days Offline From TODAY().

Now if this is not self explanatory then I don't know.

[sintek]

Was that so difficult...Well done in explaining...
by the by, your other uploaded files had incorrect results...
Here is a easy to understand loop...by the way...M4 answer is incorrect in your sample...
I am also sure that this code should not reference Col L & M but the offset columns of the end of table...

Please Login or Register  to view this content.

So, now, if you can just explain the results you wanted in Col L as easy as you explained the above...then we just might easily be able to incorporate that requirement into the above code...Which would solve your initial requirement in your first thread...

[SteynfaardtD]

Hi Sintek,

Thank you.

The reason for M4 seems incorrect, is because of the previous logic which needs to be :

Row 4, Column L Indicates "Online" -- Reference, Row 4
Row 4, Column J = "Offline"
Row 4, Column I = "Online"
Row 4, Column H = "Online"
Row 4, Column G = "Online"
Row 4, Column F = "Online"
Row 4, Column E = "Online"
Row 4, Column D = "Online"

The reason why I am ignoring the last Column if "Offline" is because I will only report it as "Offline" if the last 2 Columns of the Table (In this case Column I:J) is Offline.

So as per the explanation above.

I need to have Column L ( Which you are correct, this will be an offset range from last Column in Table1 ) to do the following

Based on the previous post I need to only look at the Last 3 Columns of the Table 1 and Count the times that a given Criteria is Met.

Column J - Column H ( Last 3 Columns in Table 1 )

Please Login or Register  to view this content.

Then the following needs to happen.

Please Login or Register  to view this content.

If this will cause any issues can we not then do the calculations for Column N in sample with the range only from B:I if the Last Column (J) = "Offline"

This will then basically exclude the last entry (Column J) from the calculations.

then with the code

Please Login or Register  to view this content.

If the Last Column (Column J) does not equal Status(Column L) then it should count that Column J with the others that does match.

So if
Column L = "Online" and Column J = "Offline" then
--- It must count the Offline as Online.
--- The Above Code + 1 When Last Column(Column J) does not equal to Status(Column L) ?

This should only occur in cases where the Last Column(Column J) is "Offline" but the Status(Column L) is "Online"

Hope this explanations is understood?

[sintek]

k, so help me understand...
According to this...

Please Login or Register  to view this content.

If the last column is "Online" then the status will always be "Online"
If it is not then count the "Offline" in last 3 Col ... if the count is > 1 then status will be "Offline"
If not then count the "-" in last 3 Col... If count = 3 then status = "No data"

See if this works?

[SteynfaardtD]

Was that so difficult...Well done in explaining...
by the by, your other uploaded files had incorrect results...
Here is a easy to understand loop...by the way...M4 answer is incorrect in your sample...
I am also sure that this code should not reference Col L & M but the offset columns of the end of table...

Please Login or Register  to view this content.

So, now, if you can just explain the results you wanted in Col L as easy as you explained the above...then we just might easily be able to incorporate that requirement into the above code...Which would solve your initial requirement in your first thread...

Hi Sintek,

I am now running into a error with the code you provided.

I added 1600+ rows and results in
Attachment 734981

Could this be of the way the rows are added to the Table?

I basically copied all data in my live Sheet (A2:A1874) and pasted as values in Table1 A1

[SteynfaardtD]

k, so help me understand...
According to this...

Please Login or Register  to view this content.

If the last column is "Online" then the status will always be "Online"
If it is not then count the "Offline" in last 3 Col ... if the count is > 1 then status will be "Offline"
If not then count the "-" in last 3 Col... If count = 3 then status = "No data"

See if this works?

YOU ARE A BEAST.!!

This works on the Sample.

How ever if I add more rows to Table it results in the same error as per Post #10
Also Noted that the days does not actually grab the "Days" from header, If intended then i apologize for the wrong information.

The "Days" (Column M) is actually a count instead of a NETWORKDAYS Function.

With your code it needs to Grab the Header of X Column that does match and use that to determine the days with NETWORKDAYS. (Start Date, End Date)

to make an example.

If you change B1 from "25-May-21" to "25-Apr-21" the result in Colum M should be 28 Days?

Networkdays(Date(2021,04,25{B1}),Today())

Hope this makes sense. Loadshedding just kicked in and my Pc off so will try do better examples when back on.

[sintek]

Attachment not valid...So cannot test...
I am assuming the Table does not start in row 1

[SteynfaardtD]

Attachment not valid...So cannot test...
I am assuming the Table does not start in row 1

Hi Sintek,

Same Table you shared "Copy of Aging Sheet v4"

I added the raw data as values into the Sheet A2 and tested script again and no lock?

[sintek]

I added the raw data as values into the Sheet A2 and tested script again and no lock?

I basically copied all data in my live Sheet (A2:A1874) and pasted as values in Table1 A1

Like I said, cannot test as I do not have this file you are referring too...

[SteynfaardtD]

Please see attached my Raw data type

[sintek]

Just can't win with you...

What has this file got to do with anything???

[SteynfaardtD]

Never mind, I managed to copy that "Raw Data" into the "Copy of Aging Sheet v4".

It is working now with all lines. Please can we just have look at the code to do the "Date" calculation based on the Header Date?

So take the attached

Row 2 , Column I = "Online"
Row 2 , Column J = "Offline"
Row 2 , Column K = "Offline"

Now the current code works perfectly in the sense of a count?

However if I change the Header (Row 1, Column J) to "02-May-2021" then the code needs to look at the actual last successful match( Which is J2 ) and use the Header(Date.value) and do NETWORKDAYS Function to actually determine the "Days"

Sample result is in Column Q, Row 2.

so the "Count"(2) that currently shows in "Days"(Column N,Row 2) needs to be used to grab the Header of the last found match(Column J, Row 2) and the run a Formula to determine the Actual Days from TODAY()

[sintek]

For real?

The Sheet you just uploaded has 1 column only????
Copying that one column into the ageing sheet does what exactly...Nothing to all the other columns that are referenced by the code...so obviously the code won't work..

I am outta here...perhaps someone else will be kind enough to offer their time to solve your issue...Good luck

Please Note…
Quoted From Rick Rothstein

One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data). To sum up... we only know what you tell us, nothing more. Also, uploading an actual file (see yellow banner near the top of this forum's window) showing your data as it exists and on another sheet showing how you want it to look after being processed would help immensely.

[SteynfaardtD]

The Sheet you just uploaded has 1 column only????
Copying that one column into the ageing sheet does what exactly...Nothing to all the other columns that are referenced by the code...so obviously the code won't work..

I am outta here...perhaps someone else will be kind enough to offer their time to solve your issue...Good luck

Thanks for the help thus far.

Will wait and see if someone else can assist.

to Explain better is nearly impossible as this is a new Build.

but never the less thanks for all you assistance thus far, cheers.

[sintek]

Put yourself in my position...From the get go with your other thread which is a duplicate of this thread I have been telling you to be more descriptive...
It's like Rick says...We are not MIND READERS...

Follow your instructions - paste that column into the ageing sheet you supplied and upload it here...and you will see it makes no sense...

[SteynfaardtD]

Put yourself in my position...From the get go with your other thread which is a duplicate of this thread I have been telling you to be more descriptive...
It's like Rick says...We are not MIND READERS...

Follow your instructions - paste that column into the ageing sheet you supplied and upload it here...and you will see it makes no sense...

Hi Sintek,

i understand the frustration, but also understand mine as I do not know how you want things explained?

Please see post #17 where I attached the file that we have been working on with the "Raw Data" in it.

I noticed that if the Columns B:Last Column is Blank then it results in error.

[sintek]

Please see post #17 where I attached the file that we have been working on with the "Raw Data" in it.

That is what I have been asking for...
Instead you upload a raw data file in Post 15 with 1 Col of Site Id's and say that you copy that col into the ageing data...Doing that only has site id's and all the other columns are blank...Not so...

Now I ask you...How on earth am I supposed to test anything or even reference your error without the correct data setup???

And also...In future...Please do not edit previous posts details after responses...Otherwise nothing thereafter makes sense...

Last try...Now that I have all the information in your file...

[SteynfaardtD]

Hi Sintek,

Not sure if i did something wrong but the results in "Days" are returning as 4000 + days or below "-1200".

Please see attached.

[SteynfaardtD]

Hi Sintek,

Not sure if i did something wrong but the results in "Days" are returning as 4000 + days or below "-1200".

Please see attached.

Hi Sintek,

Went and played with it, But is there a way to fix the Date format as it now only seems to work on,
"mm/dd/yyyy" and I cant seem to change the Format of the cells to match "dd-mmm-yyyy"

[SteynfaardtD]

Hi Sintek,

Went and played with it, But is there a way to fix the Date format as it now only seems to work on,
"mm/dd/yyyy" and I cant seem to change the Format of the cells to match "dd-mmm-yyyy"

Hi Sintek,

I managed thank you.

I changed this

Please Login or Register  to view this content.

To This

Please Login or Register  to view this content.

[sintek]

Glad you got it sorted...thank you for added rep +