complicated if statement

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: complicated if statement

  1. #1
    Board Regular
    Join Date
    Aug 2003
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default complicated if statement

     
    I have thousands of row in a sheet that look like the following:


    Column A Column B Column C

    CG000045 $2,756.00 1
    CG000045 $225.00 2
    CG000062 $4,603.00 1
    CG000062 $188.00 2
    CG000062 $731.00 2
    CG000057 $3,419.00 1
    CG000057 $73.00 2
    CG000069 $2,660.00 1
    CG000069 $1,496.00 2
    CG000069 $41.00 2
    CG000069 $149.00 2


    What do I need to do to get the following result:

    CG000045 $2,756.00 1
    CG000045 $225.00 2
    CG000062 $4,603.00 1
    CG000062 $919 2
    CG000057 $3,419.00 1
    CG000057 $73.00 2
    CG000069 $2,660.00 1
    CG000069 $1786 2

    In other words if column C is "2" and column A is the same add the value in column C together such thatI will only have one row with column C is 1 and one row with column C is 2 with column A being the same number.

    I hope I have made sense. and some one can help me.

    Thank you in advance.

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: complicated if statement

    Have you tried a Pivot Table?

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    2,314
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: complicated if statement

    A pivot table can do this.

    Put column A & B in Row and Sum of Col. C in Data.

  4. #4
    Board Regular eliW's Avatar
    Join Date
    Mar 2002
    Posts
    1,919
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: complicated if statement

    Quote Originally Posted by Curious
    I have thousands of row in a sheet that look like the following:

    .........
    In other words if column C is "2" and column A is the same add the value in column C together such thatI will only have one row with column C is 1 and one row with column C is 2 with column A being the same number.
    I hope I have made sense. and some one can help me.

    Thank you in advance.
    Hi

    Your question is not clear enough, I think that in last sentences there is some typo error.

    Any way I've noticed that records with C=1 are unique in your example. Is this always so?

    If the answer is no then would you sum them also?

    Your question is a classic issue for consolidation or pivot tables,

    Eli

  5. #5
    Board Regular Brian from Maui's Avatar
    Join Date
    Feb 2002
    Posts
    8,445
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: complicated if statement

    Pivot Table,

    ******** ******************** ************************************************************************>
    Microsoft Excel - Book1___Running: xl2002 XP : OS = Windows XP
    File Edit View Insert Options Tools Data Window Help About
    =

    A
    B
    C
    D
    3
    Sum of CostNumber
    4
    Product12Grand Total
    5
    CG00004527562252981
    6
    CG0000573419733492
    7
    CG00006246039195522
    8
    CG000069266016864346
    9
    Grand Total13438290316341
    Sheet2

    [HtmlMaker light Ver1.10] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

  6. #6
    Board Regular
    Join Date
    May 2003
    Posts
    260
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: complicated if statement

    You can probably use a pivot table.

    But, if you are like me, however, I hate PT's.

    This is quite a longwinded solution, I'm afraid
    Insert a column to the left of a and insert the formula
    =b2&"_"&d2

    Fill this down for the length of the data.

    Insert a column between to the left of A, and insert the formula

    =countif(b2:$B$2,2) in cell a2

    and fill down.

    In column f, insert the formula

    =sumif(b:b,b2,d:d)

    and fill down, (and replace with values)


    Copy column f and paste it OVER column d as values

    Autofilter column a on all cells which aren't 1

    Delete those rows (which dont have 1 as there value) , take off the autofilter and delete columns a and b

    et voila. That should do it

    Thats how I'd do it, anyway, by the looks of things, theres probably a much quicker way!

  7. #7
    Board Regular
    Join Date
    Aug 2003
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: complicated if statement

    Thanks for the info. I tried it and I got the following:
    1-CG000726 -1429 1
    261 1
    10189 1
    1-CG000726 Total 3
    1-CG000727 3997 1
    1-CG000727 Total 1
    1-CG000730 72 1
    3997 1
    1-CG000730 Total 2
    This is not what I am looking for. I have tried to download the colo HTML maker so I can display my sheet but I must be doing something wrong as I get the zip file but I am not sure what to do next. If I could get instructions how to download and use the HTML maker I would appriciate it and maybe then I can show you what I am really asking.

    Thanks

  8. #8
    Board Regular
    Join Date
    Aug 2003
    Posts
    77
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: complicated if statement

    The following is what I am looking for:
















































































































































    Before
    Policy Premium Audit
    CG000001 $44,392.00 1
    CG000001 $3,249.00 2
    CG000063 $3,067.00 1
    CG000063 $156.00 2
    CG000065 $4,328.00 1
    CG000076 $3,125.00 1
    CG000015 $20,969.00 1
    CG000015 $1,475.00 2
    CG000056 $2,406.00 1
    CG000056 $866.00 2
    CG000056 $115.00 2
    After
    Policy Premium Audit
    CG000001 $44,392.00 1
    CG000001 $3,249.00 2
    CG000063 $3,067.00 1
    CG000063 $156.00 2
    CG000065 $4,328.00 1
    CG000076 $3,125.00 1
    CG000015 $20,969.00 1
    CG000015 $1,475.00 2
    CG000056 $2,406.00 1
    CG000056 $981.00 2

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: complicated if statement

      
    Using the Excel ODBC and an SQL statement like this:

    SELECT `Sheet1$`.Policy, Sum(`Sheet1$`.Premium) AS 'Sum of Premium', `Sheet1$`.Audit
    FROM `C:\Book3`.`Sheet1$` `Sheet1$`
    GROUP BY `Sheet1$`.Policy, `Sheet1$`.Audit

    its possible to do what you want... where 'C:\Book3' is the name and location of the file, and Sheet1 the sheet where the data is located.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

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
  •  

 

 
DMCA.com