Software

Software

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

Customer Question
columns Part # (UPC code...
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: 1 year ago.Category: Software
Show More
Show Less
9/16/2016
Software technician: Jess M., Computer Support Specialist replied 1 year ago
Jess M., Computer Support Specialist
Category: Software
Satisfied Customers: 7,247
Experience: Computer Software Support specialist for more that 10 years
Verified

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.

Best regards,
Jess

Customer reply replied 1 year 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.
Software technician: Jess M., Computer Support Specialist replied 1 year ago

Customer reply replied 1 year 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
Software technician: Jess M., Computer Support Specialist replied 1 year ago

Software technician: Jess M., Computer Support Specialist replied 1 year 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 reply replied 1 year ago
I think so, The final result should include UPC, Desc and total Qty for each UPC.
Software technician: Jess M., Computer Support Specialist replied 1 year 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 reply replied 1 year ago
I understand.
Customer reply replied 1 year 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.
Software technician: Jess M., Computer Support Specialist replied 1 year ago

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

Customer reply replied 1 year ago
Jess, how's it going?
Software technician: Jess M., Computer Support Specialist replied 1 year ago

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

Customer reply replied 1 year ago
Roger that.
Software technician: Jess M., Computer Support Specialist replied 1 year ago

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

Customer reply replied 1 year ago
Okay
Software technician: Jess M., Computer Support Specialist replied 1 year ago

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

Software technician: Jess M., Computer Support Specialist replied 1 year ago

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

Software technician: Jess M., Computer Support Specialist replied 1 year 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 reply replied 1 year 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.
Software technician: Jess M., Computer Support Specialist replied 1 year 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?

Software technician: Jess M., Computer Support Specialist replied 1 year ago

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

Software technician: Jess M., Computer Support Specialist replied 1 year 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 reply replied 1 year ago
who do I use this for my other files?
Software technician: Jess M., Computer Support Specialist replied 1 year 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 reply replied 1 year ago
Don't you mean columns A, B and C - C is the column with the Qty.
Software technician: Jess M., Computer Support Specialist replied 1 year 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.

http://filesxpress.com/d-a090a3dd

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

Customer reply replied 1 year ago
Thank you Jess. I'm very tires so may not be able to test until the morning.
Software technician: Jess M., Computer Support Specialist replied 1 year ago

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!

Thank you!

Best regards,
Jess

Customer reply replied 1 year 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
Software technician: Jess M., Computer Support Specialist replied 1 year 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 reply replied 1 year 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
Software technician: Jess M., Computer Support Specialist replied 1 year 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!

Thank you!

Best regards,
Jess

Jess M., Computer Support Specialist
Category: Software
Satisfied Customers: 7,247
7,247 Satisfied Customers
Experience: Computer Software Support specialist for more that 10 years

Jess M. is online now

A new question is answered every 9 seconds

• Ask an ExpertExperts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
• Get a Professional AnswerVia email, text message, or notification as you wait on our site. Ask follow up questions if you need to.

Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.

## What Customers are Saying:

My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed.

One Happy CustomerNew York

Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help.

Mary C.Freshfield, Liverpool, UK

This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!!

AlexLos Angeles, CA

Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult.

GPHesperia, CA

I couldn't be more satisfied! This is the site I will always come to when I need a second opinion.

JustinKernersville, NC

Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around.

EstherWoodstock, NY

Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know.

RobinElkton, Maryland

< Previous | Next >

## Meet the Experts:

Jess M.

Computer Support Specialist

7,247 satisfied customers

Computer Software Support specialist for more that 10 years

Chris L.

Support Specialist

505 satisfied customers

Certified Software expert with over 10 years experience.

IT Miro

Computer Scientist

497 satisfied customers

Bachelor's Degree in Information Technology, Microsoft Certified Professional

Jins M. N.

Computer Hardware Engineer

463 satisfied customers

12+ years experience. Expert in installing and problem fixing of softwares.

Kamil Anwar

Software Specialist

456 satisfied customers

8+ Years of Experience. / CCNA (S), CCNA (W), CCNA (RS), MCTS, MBCs.

Steve Herrod

Computer Support Specialist

220 satisfied customers

Familiar with a wide variety of software and experienced in user training/support

Byron

Computer Support Specialist

164 satisfied customers

12 years in operations at a large law firm supporting both retail and specialty applications.

< Previous | Next >

