Comparing 2 Columns

tstoneh

Board Regular
Joined
Feb 2, 2004
Messages
126
Is there a way to compare two columns with the same information in different spreadsheets and then put a formula in the column if they agree -- not just a true of false?
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
yup, whatcha trying to compare, care to post what you have?
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
download and install Colo's HTML Maker and post with that.

if you can't, try and explain it the very best you can in as much detail as you can.
 

tstoneh

Board Regular
Joined
Feb 2, 2004
Messages
126

ADVERTISEMENT

I am trying to compare two spreadsheet columns with part numbers and unit costs in them. I have two problems.

Problem 1 - One of the part numbers is does not contain 0’s before the main number such as 00012466 vs. 12466 and I am trying to match those two numbers with a v-lookup function and it doesn’t seem to be working because it is not matching many of the numbers.

Problem 2 - If the v-lookup does happen to find the number then I want to take a unit cost number from the first spreadsheet which is a pricing file with unit prices and put this unit price into the second spreadsheet. The second SS uses a formula to compute a unit price based on total sales divided by units sold for individual parts and compare and compare them. I am basically trying to compare a static “pricing file” with unit costs in it to a actual revenue and units cost spreadsheet.

SS 1 columns in the pricing file is

1) Part Number
2) 2003 unit price
3) 2004 unit price

SS2 has the following columns

1) part number
2) part name
3) data description from pivot table
A) Gross Sales Amount
B) Gross Qty Sold
4) Total Sales and total quantities compiled from pivot table
5) Calculated Unit Cost in using data from 4 above.

Hopefully this gives you sufficient info. Thanks for the help.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
well to address your first problem:

i don't know why you are getting varied results, i would first suspect your formula, then your list. even if you hand input the leading zero's, vlookup will still bring back the first (or closest, depending on your formula) numerical - if that is the reference - match. like so...
Book4
ABCD
1handinput
212466500012466
300012466105
4customformat
Sheet1


as for your second problem, posting with Colo's HTML Maker would be much easier to understand. but something like...

=VLOOKUP(A2,'[SS1.xls]Sheet1'!A2:C100,3,0)

the 3 will bring back your 2004 unit price, assuming the data is on sheet1, of SS1, in range A2:C100 (assuming row 1 has headers).

does this help?
 

tstoneh

Board Regular
Joined
Feb 2, 2004
Messages
126

ADVERTISEMENT

I couldn't get the HTML thing to work. What do I need to do to get it to go?
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
download link (either my post above, or bottom of page - follow links). once downloaded, put in folder C:\Windows\Addins
then, in excel, click Tools -> Addins -> Browse
go to the above folder, click file. Ok, Ok.

it should put "Html" on your toolbar.
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
btw, you must unzip the file, once it's downloaded.

easier to unzip right to folder, then add it in excel.

sorry bout that.
 

tstoneh

Board Regular
Joined
Feb 2, 2004
Messages
126
It seems like this should be easy but it is still not recognizing the part number. Could it be because it is text or something?
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,565
Messages
5,765,133
Members
425,263
Latest member
alcat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top