Sorting and adding repeating information

**Grumplestiltskin** · 05-14-2010, 03:23 PM

Hi everyone,
I'm pretty new to Excel and I have a problem which is quite difficult to me and I'm hoping there is a kind soul to help me out.
I have attached a spreadsheet which contains details about pick rates for some manufacturing machines. The first two sheets contain details of the pick rates from two seperate machines (Line1 & Line2). Each sheet has 3 columns, the component, how many times it has picked and also how many times it has placed the component. I have extracted this data but unfortunately it doesn't add them all up. If you view it you will see that some components are listed maybe 10 times or so with seperate pick and place data.

What I am needing is that the third "master" sheet can list all the components only once in the 1st column. Then in the next columns it will total up all the pick and place data to show if it used on Line1 or Line2.

Hope this all makes sense. Any assistance would be greatly appreciated.

Regards
Jason

**NBVC** · 05-14-2010, 03:25 PM

Welcome to the forum,

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

**day92** · 05-14-2010, 03:45 PM

Grump,

I updated your spreadsheet. The the formula and drag it down.

**Grumplestiltskin** · 05-14-2010, 03:52 PM

Hmmm, I still can't get it to work.

**Grumplestiltskin** · 05-14-2010, 04:14 PM

Hi again...
I'm still having problems...if I try to copy the formula down then it stops returning values at at column 23. I know this is because some of the part numbers have a letter in as well. I have tried to format them as text, general, numbers etc but it still wont link

Also could you tell me how you listed all the part numbers in the first column so they only appeared once. Regards Jason

**martindwilson** · 05-14-2010, 07:07 PM

why not just in b3
=SUMIF(Line1!A:A,A3,Line1!b:b) ?
lther are a couple of ways of getting that list
easiest is to copy both ranges one below the other to a new sheet in column A
then you can remove duplicates and sort
some different ways shown here
http://www.mrexcel.com/tip138.shtml