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 Jess M. Your Own Question
Jess M.
Jess M., Computer Support Specialist
Category: Software
Satisfied Customers: 6710
Experience:  Computer Software Support specialist for more that 10 years
49766785
Type Your Software Question Here...
Jess M. is online now
A new question is answered every 9 seconds

I have a bunch of Excel worksheets. Each worksheet has 3

Customer Question

I have a bunch of Excel worksheets. Each worksheet has 3 columns Part # (UPC code actually), Description and Qty. I need to reduce the lists, on each spreadsheet to the same colum names but totaled by the Part #.
Submitted: 10 months ago.
Category: Software
Expert:  Jess M. replied 10 months ago.

Hi Rod, welcome and thank you for your question. My name is ***** ***** I am glad to assist you today.

Can you send me a sample or dummy of your Excel file so that I can directly check the structure of your data? You can create a copy of the file, remove sensitive info, and attach it here using the Add Files button.

Please let me know by replying to me here so that I can help you further.

Best regards,
Jess

Customer: replied 10 months ago.
Sure, give me a minute. I'm working on 6 files tonight and have to complete about 100 by the end of the month.
Expert:  Jess M. replied 10 months ago.

Thank you. Please take your time

Customer: replied 10 months ago.
The file is attached. What I do is delete all rows with no or 0. Then i sort by UPC and reduce the file to one row for each UPC (all quantities summed for each UPC). In the attached file, all relevant data end at row 117 or 118 (I think) .I'll need a procedure so I can do the rest myself.Thanks,
Rod
Expert:  Jess M. replied 10 months ago.

Thank you, ***** ***** me a moment to download and check your file.

Expert:  Jess M. replied 10 months ago.

Please tell me if this approach will work:

  1. We will use or add 2 columns to the right, like C and D
  2. In column C we will list ALL unique UPC. For instance, you have 10000 in the list, but only 550 are unique. So Column C will list only 500.
  3. The 4th column, D, will compute the SUM of values in column B for each specific UPC.

Will that work?

Customer: replied 10 months ago.
I think so, The final result should include UPC, Desc and total Qty for each UPC.
Expert:  Jess M. replied 10 months ago.

OK, that will be alright then. But what I was saying was that in order to use formulas to automate this, we need to utilize other columns while the formulas work on the original A and B columns.

Customer: replied 10 months ago.
I understand.
Customer: replied 10 months ago.
Do it the best way you know how - just as long as I end up with the 3 columns I need. If there are extra columns, I can delete the unnecessary ones.
Expert:  Jess M. replied 10 months ago.

Thank you. Please give me a moment to create the formulas for you so that you can test on your actual files.

Customer: replied 10 months ago.
Jess, how's it going?
Expert:  Jess M. replied 10 months ago.

I am still working on the required formula... Please give me a moment

Customer: replied 10 months ago.
Roger that.
Expert:  Jess M. replied 10 months ago.

There are a lot of data so my array calculation is taking some time to show up. Thank you for your patience.

Customer: replied 10 months ago.
Okay
Expert:  Jess M. replied 10 months ago.

I am also trying a Macro approach. I need to see which is faster

Expert:  Jess M. replied 10 months ago.

I believe I miss something. Are you saying that the final summary table will only show those rows with values?

Expert:  Jess M. replied 10 months ago.

In the sample, you have almost 11,000 rows but few hundred rows with values. So you want to only display the rows with values and ignore the others, right?

Customer: replied 10 months ago.
Customer: replied 10 months ago.
I've attached the final file for the sample. The original data had no duplicate rows - that's unusual. Usually the same UPC will start with 2, 3 or more row.
Expert:  Jess M. replied 10 months ago.

Thank you for the confirmation. Since you have a lot of data, extract the unique data will take a lot of time with array formulas and could cause Excel to freeze. What about the option to just manually extract the rows with data manually deleting those with blank rows, then work on the smaller file?

Expert:  Jess M. replied 10 months ago.

Ok, this sample file is smaller now, give me a moment to apply the formulas

Expert:  Jess M. replied 10 months ago.

Here is the file:

http://filesxpress.com/d-ac31e458

The file contains a summary table I created with formula. However, since there are no duplicates, the table is exactly the same.

