Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-18-2012, 12:59 PM
MattMurdock MattMurdock is offline Moving data macro problem Windows 7 64bit Moving data macro problem Office 2010 64bit
Novice
Moving data macro problem
 
Join Date: Jul 2012
Posts: 5
MattMurdock is on a distinguished road
Default Moving data macro problem

Hi guys- I was wondering if there was a way to create a macro to move data like I have in my attachment ("Before" vs "After")




I really have been struggling on this and no expert on macros so any help would be great- thank you in advance. my problem is being able to populate Item with as many months there are.

Again, any help would be very much appreciated- thanks!
Attached Files
File Type: xlsx SampleBook2.xlsx (41.0 KB, 11 views)
Reply With Quote
  #2  
Old 07-20-2012, 04:49 AM
Catalin.B Catalin.B is offline Moving data macro problem Windows Vista Moving data macro problem Office 2010 32bit
Expert
 
Join Date: May 2011
Location: Iaşi, Romānia
Posts: 386
Catalin.B is on a distinguished road
Default

Are you sure you need a macro for this? it can be done with a CSE formula like:
=SUM(IF((Before!$A$4:$A$87=After!A22)*(Before!$B$4 :$B$87=After!B22);OFFSET(Before!$C$4:$C$87;0;MATCH (C22&D22&E22;$I$2:$R$2;0)-1));0) confirmed with CTRL+SHIFT+ENTER, as in worksheet attached.
I have created a new range in yellow to simplify the formula

L.E: The sample you provided it's not very accurate, as there could be, in "After" sheet, more than 1 record on 2012 january, depending on items, type or supplier. The formula will return N/A if there is no match for those 5 criteria (year, month, item, type, suplier). It's not easy to build a macro to match all criterias. After you build the structure of data, use the formula to extract the appropriate values from "Before" sheet, then copy the results and paste them as values in the same place if you want to make data permanent.
Be careful when you enter item number, type or supplier, or even year or month, as there will be false results if you don't type names exactly as they are in Before Sheet; a better option is to use data validation list to select year, products, items and so on. You might use a single line in "After" sheet, then change those 5 criteria cells (with data validation lists), this way will work like a data base interogation.
Attached Files
File Type: xls SampleBook2-v2.xls (87.0 KB, 7 views)

Last edited by Catalin.B; 07-20-2012 at 06:05 AM.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
moving data files g48dd Outlook 2 06-17-2011 01:18 PM
Moving data macro problem Macro for moving one cell down from current position Johnny thunder Word VBA 3 04-07-2011 04:44 PM
Moving data macro problem Problems moving Outlook Data to new computer jeffrichard Outlook 2 01-11-2011 08:47 PM
Moving data macro problem moving data from single to multiple columns? mzimmers Excel 3 08-23-2010 08:20 AM
problem with moving documents between folders Trooper1 Word 0 01-21-2010 03:34 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:17 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft