+ Reply to Thread
Results 1 to 21 of 21

Look at last 3 Columns in table

  1. #1
    Forum Contributor
    Join Date
    06-25-2018
    Location
    South-Africa Pretoria
    MS-Off Ver
    2016
    Posts
    143

    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.

    Please Login or Register  to view this content.
    Last edited by SteynfaardtD; 05-27-2021 at 04:15 AM.

  2. #2
    Forum Contributor
    Join Date
    06-25-2018
    Location
    South-Africa Pretoria
    MS-Off Ver
    2016
    Posts
    143

    Post Re: Look at last 3 Columns in table

    Please see Current sample attached.

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

  3. #3
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,138

    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...
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  4. #4
    Forum Contributor
    Join Date
    06-25-2018
    Location
    South-Africa Pretoria
    MS-Off Ver
    2016
    Posts
    143
    Quote Originally Posted by sintek View Post
    Ignore your formula for now...what is it you are wanting to match...please explain in more detail...
    Hi Sintek,

    Thanks for your reply.

    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. #5
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,138

    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. #6
    Forum Contributor
    Join Date
    06-25-2018
    Location
    South-Africa Pretoria
    MS-Off Ver
    2016
    Posts
    143

    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.

    Please Login or Register  to view this content.
    but i need a way to make the range Dynamic instead of indicating the actual Range.

  7. #7
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,138

    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. #8
    Forum Contributor
    Join Date
    06-25-2018
    Location
    South-Africa Pretoria
    MS-Off Ver
    2016
    Posts
    143

    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. #9
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    2016 primarily
    Posts
    7,313

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

  10. #10
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,138

    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...
    Please Login or Register  to view this content.
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    06-25-2018
    Location
    South-Africa Pretoria
    MS-Off Ver
    2016
    Posts
    143

    Re: Look at last 3 Columns in table

    Hi Sintek,

    Thank you.

    please advise what the Debug.Print is?

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

    Please Login or Register  to view this content.

  12. #12
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,138

    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. #13
    Forum Contributor
    Join Date
    06-25-2018
    Location
    South-Africa Pretoria
    MS-Off Ver
    2016
    Posts
    143

    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

    Please Login or Register  to view this content.
    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

    Please Login or Register  to view this content.
    Attachment 734505

  14. #14
    Forum Contributor
    Join Date
    06-25-2018
    Location
    South-Africa Pretoria
    MS-Off Ver
    2016
    Posts
    143

    Re: Look at last 3 Columns in table

    I can get the last column in the row like this

    Please Login or Register  to view this content.
    But this does not allow me to use last 3 columns as a "RANGE"

  15. #15
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,138

    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...
    Please Login or Register  to view this content.
    Attached Files Attached Files

  16. #16
    Forum Contributor
    Join Date
    06-25-2018
    Location
    South-Africa Pretoria
    MS-Off Ver
    2016
    Posts
    143

    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
    Please Login or Register  to view this content.
    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.
    Attached Files Attached Files

  17. #17
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,138

    Re: Look at last 3 Columns in table

    This is what you are trying to do...
    Please Login or Register  to view this content.
    Is however, a simpler way...
    Please Login or Register  to view this content.
    Last edited by sintek; 05-29-2021 at 03:29 AM.

  18. #18
    Forum Contributor
    Join Date
    06-25-2018
    Location
    South-Africa Pretoria
    MS-Off Ver
    2016
    Posts
    143

    Re: Look at last 3 Columns in table

    Quote Originally Posted by sintek View Post
    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"

    Please Login or Register  to view this content.
    I did it as follow

    Please Login or Register  to view this content.
    This however is beyond my knowledge. Still a beginner in VBA.

    Quote Originally Posted by sintek View Post
    Is however, a simpler way...

    Please Login or Register  to view this content.

  19. #19
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,138

    Re: Look at last 3 Columns in table

    If it works for you then ... well done...please mark thread as solved...
    Last edited by sintek; 05-30-2021 at 10:21 AM.

  20. #20
    Forum Contributor
    Join Date
    06-25-2018
    Location
    South-Africa Pretoria
    MS-Off Ver
    2016
    Posts
    143

    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. #21
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    11,138

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 5
    Last Post: 06-12-2020, 01:55 PM
  2. Replies: 4
    Last Post: 05-24-2020, 12:26 AM
  3. [SOLVED] Filling Listbox with more than 10 columns with array made from table with hidden columns
    By hemantparmar in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-16-2019, 03:53 AM
  4. Replies: 4
    Last Post: 03-03-2019, 02:24 PM
  5. [SOLVED] Copy across 9 columns of source data into 9 columns of an output table
    By nunez100 in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-29-2019, 10:19 AM
  6. Group in a Pivot table but keep several text data columns as columns..
    By NunesJunior in forum Excel Charting & Pivots
    Replies: 8
    Last Post: 06-22-2017, 04:45 PM
  7. VBA code to hide filtered columns (simple table 5 rows / 6 columns wide)
    By e2010 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-18-2014, 02:41 PM

Bookmarks

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