Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Merging worksheet data
Hi there - another new user here. This has been bugging me all day so any help or suggestions would be really gratefully appreciated. I have 27 worksheets each containing purchasing data from different businesses. There are 6 columns in each sheet showing a purchase route, each row represents a supplier and each cell represents a value (so A = "Direct", 1="AAA Ltd" and A1 = £200) Although the Supplier names are all similar they are not all the same (and there is a variance on the number for each sheet (some have 300, some have up to 400)) meaning that vlookup is not really working. What I want to see is the spend of each company by supplier, also showing the purchase routes so I can compare each company's total spend by purchase route/supplier or by supplier total. I have tried using pivot tables, but with no success (as the data is across worksheets) and the less said about my access attempt the better. So, how do I get all the data to show neatly (and visibly) on one sheet and still be able to manipulate it to extract the information I need? Many thanks L -- leecou ------------------------------------------------------------------------ leecou's Profile: http://www.excelforum.com/member.php...o&userid=27791 View this thread: http://www.excelforum.com/showthread...hreadid=473118 |
#2
|
|||
|
|||
The first thing I would do is get all that data into one worksheet.
Ron de Bruin has some code that might make it easier: http://www.rondebruin.nl/copy2.htm If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Then maybe the pivottable would look nicer. leecou wrote: Hi there - another new user here. This has been bugging me all day so any help or suggestions would be really gratefully appreciated. I have 27 worksheets each containing purchasing data from different businesses. There are 6 columns in each sheet showing a purchase route, each row represents a supplier and each cell represents a value (so A = "Direct", 1="AAA Ltd" and A1 = £200) Although the Supplier names are all similar they are not all the same (and there is a variance on the number for each sheet (some have 300, some have up to 400)) meaning that vlookup is not really working. What I want to see is the spend of each company by supplier, also showing the purchase routes so I can compare each company's total spend by purchase route/supplier or by supplier total. I have tried using pivot tables, but with no success (as the data is across worksheets) and the less said about my access attempt the better. So, how do I get all the data to show neatly (and visibly) on one sheet and still be able to manipulate it to extract the information I need? Many thanks L -- leecou ------------------------------------------------------------------------ leecou's Profile: http://www.excelforum.com/member.php...o&userid=27791 View this thread: http://www.excelforum.com/showthread...hreadid=473118 -- Dave Peterson |
#3
|
|||
|
|||
Thanks Dave, This is my first go with macro's and it looks a bit daunting. I'll post back in 24hrs and let you know how i get on. L -- leecou ------------------------------------------------------------------------ leecou's Profile: http://www.excelforum.com/member.php...o&userid=27791 View this thread: http://www.excelforum.com/showthread...hreadid=473118 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data From One Worksheet To Be Used In A Cell In A Different Worksheet | Excel Worksheet Functions | |||
SELECT large amount of data in a worksheet | Excel Discussion (Misc queries) | |||
Excel 2002 chart does not update when worksheet data changes | Charts and Charting in Excel | |||
extract data from worksheet | Excel Worksheet Functions | |||
How do I collect data from an interactive web worksheet and impor. | Excel Discussion (Misc queries) |