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 Ingo U Your Own Question
Ingo U
Ingo U, Software Engineer
Category: Programming
Satisfied Customers: 755
Experience:  Over 25 years experience in software development. Expert in Microsoft .Net, C#, C++, VB, SQL
44076068
Type Your Programming Question Here...
Ingo U is online now
A new question is answered every 9 seconds

1. I need to write a formula to consolidate/group a

Customer Question

1. I need to write a formula to consolidate/group a spreadsheet by state
2. I need a formula to take two table and populate them into one
Submitted: 1 year ago.
Category: Programming
Expert:  Jason Jones replied 1 year ago.

Hello,

I am the person that will be helping you today. May I take a look at the document in question along with a more elaborate description of what needs to be done?

Please, be forewarned that the site is not secure:
- Click the following link: http://www.wikisend.com
- Upload the file to that website
- Once it is uploaded, the resulting page will display a "File ID" number.
- Please, give me that "File ID Number"
I will be standing by, looking forward to your response.

Thank you,
Jason

Customer: replied 1 year ago.
Here is the file id number: 666136
Expert:  Jason Jones replied 1 year ago.

I have the document. Please, walk me through all what you need for me to do on this document.

Customer: replied 1 year ago.
there are two tabs in the document. In the first tab i need to have a formula to consolidate/group the orders and demand in table by state in table 2 (1 row per state). In the second tab I need a formula so that i can use the columns in tables 1&2 to populate table 3
Expert:  Jason Jones replied 1 year ago.

I am sorry to say, but I don't understand what you want me to do in the document. Please, give me examples.

Customer: replied 1 year ago.
i dont need you to do anything with this document i just need to know what formulas i would have to use.
Expert:  Jason Jones replied 1 year ago.

Okay. Please, give me examples of what you need the "Formulas" to do. Which values will they take into consideration and what will be the end result.

Customer: replied 1 year ago.
for the first tab i need the formula to consolidate/group the orders and demand by state, the second tab i need the formula to populate what is in the two tables into a third. i think the first one would proabbly be a sumif function of some kind and the second one might be a vlookup or index match
Expert:  Jason Jones replied 1 year ago.

Please, understand that this is the first time I have seen your layout of the document.

When you say that you "....need the formula to consolidate/group the orders and demand by state...", what does that mean?

When you say that in the second tab you "need the formula to populate what is in the two tables into a third...", what does that mean?

I'm sorry, but I don't understand.

Customer: replied 1 year ago.
I am asking how you would go about using the tools available in excel to group the orders and by state, and how you would go about consolidating the two tables to create a third.
Expert:  Jason Jones replied 1 year ago.

I wish I could answer your question, but I don't understand what the question is.

For this reason, I am opting out of the question and allowing another expert the chance to help.

The next expert will see all that is on this page, so there will be no need to repeat anything.

When another expert picks this up, you will be notified by email.

Thank you for your patience.

- Jason

Expert:  Ingo U replied 1 year ago.

Hi,

I made an attempt to provide the first answer in this version of your document:

http://dl.dropbox.com/u/7895834/QUESTIONS-V1.xlsx?dl=0

You'll see that I used the SUMIF formula to compute the numbers. To generate the list of unique states, I used a one-time advanced filter/copy step, which doesn't show up as a formula in the second table, but I can show you how it was done.

Before continuing, I just wanted to run it by you to see if we're on the right track and to confirm you're still interested in the 2nd part.

Regards,

-Ingo U

Customer: replied 1 year ago.
Hi! That's exactly what i needed for the first question and would like to see how you would solve the second.
Expert:  Ingo U replied 1 year ago.

Glad to hear it!

Here's my approach to the 2nd part - updated workbook:

http://dl.dropbox.com/u/7895834/QUESTIONS-V2.xlsx?dl=0

A few notes:

- the key function you were looking for is VLOOKUP

- It happens to work out nicely because the list of customer IDs in table 1 and 2 are the same. So I just copied that column verbatim to table 3, and then filled in the VLOOKUPs to pull the corresponding cells from Table1 & 2. If you were to have different lists of customer IDs in table 1 & 2, things would get more complicated.

- your table 3 currently has an extra "header" row that's considered part of the data rows, so if you sort the table, that row gets moved around. YOu probably want to delete that row or make it the official header row of the table instead of the generic "ColumnX" names.

Hope that helps!

Regards,

-Ingo U

Expert:  Ingo U replied 1 year ago.

Hi,
just following up, were you able to use my solution?
Please feel free to post any additional questions, comments or concerns you may have regarding this issue.

If you're not yet satisfied with the result, please let me know what is missing.
I will do my best to make it right for you.

Otherwise, please don't forget to rate my service - I don't get paid for my work until your rating is received.

Regards,
Ingo U

Expert:  Ingo U replied 11 months ago.

Hi,
just following up, were you able to use my solution?
Please feel free to post any additional questions, comments or concerns you may have regarding this issue.

If you're not yet satisfied with the result, please let me know what is missing.
I will do my best to make it right for you.

Otherwise, please don't forget to rate my service - I don't get paid for my work until your rating is received.

Regards,
Ingo U

Expert:  Ingo U replied 11 months ago.

Hi,
just following up, were you able to use my solution?
Please feel free to post any additional questions, comments or concerns you may have regarding this issue.

If you're not yet satisfied with the result, please let me know what is missing.
I will do my best to make it right for you.

Otherwise, please don't forget to rate my service - I don't get paid for my work until your rating is received.

Regards,
Ingo U

Related Programming Questions