Can you send me a sample or old file with few duplicates to test the formula template I created?

Customer: replied 10 months ago.
who do I use this for my other files?
Expert:  Jess M. replied 10 months ago.

You can use this as a template. With your other files, you manually extract the rows with data, copy them, and then paste them in columns A and B starting row 2 since row 1 is the header. Then the summary table will automatically populate the data based on the data you pasted.

However, that template I just sent you only has 117 rows in the formula. You just need to drag the formula down to the last row you pasted.

Customer: replied 10 months ago.
Don't you mean columns A, B and C - C is the column with the Qty.
Expert:  Jess M. replied 10 months ago.

Here is a modified version of the file, I already tested it by copying and pasting the tail end part of the data to produce duplicates, and the formulas just worked fine.

Also, I added IFERROR function to show blank cells when errors occur.

Here is the link:

http://filesxpress.com/d-a090a3dd

And oh, YES, you are correct. You need to copy and paste 3 columns A, B and C!

Customer: replied 10 months ago.
Thank you Jess. I'm very tires so may not be able to test until the morning.
Expert:  Jess M. replied 10 months ago.

You're welcome, Rod. Please take your time.

If you need further assistance with the template file I created for you, just let me know so that I can assist you further. Please bookmark or add our chat page to Favorites so that you can get back to me here in this chat easily.

I hope that helped.

Please remember to rate my service positively (3-5 stars/faces) if this helped. Tips are always highly appreciated!

If you need further assistance, please do not rate me negatively with 1 or 2 faces. Instead, please reply to me so that I can help you further.

Thank you!

Best regards,
Jess

Customer: replied 10 months ago.
Jess,I just replace Columns a,b and c with new data.Should I do anything to Columns d, e and f?How do I "run" the application?Thank you,
Rod
Expert:  Jess M. replied 10 months ago.

Rod,

If you paste new data in columns A, B and C, the summary table will automatically populate with the correct data. Please check and verify that.

But in the last template I gave you, it works only up to row 124. If the pasted data exceeds row 124, please click on the last cell of the table and drag to the last row of the pasted data to cover the actual length of your data.

If you paste only few data, it will be no problem, the cells BELOW will show blank.

Customer: replied 10 months ago.
Jess,It didn't work. See attached, renamed, file.I'm hitting the sack - hopefully it will work when I wake up.Thank you,
Rod
Expert:  Jess M. replied 10 months ago.

The formula is working, but there are few things you need to adjust when the data you paste is greater that of the existing table. You will see this easily because the table has colors. So if the data in ABC is shown past the table, then you need to do some adjustments.

Here are the adjustments you need to do:

  1. In column D, you just need to click on the last, first or any row, point the mouse pointer to the fill handle, that is the lower right corner of the "selected" cell showing a + mark when the cell is selected, then drag DOWN up to the last row of the pasted data in ABC.
  2. In column E, you need to manually change the RANGE of the lookup, since it will not automatically increase. The formula in cell E2 is =IFERROR(VLOOKUP(D2,$A$2:$C$117,2,FALSE),""). The range to manually edit INSIDE the formula is $A$2:$C$117. If the row is up to 189, the range should be $A$2:$C$189. You need to do the editing of this range in the top row, E2, then drag down to copy.
  3. In column F, the top formula in F2 is =IF([@[Unique UPC]]="","",SUMIF($D$2:$D$189,D2,$C$2:$C$117)). The ranges to change so ensure they match to the actual number of rows are inside the SUMIFS function, $D$2:$D$189 and $C$2:$C$117. The first is already OK since it is already up to row 189. But the C range should be $C$2:$C$189.

Doing all these 3 adjustments will fix the problem, Remember, if you paste fewer data, there will be no need for adjustments, only when pasting larger data past the summary table.

Here is the final file:

http://filesxpress.com/d-c5ca03e9

I hope that helped.

Please remember to rate my service positively (3-5 stars/faces) if this helped. Tips are always highly appreciated!

If you need further assistance, please do not rate me negatively with 1 or 2 faces. Instead, please reply to me so that I can help you further.

Thank you!

Best regards,
Jess

Related Software Questions