Hi Tom,

I'm not sure I have fully understood the IF-sentence.

The reason I am asking is that it does not work correct I get syntax errors
if I just copy it into
the Excel file as you described.

Can you explain to me how it works?

$A$2 what does it mean, exactly?
"Delete": This function does not work, do you know the explanation?

When I separate the IF-sentence into smaller pieces: COUNTIF and SUMIF they
work without
syntax errors but I still have to make some small changes before they do:

=countif($A$2:A2)=1 become =(COUNTIF($A$2;A2)=1 (the result I get is a true
or false)


=Sumif($A:$A,A2,$B:$B) become =SUMIF(A:A;A2;B:B) (I get a number as result)


"Delete": Excel does not know this "command" at all ????

Hopefully you are able to help.


JIP
"Tom Ogilvy" skrev i en meddelelse
...
Unless this is something you have to do repetetively, it would be easier
just to do it manually

Assume you data starts in A2 and Column G is open. in G2 put in the
formula

=if(countif($A$2:A2)=1,Sumif($A:$A,A2,$B:$B),"Dele te")

Drag fill that down the column (double click the lower right corner of the
selection highlight on the little square as one way).

Now select Column G and do Edit=Copy, then Edit=PasteSpecial and select
Values. This replaces the formulas with the values they display.

Now select row1 and do Data=Filter=AutoFilter
in the column G dropdown select Delete
Select rows 2:4000 (use the name box as an easy way - type in 2:4000<cr)
and do Edit=Delete
now do Data=Filter=Autofilter to remove the autofilter. You should now
have your result data.

If you need a macro, turn on the macro recorder while you do this
manually,
then clean it up to generalize it.

--
Regards,
Tom Ogilvy


"jip" wrote in message
...
Platform: Windows 2000/Excel 2000


Is there anybody out here who is able to help me solving following
Excel-case:

I have an Excel-price file consists of approximately 4000 rows and 6
columns,

First column consist of consecutive numbers each usual
attached/connected
with a price (the price can be the same but not necessarily).

I need to make a macro/VB-code which can do following job:

If a number occur more than once (as for instance 101075 or 101078,
shown
in
the example down under) the macro/VB-code shall replace all
repetition-rows
with only one row, but same time add. the prices belonging to the same
number..hope this explanation makes it clear????


Example

Before change:

Number Price
101074 2000
101075 5412
101075 243
101075 8492
101076 105531
101077 244
101078 5456
101078 81512

After the change has been implemented:

Number Price
101074 2000
101075 14147
101076 105531
101077 244
101078 86968

I am looking forward that you are able to help.

JIP