complicated if statement

Curious

Board Regular
Joined
Aug 22, 2003
Messages
77
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. (y) (y) (y) (y)
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Curious said:
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. (y) (y) (y) (y)

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
 
Upvote 0
Pivot Table,
Book1
ABCD
3SumofCostNumber
4Product12GrandTotal
5CG00004527562252981
6CG0000573419733492
7CG00006246039195522
8CG000069266016864346
9GrandTotal13438290316341
Sheet2
 
Upvote 0
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!
 
Upvote 0
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
 
Upvote 0
The following is what I am looking for:



<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0">
<tr>
<td width="67" height="21" valign="bottom"><font face="Tahoma" size="2">Before</font></td>
<td width="70" height="21" align="right" valign="bottom"><font face="Tahoma" size="2"> </font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2"> </font></td>
</tr>
<tr>
<td width="67" height="21" align="right" valign="bottom"><font face="Tahoma" size="2"> </font></td>
<td width="70" height="21" align="right" valign="bottom"><font face="Tahoma" size="2"> </font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2"> </font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" width="67" height="21" align="left" valign="bottom"><u><font face="Tahoma" size="2">Policy</u></font></td>
<td bgcolor="#FFFFFF" width="70" height="21" align="right" valign="bottom"><u><font face="Tahoma" size="2">Premium</u></font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">Audit</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" width="67" height="21" align="left" valign="bottom"><font face="Tahoma" size="2">CG000001</font></td>
<td bgcolor="#FFFFFF" width="70" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">$44,392.00</font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">1</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" width="67" height="21" align="left" valign="bottom"><font face="Tahoma" size="2">CG000001</font></td>
<td bgcolor="#FFFFFF" width="70" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">$3,249.00</font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">2</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" width="67" height="21" align="left" valign="bottom"><font face="Tahoma" size="2">CG000063</font></td>
<td bgcolor="#FFFFFF" width="70" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">$3,067.00</font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">1</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" width="67" height="21" align="left" valign="bottom"><font face="Tahoma" size="2">CG000063</font></td>
<td bgcolor="#FFFFFF" width="70" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">$156.00</font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">2</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" width="67" height="21" align="left" valign="bottom"><font face="Tahoma" size="2">CG000065</font></td>
<td bgcolor="#FFFFFF" width="70" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">$4,328.00</font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">1</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" width="67" height="21" align="left" valign="bottom"><font face="Tahoma" size="2">CG000076</font></td>
<td bgcolor="#FFFFFF" width="70" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">$3,125.00</font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">1</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" width="67" height="21" align="left" valign="bottom"><font face="Tahoma" size="2">CG000015</font></td>
<td bgcolor="#FFFFFF" width="70" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">$20,969.00</font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">1</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" width="67" height="21" align="left" valign="bottom"><font face="Tahoma" size="2">CG000015</font></td>
<td bgcolor="#FFFFFF" width="70" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">$1,475.00</font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">2</font></td>
</tr>
<tr>
<td bgcolor="#FFFF00" width="67" height="21" align="left" valign="bottom"><font face="Tahoma" size="2">CG000056</font></td>
<td bgcolor="#FFFF00" width="70" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">$2,406.00</font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">1</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" width="67" height="21" align="left" valign="bottom"><font face="Tahoma" size="2">CG000056</font></td>
<td bgcolor="#FFFFFF" width="70" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">$866.00</font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">2</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" width="67" height="21" align="left" valign="bottom"><font face="Tahoma" size="2">CG000056</font></td>
<td bgcolor="#FFFFFF" width="70" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">$115.00</font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">2</font></td>
</tr>
<tr>
<td width="67" height="21" align="right" valign="bottom"><font face="Tahoma" size="2"> </font></td>
<td width="70" height="21" align="right" valign="bottom"><font face="Tahoma" size="2"> </font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2"> </font></td>
</tr>
<tr>
<td width="67" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">After</font></td>
<td width="70" height="21" align="right" valign="bottom"><font face="Tahoma" size="2"> </font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2"> </font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" width="67" height="21" align="left" valign="bottom"><u><font face="Tahoma" size="2">Policy</u></font></td>
<td bgcolor="#FFFFFF" width="70" height="21" align="right" valign="bottom"><u><font face="Tahoma" size="2">Premium</u></font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">Audit</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" width="67" height="21" align="left" valign="bottom"><font face="Tahoma" size="2">CG000001</font></td>
<td bgcolor="#FFFFFF" width="70" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">$44,392.00</font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">1</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" width="67" height="21" align="left" valign="bottom"><font face="Tahoma" size="2">CG000001</font></td>
<td bgcolor="#FFFFFF" width="70" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">$3,249.00</font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">2</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" width="67" height="21" align="left" valign="bottom"><font face="Tahoma" size="2">CG000063</font></td>
<td bgcolor="#FFFFFF" width="70" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">$3,067.00</font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">1</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" width="67" height="21" align="left" valign="bottom"><font face="Tahoma" size="2">CG000063</font></td>
<td bgcolor="#FFFFFF" width="70" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">$156.00</font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">2</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" width="67" height="21" align="left" valign="bottom"><font face="Tahoma" size="2">CG000065</font></td>
<td bgcolor="#FFFFFF" width="70" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">$4,328.00</font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">1</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" width="67" height="21" align="left" valign="bottom"><font face="Tahoma" size="2">CG000076</font></td>
<td bgcolor="#FFFFFF" width="70" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">$3,125.00</font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">1</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" width="67" height="21" align="left" valign="bottom"><font face="Tahoma" size="2">CG000015</font></td>
<td bgcolor="#FFFFFF" width="70" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">$20,969.00</font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">1</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" width="67" height="21" align="left" valign="bottom"><font face="Tahoma" size="2">CG000015</font></td>
<td bgcolor="#FFFFFF" width="70" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">$1,475.00</font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">2</font></td>
</tr>
<tr>
<td bgcolor="#FFFF00" width="67" height="21" align="left" valign="bottom"><font face="Tahoma" size="2">CG000056</font></td>
<td bgcolor="#FFFF00" width="70" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">$2,406.00</font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">1</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" width="67" height="21" align="left" valign="bottom"><font face="Tahoma" size="2">CG000056</font></td>
<td bgcolor="#FFFFFF" width="70" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">$981.00</font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2">2</font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" width="67" height="21" align="left" valign="bottom"><font face="Tahoma" size="2"> </font></td>
<td bgcolor="#FFFFFF" width="70" height="21" align="right" valign="bottom"><font face="Tahoma" size="2"> </font></td>
<td width="36" height="21" align="right" valign="bottom"><font face="Tahoma" size="2"> </font></td>
</tr>
</table>
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top