# Comparing 2 Columns

#### tstoneh

##### Board Regular
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
yup, whatcha trying to compare, care to post what you have?

#### tstoneh

##### Board Regular
How do I post the data?

#### Zack Barresse

##### MrExcel MVP

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

#### tstoneh

##### Board Regular

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

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

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

#### Zack Barresse

##### MrExcel MVP
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

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

sorry bout that.

#### tstoneh

##### Board Regular
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?

Replies
7
Views
675
Replies
13
Views
758
Replies
3
Views
135
Replies
0
Views
204
Replies
3
Views
148

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

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.

### Which adblocker are you using?

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

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