complicated if statement

Thanks:  0
Likes:  0

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

2. ## Re: complicated if statement

Have you tried a Pivot Table?

3. ## Re: complicated if statement

A pivot table can do this.

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

4. ## Re: complicated if statement

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.

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. ## 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
 A3 =

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. ## 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. ## 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. ## 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. ## Re: complicated if statement

Using the Excel ODBC and an SQL statement like this:

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.

## User Tag List

#### Posting Permissions

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