Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: lists and more lists

  1. #1
    Board Regular
    Join Date
    May 2002
    Posts
    158
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ok folks is this possible?
    column A is a cust number, column B is a name and column C is a dollar amount. I need to find the duplicate cust number from A and move the dollar amount to D next to the existing number in C and possibly delete the A, B, and C from the duplicate.

    Thanks

  2. #2
    Board Regular s-o-s's Avatar
    Join Date
    Apr 2002
    Location
    Kissimmee, Florida
    Posts
    384
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-05-03 15:21, mrxwantobe wrote:
    ok folks is this possible?
    column A is a cust number, column B is a name and column C is a dollar amount. I need to find the duplicate cust number from A and move the dollar amount to D next to the existing number in C and possibly delete the A, B, and C from the duplicate.

    Thanks
    If you have one match this will find a match and put the value in D of the other cell.

    =OFFSET(C4,MATCH(A4,A5:A$65536,0),0)

    My guess is that this is not the end of the problem, so here are some questions:

    Will the be multiple duplicates?
    Are you adding items to the list as you are going on (This will make life a little easier)

    There's another question in there that I can't get to at the moment just waiting to pop out.

    Once we've got that then you can look at the macro code to delete data etc. Which whould be the next bit.

    Hope This Helps.
    Sean.
    Digest of Homes

    WinXP, XL XP

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,993
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-03 15:21, mrxwantobe wrote:
    ok folks is this possible?
    column A is a cust number, column B is a name and column C is a dollar amount. I need to find the duplicate cust number from A and move the dollar amount to D next to the existing number in C and possibly delete the A, B, and C from the duplicate.

    Thanks
    Lets say that A1:C6 houses the following sample:

    {"c#","cname","amount";
    1,"a",20;
    1,"a",10;
    2,"b",6;
    3,"c",7;
    2,"b",12}

    This is important: Insert a new column after column B.

    In (new) C1 enter: Amount

    In C2 enter and copy down:

    =SUMIF($A$2:$A$6,A2,$D$2:$D$6)

    Now, the data area will look like this:

    {"c#","cname","amount","amount";
    1,"a",30,20;
    1,"a",30,10;
    2,"b",18,6;
    3,"c",7,7;
    2,"b",18,12}

    This is important: Format the labels in row 1 very distinctly, e.g., in bold and italic.

    Now activate A2.
    Activate Data|Filter|Advanced Filter.
    Check Copy to another location.
    Enter $A$1:$C$6 for List range.
    Enter $H$1 for Copy to.
    Check Unique records only
    Leave everything else alone.
    Activate OK.

    You might want to delete columns A to D.

    Aladin

  4. #4
    Board Regular
    Join Date
    May 2002
    Posts
    158
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    it is a monthly sales report. i need to take next months report and move the sales figures from "C" up to "D" with the corosponding cust number. i thought i could paste the new report in the existing one and by finding the duplicates move the new months figures up to "D"

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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