#1
|
|||
|
|||
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! |
#2
|
|||
|
|||
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. Last edited by Catalin.B; 07-20-2012 at 06:05 AM. |
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 |
Macro for moving one cell down from current position | Johnny thunder | Word VBA | 3 | 04-07-2011 04:44 PM |
Problems moving Outlook Data to new computer | jeffrichard | Outlook | 2 | 01-11-2011 08:47 PM |
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 |