How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask ATLPROG Your Own Question
ATLPROG, Computer Software Engineer
Category: Programming
Satisfied Customers: 7677
Experience:  MS in IT.Several years of programming experience in Java C++ C C# Python VB Javascript HTML
Type Your Programming Question Here...
ATLPROG is online now
A new question is answered every 9 seconds

I have three worksheets with multiple columns of data, each

Customer Question

I have three worksheets with multiple columns of data, each worksheet has the same columns, but different data in it. All data in these worksheets is in tables. I want to have another worksheet that will consolidate the data if it has the same data in the second column. Sheet 1, 2, 3 each have a list of farms for a customer, that list has a column that tells me what primary crop is planted on it, and what secondary crop is planted, and then another column for each telling me how may acres there is of each. I want to consolidate the list into a new worksheet and list all the farms of all the customers if the primary crop is beans and then have another 8 or so columns to the right where I can enter the plant date, variety, conditions, etc. Then I want another worksheet to do the same for corn, another for wheat, etc.. I want each list to automatically update when the data on the original three sheets is changed. Then I want to be able to format each new sheet so it can print on its own on one page.
Submitted: 1 year ago.
Category: Programming
Expert:  Zabo04 replied 1 year ago.
I have a very high-end machine, a quad core i7 with hyper threading and 12GB of RAM, two RAID arrays (1TB and 4TB). This workbook would crash my machine on calculations. Excel is not the correct program. You need a database, like Access to store the data and then produce reports. I absolutely know that's not what you want to hear, and I don't expect you to pay me for this, unless you want me to make the Access database, but that is the answer. The reason is Excel can do that work in a VBA macro or an Array Formula. Everytime you enter a cell it would make an array of all the data, sort and parse the data as told, and recalculate every cell. Every cell would need to refer to the array. So if you have 100 entries, with 4 columns, that's 4000 pieces of information, copied once for each farm, so say 20 farms. You now have 80000 pieces of information for get the unique farms. Now you have to match crops... So what's important to understand is the limited scale of Excel solutions based on its primary purpose, accounting and automate fiscal calculation, versus your problem which is multidimensional. So each time you add a factor in Excel, it scales dimensionally, or in other terms you have a 6-dimensional problem that requires a different approach, like entity-relationship sorting, matching and grouping, which is what R, SQL, Ruby or Python do very well. SQL is the correct choice for manipulated information, so that's why I would recommend Access. I mean there is always MS-SQL with a C# ***** or MySQL, but Access you probably have and does not require an Admin to maintain.