VBA code to merge the repeated cell and sum

December 15, 2010 at 15:57:43
Specs: Windows XP
I am very new to VBA coding
Can someone help me please...

I am trying to merge the repeated cells in column A and get the sum for the corresponding cells in the column B.The entire result should be displayed in sheet2

Example:
Sheet1
ColA ColB
123 10
234 10
123 15
456 5
234 20

now the result should be
Sheet2

ColA colB
123 25
234 30
456 5


Please let me know if you have any doubts in my explanation?
Please try to solve this!!

Thank you


See More: VBA code to merge the repeated cell and sum

Report •


#1
December 15, 2010 at 18:31:31
You don't need VBA.

In Sheet2 Column A use a Data...Filter...Advanced Filter...Unique Records Only to create a list of the individual numbers from Sheet1 Column A.

In Sheet2 Column B use SUMIF(range, criteria, sum_range)

Review the SUMIF function in Excel Help to see how it is used.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
December 15, 2010 at 18:52:45
Thank you for the reply..:)
But what I posted is a small part of my project...this should be executed in the middle of the main program...So it should be hard coded..
Can you please help me with that..!!

Report •

#3
December 15, 2010 at 20:56:05
Sub FilterAndSum()
'Determine length of data in Sheet1 Column A
 numRws = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
  With Sheets("Sheet2")
'Perform AdvancedFilter to Sheet2 Column A
   Sheets("Sheet1").Range("A1:A" & numRws).AdvancedFilter _
        Action:=xlFilterCopy, _
        CopyToRange:=.Range("A1"), Unique:=True
'Assumes no column label
      .Rows(1).Delete
'Count Unique Values
      numUni = .Range("A" & Rows.Count).End(xlUp).Row
'Sort Unique Values
      .Range("A1:A" & numUni).Sort Key1:=.Range("A1"), _
        Order1:=xlAscending
'Place SUMIF Formula in Column B
      .Range("B1:B" & numUni).FormulaR1C1 = _
           "=SUMIF(Sheet1!R1C1:R" & numRws & _
                 "C1,RC[-1],Sheet1!R1C2:R" & _
                  numRws & "C2)"
  End With
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

Related Solutions

#4
December 16, 2010 at 13:03:00
Thank you very much for your immediate response. Actualy it is working for example wot I have use to explain. But it is not working on my original project :(
I want to send the excel sheet wot I am working right now...
can you please give your email Id or any alternative...
Please let me know because I need to submit this by monday..:( thank you very much

Report •

#5
December 16, 2010 at 17:51:10
re: "Actualy it is working for example wot I have use to explain. "

Well, what else could I use to base the code on?

Why would you post an example that is different than what you really need?

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#6
December 16, 2010 at 22:58:45
A B C D E F G H I J
1
5 Dep sdep clas desc stat kit ckit desc2 quat quat2
7 34 32 3 abc a 2345 3456 asfd 1 10
8 23 44 3 fsdf a 3453 3456 fdhh 2 45
9 12 23 4 ghfg 1 4545 2245 hgda 1 23
10 21 34 4 zxcc 2 7676 3456 lkjjh 1 10
:
My sheet1 is something like this....Actual requirement is, The repeated numbers in (sheet1)ColumnG should be merged and corresponding columns of (sheet1)ColumnJ should be summed accordingly. And respective columns A,B,C,H should be dsplayed in sheet2. the total result should be displayed in sheet2.

Can you please modify the code accordingly

Thank you very much


Report •

#7
December 17, 2010 at 04:25:33
Click on the words How To in the following line, read the instructions, and then repost your data.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

Ask Question