+ Reply to Thread
Results 1 to 3 of 3

More issues with finding the correct entry

  1. #1
    Registered User
    Join Date
    12-24-2003
    Posts
    19

    Exclamation More issues with finding the correct entry

    Got my last problem figured out, thanks all, now i realize there is another issue.

    as stated in a previous post, say i have a list of a lot of different purchase order numbers.

    now say i may have 6 lines with the same purchase order.

    now say each purchase order has 3 columns next to it. Sometimes there is a number in A, Sometimes B, and sometimes C. sometimes that number is Negative. C is the best choice (most accurate) B is my second pref, and A last choice.

    now, when i import this list, i can make the macro to auto sort, no prob. I can also get it to find duplicates.

    But to get it to run through the duplicates, and do something like =if(c1=0,"repeat for b, then a" else return value in c) obviously not with that exact text, but my mind is fried.

    ID A B C
    xxxyyyaaa 121.3 00 00
    xxxyyybbb 000 22 00
    xxxyyyaaa -121.3 00 000
    xxxyyyccc 000 10 0
    xxxyyyaaa 120.5

    is a rough picture of what im looking at, out of that i would want to see

    xxxyyyaaa 120.5
    xxxyyybbb 22
    xxxyyyyccc 10

    im going to work on this at home, but im a little lost on where to start on this one, i know its going to be a macro that runs a string of if then statements, but how to make that cycle only through matching ID's, then repeat for the next matching id, in a specified range (i can do that manually) yeah i dont know.

    Any help would be great.

    Thanks

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Quote Originally Posted by Neflheim
    Got my last problem figured out, thanks all, now i realize there is another issue.

    as stated in a previous post, say i have a list of a lot of different purchase order numbers.

    now say i may have 6 lines with the same purchase order.

    now say each purchase order has 3 columns next to it. Sometimes there is a number in A, Sometimes B, and sometimes C. sometimes that number is Negative. C is the best choice (most accurate) B is my second pref, and A last choice.

    now, when i import this list, i can make the macro to auto sort, no prob. I can also get it to find duplicates.

    But to get it to run through the duplicates, and do something like =if(c1=0,"repeat for b, then a" else return value in c) obviously not with that exact text, but my mind is fried.

    ID A B C
    xxxyyyaaa 121.3 00 00
    xxxyyybbb 000 22 00
    xxxyyyaaa -121.3 00 000
    xxxyyyccc 000 10 0
    xxxyyyaaa 120.5

    is a rough picture of what im looking at, out of that i would want to see

    xxxyyyaaa 120.5
    xxxyyybbb 22
    xxxyyyyccc 10

    im going to work on this at home, but im a little lost on where to start on this one, i know its going to be a macro that runs a string of if then statements, but how to make that cycle only through matching ID's, then repeat for the next matching id, in a specified range (i can do that manually) yeah i dont know.

    Any help would be great.

    Thanks
    Hi,

    from your description, why not simply add all A B and C's for matching ID's ?

    The number appears to be from an invoice line, with Amount, refunds, paid etc.

    If this is true then the balance remaining is the sum of all figures.

    ---
    Last edited by Bryan Hessey; 01-18-2007 at 05:48 PM.
    Si fractum non sit, noli id reficere.

  3. #3
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,967

    Response

    Try this

    Sub Test()
    For N = 2 To Cells(65536, 1).End(xlUp).Row
    If Application.CountIf(Range(Cells(N, 1), Cells(Cells(65536, 1).End(xlUp).Row, 1)), Cells(N, 1)) = 1 Then 'Finds last rows only
    For M = 4 To 2 Step -1
    If Cells(N, M) <> 0 Then
    Cells(65536, 6).End(xlUp).Offset(1, 0) = Cells(N, 1)
    Cells(65536, 6).End(xlUp).Offset(0, 1) = Cells(N, M)
    Exit For
    End If
    Next M
    End If
    Next N
    End Sub

    It assumes that cols 6 and 7 are free to put in the preferred data
    Martin

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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