Related Software Questions
My name shows up when I use craigslist. I don't want my name
My name shows up when I use craigslist. I don't want my name showing up when I respond to someone … read more
Jess M.
Computer Support Specialist
7,247 satisfied customers
I am able to log on to my computer but then the next screen
I am able to log on to my computer but then the next screen is just black there is no tool bar or my e mail to check and if I hit control,alt, delete it … read more
Sheri
Bachelor\u0027s Degree
102 satisfied customers
I have software for my embroidery business that is called
I have software for my embroidery business that is called Forte, they are no longer in business. I was moving to a larger building when the movers broke the dongle (key) for this program and it cannot… read more
Kris R
IT Manager
Computing
2,648 satisfied customers
I need to change the backround in all my programs from black
I need to change the backround in all my programs from black to white … read more
The-PC-Guy
Bachelor\u0027s Degree
629 satisfied customers
Have LR Classic CC, I have mobile on my iPad with all of my
Have LR Classic CC, I have mobile on my iPad with all of my files/images. On my laptop it is different, many files with missing pictures, multiples of files. Contacted LR support, their recommendation… read more
Pete
Engineer
Bachelor\u0027s Degree
1,124 satisfied customers
My aol account said that the version that i have is no
my aol account said that the version that i have is no longer available. i tried to update to aol gold, but was unable to accomplish the upgrade… read more
Jins M. N.
Computer Hardware Engineer
Diplom
463 satisfied customers
My Facebook and I messenger has been hacked what do I need
My Facebook and I messenger has been hacked what do I need to do to fix this? … read more
Kris R
IT Manager
Computing
2,648 satisfied customers
Office ribbon question: in some installations, some of the
office ribbon question: in some installations, some of the items do not show up, although when trying to customize the ribbon they appear listed as available … read more
RussellA
Solutions Architect
Bachelor\u0027s Degree
123 satisfied customers
I keep getting NGStudio has stopped working while trying to
I keep getting NGStudio has stopped working while trying to edit my movie in pinnicle17 … read more
Kris R
IT Manager
Computing
2,648 satisfied customers
I bought a new laptop and installed Photoshop CS-4 on it.
I bought a new laptop and installed Photoshop CS-4 on it. The menus are all micro sized and are super difficult to navigate. Can you help me? … read more
Bryan
IT Consultant
8,751 satisfied customers
I suddenly cant open email attachments on AOL. Earlier
Earlier yesterday I believe. First experience of the problem was yesterday and again today … read more
Kamil Anwar
Software Specialist
Bachelor's Degree
456 satisfied customers
I am running Mac High Sierra 10,13,2. I have Bento version
I am running Mac High Sierra 10,13,2. I have Bento version 4.1.2. I am not able to get my Bento data printed. Even though I have selected/highlighted all the data the only thing that shows up when I t… read more
Kris R
IT Manager
Computing
2,648 satisfied customers
How can I save a shared google album as a jpeg or pdf? The
How can I save a shared google album as a jpeg or pdf ? The preference would be to safe each picture as separate jpeg pictures.… read more
RussellA
Solutions Architect
Bachelor\u0027s Degree
123 satisfied customers
How do I recover W10 deleted files? Dell Optiplex PC. W10.
Few minutes. Looked for file history and Carbonite backup. … read more
caraleneK
Service Manager
1,676 satisfied customers
Ineed recovery disk for my Dell Inspiron 11 3000 series.
Ineed recovery disk for my Dell Inspiron 11 3000 series. Service Tag No. DLFJ3C2. eXPRESS SERVICE CODE*********** … read more
caraleneK
Service Manager
1,676 satisfied customers
I'm trying to buy a movie and it is giving me the run around
I'm trying to buy a movie and it is giving me the run around can I have a phone # to talk to a human? … read more
Kris R
IT Manager
Computing
2,648 satisfied customers
Josh
It Support Specialist
Associate Degree
5,140 satisfied customers
Help to install installation of Mcafee Livesafe. McAfee All.
Help to install installation of Mcafee Livesafe. JA: Which McAfee product do you have? Customer: McAfee All JA: What have you tried so far with your McAfee software? Customer: Need to enter product ke… read more
RussellA
Solutions Architect
Bachelor\u0027s Degree
123 satisfied customers
Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Disclaimer: Information in questions, answers, and other posts on this site ("Posts") comes from individual users, not JustAnswer; JustAnswer is not responsible for Posts. Posts are for general information, are not intended to substitute for informed professional advice (medical, legal, veterinary, financial, etc.), or to establish a professional-client relationship. The site and services are provided "as is" with no warranty or representations by JustAnswer regarding the qualifications of Experts. To see what credentials have been verified by a third-party service, please click on the "Verified" symbol in some Experts' profiles. JustAnswer is not intended or designed for EMERGENCY questions which should be directed immediately by telephone or in-person to qualified professionals.

Show MoreShow Less