Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel combine cells

Posted on 2014-01-06
6
Medium Priority
?
172 Views
Last Modified: 2014-01-07
Hello,
can you please help,
is there a vba that can find the duplicates names in Column c,
if there are, then combine them to one row, use the first Invoice #,  and add the subtotal, taxes, total.

same thing for Column L.

Number of rows is different all the time.

Please see sample attached.
Any help is appreciated.

thanks
0
Comment
Question by:W.E.B
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 

Author Comment

by:W.E.B
ID: 39760770
Sample attached.
sample.xlsx
0
 
LVL 25

Expert Comment

by:chaau
ID: 39760937
Do you insist on VBA code, or are you happy to use built-in Excel data analysing tools? If the latter, then it can be easily done. Just follow these simple steps.
1. Select first chunk of data you wish to analyse. Make sure you include the heading columns, but do not include the very first row (with 11_DAYS):
Select chunk2. Click on "Filter" button in the "Data" toolbar. You should get the screenshot like this:
Filtered data3. Click on Remove duplicates and in the popup unselect all checkboxes and select only "name"
Remove Duplicates4. That's it:
End result5. You can now click on "Filter" button again to bring the spreadsheet to the normal view.

Do the same thing with your L column
0
 
LVL 25

Expert Comment

by:chaau
ID: 39760947
BTW, the steps above can be recorded as a Macro. Here is a VBA code that does the same thing:
Sub Remove_Duplicates_for_column_C()
'
' Remove_Duplicates_for_column_C Macro
'

'
    Range("A2:G30").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$2:$G$30").RemoveDuplicates Columns:=3, Header:=xlYes
End Sub

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:W.E.B
ID: 39760971
Hello,
thanks for the help,

Beside removing the duplicates, I need the summary of the fields.
In the sample ,
I have,
example.

11/30/2012      AT3339       Name 07      523504      $1.00      $2.00      $3.00
11/30/2012      AT3339      Name 07      523505      $1.00      $2.00      $3.00
11/30/2012      AT3339       Name 07      523506      $1.00      $2.00      $3.00

I need this to become
11/30/2012      AT3339      Name 07      523504      $3.00      $6.00      $9.00

thanks
0
 
LVL 25

Accepted Solution

by:
chaau earned 1200 total points
ID: 39761030
It is still possible, however some data preparation is required.
1. Insert three empty columns after column G (they will be H, I, J)
2. In the cell H3 type this formula, and hit <Enter>:
=IF($C3=$C4,E3+H4,E3)

Open in new window

Formula3. Replicate this formula across range H3:J30
Formula Replicated4. Copy the content (use Ctrl-C) of the range H3:J30
5. Select Range E3:G30 and use Paste Values option, like this:
paste values6. Now you can delete the columns H, I, J, as they are no longer required.
Now, repeat the steps I have described in my first post to remove duplicates
0
 

Author Closing Comment

by:W.E.B
ID: 39761927
Thank you,
I build on what you posted, and created the VBA,
thanks.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question