Comparing Cells in Excel 2

[franku]

Is there a way to compare and merge info from two spreadsheets? I have a 2003 parts list with prices and descriptions. I now have a 2004 parts list with additional parts and new prices. I need to insert the new prices opposite the correct part number on the old spreadsheet. Any Ideas?

 

[klmorgan]

You could link the sheets so that the 2003 data is in a worksheet in the 2004 book (or cut and paste it in )then do a vlookup using the stock number from the new list to the same number in the old data.

The data from 2003 would have to be sorted in stock number order.


Regards

Keith

 

[SkipVought]

franku,

Here's another approch that I really like. It uses MS Query and your 2003 & 2004 data as Tables in a Database.

1) Both Tables must be in ONE workbook

2) Each Table must be on a separate sheet (Rename the Sheet Tabs 2003 & 2004)

3) on a blank sheet, Data/Get External Data/New Database Query/Select Excel Files - then The Workbook with the Tables - select one table, [Next],[Next],[Next], Select Option Button to EDIT the Query, [Finish]

4) In the MS Query Grid, Add the other Table, Join the Tables on the Key Fields OR go the the SQL Icon and write a UNION query, Drag Fields into the Display Grid, and Finally File/Return Results to Excel.

VOLA! You have the joined data in one sheet!



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[jdhilljr]

a1 part number
b1 =VLOOKUP(A1,2003,2,0)
c1 =VLOOKUP(A1,2004,2,0)
2003 and 2004 are data ranges for the 2 sheets

You don't have to worry about sorting with the false statement on the end.

Jim

 

[KenWright]

Noooooooo - tell me it isn't so!!!!!! Skip has suggested something other than our beloved Pivot table, Sob Sob!!! :-< What has PT done to upset you Skip???

Insert a single extra column in your two sheets and in the new column in the 2003 data put 2003 in every cell in the new column, and 2004 in every cell in the new column in the other sheet (ie the 2004 data).

Copy all 3 columns from the 2004 data directly under the 2003 data, and assuming your data headers are PartNum, Price, Year:-

Data / Pivot table and chart report / Next / Next / Finish

Drag PartNum to the left where it says ROW fields
Drag Year to the top where it says COLUMN fields
Drag Price to the middle where it says DATA

Done

Regards
Ken..................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

Oops - nearly forgot - Right Click table, choose table options and deselect 'Grand Totals for Rows' which have no meaning here.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[SkipVought]

Hey, Ken!

I'd just like to give some Equal Time to some of the OTHER Tools in the ol' box.

I've just been havin' a BALL SQLin'. (PS: Would you care to hear the sequel to the SQL? {Don't shoot! I know it's bad} )

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[KenWright]

rotflmao

>>I'd just like to give some Equal Time to some of the OTHER Tools in the ol' box

They don't deserve it - Long live the Power to Pivot!!!!!!!



----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[KenWright]

It's also a neat way to check the difference between two sets of data, ie Price Hikes. In the source data for the example given, put -1 in a blank cell, copy it, select all the 2003 prices and do Edit / Paste Special / Multiply. Now carry on as before but don't lose the Grand Totals for Rows, and you now have all the differences in the Totals column.

I use this a lot to check for deltas between Proposal items in the bids we put out. Makes it real easy to see when engineers have increased/decreased their estimated hours/spend etc.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[SkipVought]

So...

you want to get the Last Word? to balance things out.

Would that be the......

DRUM ROLLLLLLLLLLLLL

[red]Equal Sequal???[/red]

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[franku]

Thanks guys. But before I go running in different directions, I should point out that there are part numbers in the 2003 list that are not in the 2004 one and vice versa. So its not just sorting. So should I try VLOOKUP, MS QUERY or PIVOT?????

 

[KenWright]

That fact alone would send me in the Pivot direction, but to be honest, it is literally a 2 minute (literally) thing to take the data you have, format it as I suggested by inserting the extra column, dropping one under the other and then pivot it, so you haven't exactly lost much if you try it and don't like it (But you will).

Any problems just shout and we can walk you through step by step.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[franku]

Thanks Ken. That worked a treat. I had to halve the spreadsheet but that's not a problem.

Problem is what do I do with the other fields associated with the part number e.g. description,type code so they stay with the part numbers in the new table.

I belong to the old 1-2-3 clan so this stuff is new to me

Thanks again

 

[SkipVought]

For each Part Number you can do a Lookup to return a value from the associated column...
[tt]
=Vlookup(PartNumRef,LookupRange,RelativeColOfValue,False)
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[KenWright]

You should also be able to bring them with you in the Pivot table. Just make sure you included the whole range when you created the Pivot table, and then do as you have already done, but now also drag the other fields one by one BETWEEN the ROW field and the DATA field. You will initially end up with a horrible looking mess with Totals all over the place that are meaningless, but just right click on each of the areas in the ROW fields marked Totals, choose field settings and then where it says Subtotals, choose NONE.

Eg (Excuse the artistry):-

Sum of price   Year
PartNum        2003  2004
-------------------------
a        |     ww    ww
b        |     xx    xx
c        |     yy    yy
d        |     zz    zz

Table as it stands should look like this. Now drag one of your description fields to between the PartNum field and the Data field, ie where the | is, and it will then look like this

Sum of price      Year
PartNum    Desc   2003  2004
-------------------------
a        | abc |  ww    ww
a total  |     |  ww    ww
b        | def |  xx    xx
b total  |     |  xx    xx
c        | ghi |  yy    yy
c total  |     |  yy    yy
d        | jkl |  zz    zz
d total  |     |  zz    zz

Those totals are meaningless, so just right click on for example where it says 'a total', choose field settings and then where it says Subtotals choose none. Your data should now look like this:-

Sum of price      Year
PartNum    Desc   2003  2004
-------------------------
a        | abc |  ww    ww
b        | def |  xx    xx
c        | ghi |  yy    yy
d        | jkl |  zz    zz

Now just repeat for each description field you want to bring over.

Regards
Ken....................

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------