+ Reply to Thread
Results 1 to 1 of 1

A complicated Loop with lots of IF's and a method of counting similar dates...

  1. #1
    Registered User
    Join Date
    03-01-2013
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    1

    Question A complicated Loop with lots of IF's and a method of counting similar dates...

    There's a tl;dr at the bottom that might be easier to read than my paragraphs up here. Just a warning


    I've got a list of rows with the following information:

    Drafts ordered - Drafts Recieved - Forwarded the Draft

    If I've only got a date under draft ordered, and not "Forwarded the Draft", then I'd like excel to make a task in outlook.
    If I've got a date under all 3, I'd like it to use the date from "Forwarded the draft".
    I'd also like it to keep a count on the number of "drafts" that i'm still waiting to receive, and those I've forwarded. If I've forwarded 4 drafts, I'd like excel to only make 1 task for all 4 done the same day. There will be cases where it takes longer to get a draft copy, so in the same spread sheet I'll have different forwarding dates
    .
    If I can get excel to make a seperate outlook task item for nonsimilar dates. That would be awesome.

    The whole thing has been giving me a headache, and currently, I have a Do loop and a counter that goes row by row. I've made it so if cell.offset(counter+1, 0)=cell.offset(counter,0) then counter = counter + 1.

    This avoids having more than 1 task item in Outlook. Problem is, if there's more than 1 row difference between "Drafts Forwarded", it doesn't recognize any other items.
    This is a section of my code:

    Please Login or Register  to view this content.
    <use outlook code here>
    Please Login or Register  to view this content.
    <create task>
    Please Login or Register  to view this content.
    <create task>
    Please Login or Register  to view this content.
    This code only creates tasks properly for one date. If I alter or add a new Draft Forwarded date, it does not create a task for that item.

    This basically is how excel looks like:

    DC Order Date (1st row) DC Receive Date (3rd row) DC Forward Date (10th row)
    date (may or may not have) date (may or may not have) date (may or may not have)
    date (may or may not have) date (may or may not have) date (may or may not have)
    ... ... ...
    date (may or may not have) date (may or may not have) date (may or may not have)


    ....
    Can anyone help me out?

    tl;dr: For all rows in columns

    • If cell (n, 10) isn't populated, use cell (n, 1)'s date to make an outlook item.
    • If cell (n, 10) is populated, use cell (n, 10)'s date.
    • if cell (n, 10) or cell (n, 1) date = cell (n, 10) or cell (n, 1) add to count
    • Try to count number of cell (n, 10)'s or cell (n, 1)'s with similar dates, create 1 task item for all similar dates.
    • If Cell (n, 1) or Cell (n, 10) have unique dates compared to the rest, create separate task item for each unique item.
    • if Cell (n, 3) has a value and Cell (n, 10) does not, do not make a task and do not count cell (n, 1) value.
    Last edited by hellsadvocate; 05-13-2013 at 01:15 PM. Reason: Clarification

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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