Comparing 2 Columns

[tstoneh]

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?

 

[Zack Barresse]

yup, whatcha trying to compare, care to post what you have?

 

[tstoneh]

How do I post the data?

 

[Zack Barresse]

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]

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]

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
	A	B	C	D
1			handinput
2	12466	5	00012466
3	00012466	10	5
4	customformat
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]

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

 

[Zack Barresse]

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]

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]

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?