Look at last 3 Columns in table

1. Look at last 3 Columns in table

Greetings,

I am trying to create a code to look at the last 3 Columns of a table to match it with a criteria.

Keep in mind that the Table will constantly get new columns and thus why I want it to look at last 3 column entries per Line.

I am trying to build a aging workbook for devices connected/disconnected.

So everyday we pul a report that staes if the devices is still "Online" or "Offline"

currently I am doing this with formulas and it just seems to big for the requirements.

2. Re: Look at last 3 Columns in table

Trying to rebuild this as the original has over 20 000 lines

3. Re: Look at last 3 Columns in table

I am trying to create a code to look at the last 3 Columns of a table to match it with a criteria.
Ignore your formula for now...what is it you are wanting to match...please explain in more detail...

4. Originally Posted by sintek
Ignore your formula for now...what is it you are wanting to match...please explain in more detail...
Hi Sintek,

I am trying to build logic as follow.

If Last 3 Columns(Per Line) => 2 "Offline" then it should be "Offline" otherwise "Online".

The criteria is not the difficulty but indeed to tell Excel to look at the last 3 Columns.

I just need to try and get it in such way that th "3" is dynamic.

Basically I want to use an "IF" (Last 3 Columns) Countif > 2 "Offline" then it must execute.

Hope this helps.

Trying to build a Aging Report.

5. Re: Look at last 3 Columns in table

SteynfaardtD...the logic might be easy for you to understand, but not for me...Hence explain in detail...
So with the sample file you uploaded...The last 3 columns is GV:GX
If Last 3 Columns(Per Line) => 2 "Offline" then it should be "Offline" otherwise "Online".
This to me means if the "Offline" count in last three columns' row = or > 2 then...The value of said columns must be "Offline" otherwise "Online"
This makes no sense to me...

Are you not meaning the count of Offlines from Col A to the beginning of last 3 columns?

6. Re: Look at last 3 Columns in table

Okay let me try and explain otherwise.

I will have a Table that will consists of daily entries. These Entries will be added to the end of the table.

I need the formula/Script to ALWAYS look at the last "3"(Dynamic) entries made on the list and do the calculations thereof.

So if the last 3 columns in the table indicates >1 "Offline" then my result must be Offline.

It must only Count the last 3 columns that meets the criteria (in this case "Offline") and then based on the result give me an output.
However the RANGE cannot indicate the actual Column as there will be columns added as the time passes.

So basically it must do the same as the xlUp/xlDown fuction where it start from the right of the table, and 3 columns offset to the left.
resulting in only cross referencing the LAST 3 Columns in the table to whatever the criteria might be.

but i need a way to make the range Dynamic instead of indicating the actual Range.

7. Re: Look at last 3 Columns in table

Look sorry for wasting your time...Perhaps someone else can assist...Post 6 references Tables and your sample upload is a range so even more confused...Good Luck

8. Re: Look at last 3 Columns in table

Okay sweet.

All I need is a way to replace the RANGE ("Table1[@[25/05/2021]:[27/05/2021]]") in such a way that the Range will always be the last 3 columns of a table.

So regardless of the table having 100 or 10 columns. the RANGE must always be the LAST 3 COLUMNS

9. Re: Look at last 3 Columns in table

What does your actual Table look like? The formula you've posted makes little sense in the context of the sample you posted earlier.

10. Re: Look at last 3 Columns in table

So regardless of the table having 100 or 10 columns. the RANGE must always be the LAST 3 COLUMNS
Look at attached sample of an Actual Table...This code will give you the last 3 columns...

11. Re: Look at last 3 Columns in table

Hi Sintek,

Thank you.

How can I test to see if this code can be used to determine the Range in another code.

12. Re: Look at last 3 Columns in table

It will make life so much easier if you could just explain in "layman's terms" step by step exactly what it is you are wanting to achieve...referring to sample file upload and show a expected result and how this expected result was derived...

13. Re: Look at last 3 Columns in table

As per my Code above. I need a way to get that code to utilize the last 3 Columns as "RANGE"

I do not know how to better explain that,

You Code was "Sub J3v16()"

I need the ".Range(J3v13)" to refer to the last 3 columns in a Table

I am trying to build a Aging Report for IP Connection Devices.
Daily I test the connectivity and the results are then indicated as "Online"/"Offline"

These results will be added daily to the end of "Table1" with the "Date" as header for that Column.

I need a code to take a criteria "Online"/"Offline" (Criteria in this case) and Match/Count how many times it is present over the LAST 3 Days(Columns).
The output in the sample is a msgbox but I will change the output once I am able to refrence the last 3 "Entries" to the Table but instead of an entry being Rows, it should be Columns.

Same way as above,

If I have to manually to the code it would be like this

Attachment 734505

14. Re: Look at last 3 Columns in table

I can get the last column in the row like this

But this does not allow me to use last 3 columns as a "RANGE"

15. Re: Look at last 3 Columns in table

If this doesn't give you an idea, then I give up...
Look at attached file and step through loop...

16. Re: Look at last 3 Columns in table

YESSSS. Thank you sintek. this works perfectly.

I can now proceed to build my ageing report and fit this code into it.

to be honest I am strugling to understand this code and its methods.

I now need to look a adding
Now instead of saying "I", I need to know how the code method works to be able to utilize "lc" with offset of 2 to right.

17. Re: Look at last 3 Columns in table

This is what you are trying to do...
Is however, a simpler way...

18. Re: Look at last 3 Columns in table

Originally Posted by sintek
This is what you are trying to do...
I managed to do this as the logic on your does not calculate right.
If last column is "Online" then it needs to be "Online"

I did it as follow

This however is beyond my knowledge. Still a beginner in VBA.

Originally Posted by sintek
Is however, a simpler way...

19. Re: Look at last 3 Columns in table

If it works for you then ... well done...please mark thread as solved...

20. Re: Look at last 3 Columns in table

Thanks Sintek,

I need some more help as I am struggling to change code to Loop each Column instead of each row.

Can I log new Thread and will you be able to assist

21. Re: Look at last 3 Columns in table

20 posts and I still have no clue what you are wanting to achieve...For future threads, I suggest you ... be more transparent with your requirement and actually supply a explanation of your requirement...
Good luck...

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1