Sum all data and copy

**excelsupport** · 03-06-2009, 01:55 AM

Dear all,

I am new to VBA, working on a worksheet and stuck to resolve following query.

I have a worksheet with important data that we update daily and just want to add a column at the end of the sheet.

Requirement:

Have Raw data in other sheet and want VBA to first find and sum similar variables (unique code is defined for each item). Copy them to another place or if they can directly copied to the origional sheet in the required column.

Example:
Raw data
Data is in different columns (e.g. code = Column D, Description = column F and qty = column Q)
Now what i want is it find and sum all the items with same code and make a new sheet like below:

Code Item description Qty

and with the help of vlookup (or any other way through VBA), it copies the Qty data comparing code to the actual sheet in the column added at the last of the sheet. One thing i am concerned with i dont want to increase the size of my origional file so planning to practise it with VBA rather than vlookup just to reduce the size and routine efforts.

I hope i have communicated my question properly and is easily understandable, if you feel something confusing please ask. I am looking for its solution i am not a business professional but have to do it and it seems difficult for me. Thanks

**JBeaucaire** · 03-06-2009, 02:44 AM

Someone may offer a macro approach, but I would construct this with a single helper "key" column added to my Data sheet to give all the items a unique numerical flag starting at 1 and rising.

With that, a simple INDEX/MATCH (speedy fast formula) can spot the first entry of each index key, insert the matching ITEM # in the row that matches that index. Then the Desc is another simple INDEX/MATCH on that ITEM # and the QTY TOTAL is standard SUMIF formula.

All of these functions are very low overhead, so your SUMMARY would stay updated all the time with no macros to run. You could even add a little "flag" at the top of the SUMMARY to remind you when the SUMMARY table needs to be lengthened. (included in the sample sheet attached)

A sample is attached. It's pretty low maintenance.

**JBeaucaire** · 03-06-2009, 03:52 AM

Cross post without a proper cross link:
http://www.mrexcel.com/forum/showthread.php?t=375772

**royUK** · 03-06-2009, 04:07 AM

Excelsupport:

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!