Contraction of rows
 

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

Contraction of rows
 

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

Contraction of rows
 

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