That post-title took me some time to come up with...

Here's what a want to do. I have a price list from last year in a
worksheet. I created a new price list for this year in a separate Excel
document. I would like to take last years data, lay it next to current data
and calculate the percent change in pricing on a product-by-product basis.

The "hitch" that we've added about 20 to 30 new products (out of a total of
800). Right now I have the following: Column A holds the product name from
this year; Column B to holds this year's cost; Column C holds the product
names from last year and Column D holds last year's pricing. I'd like to
sort such that A1 always equals C1 and such that any Column A data that has
no match in Column C, causes columns C and D to skip the cell in the C column
during the sort.

That's confusing and I know what I want... so here's an example I'd start
with:

2006 Prod 2006 Cost 2007 Prod 2007 Cost
ABC100 0.20 ABC100 0.19
ABC150 0.45 ABC190 0.68
ABC190 0.71 ABC200 0.39
ABC200 0.39 ABC250 0.84


NOW, Here's what I'd like after the sort:

2006 Prod 2006 Cost 2007 Prod 2007 Cost
ABC100 0.20 ABC100 0.19
ABC150 0.45
ABC190 0.71 ABC190 0.68
ABC200 0.39 ABC200 0.39
ABC250 0.88 ABC200 0.84

Thanks

I'm going to asume the 2006 data is in another workbook
so, in column A, you have this year's items
column b has this year's cost
in column c:
=if(VLOOKUP(A2,old wrokbook and sheetname A:B,1,FALSE) = FALSE,
"",VLOOKUP(A2,old wrokbook and sheetname A:B,1,FALSE))
in column D:
=if(VLOOKUP(A2,old wrokbook and sheetname A:B,2,FALSE) = FALSE,
"",VLOOKUP(A2,old wrokbook and sheetname A:B,2,FALSE))

How's that work?

"ConfusedNHouston" wrote:

That post-title took me some time to come up with...

Here's what a want to do. I have a price list from last year in a
worksheet. I created a new price list for this year in a separate Excel
document. I would like to take last years data, lay it next to current data
and calculate the percent change in pricing on a product-by-product basis.

The "hitch" that we've added about 20 to 30 new products (out of a total of
800). Right now I have the following: Column A holds the product name from
this year; Column B to holds this year's cost; Column C holds the product
names from last year and Column D holds last year's pricing. I'd like to
sort such that A1 always equals C1 and such that any Column A data that has
no match in Column C, causes columns C and D to skip the cell in the C column
during the sort.

That's confusing and I know what I want... so here's an example I'd start
with:

2006 Prod 2006 Cost 2007 Prod 2007 Cost
ABC100 0.20 ABC100 0.19
ABC150 0.45 ABC190 0.68
ABC190 0.71 ABC200 0.39
ABC200 0.39 ABC250 0.84


NOW, Here's what I'd like after the sort:

2006 Prod 2006 Cost 2007 Prod 2007 Cost
ABC100 0.20 ABC100 0.19
ABC150 0.45
ABC190 0.71 ABC190 0.68
ABC200 0.39 ABC200 0.39
ABC250 0.88 ABC200 0.84

Thanks

Thanks for trying, but you lost me. All the data's on one page but I can
move it if needbe. I'm not a VLOOKUP wizard by a LONG shot, so I have no
concept of what I'm actually "saying" in the syntax below. I did take a stab
at entering it though and the cell comes back #NAME?.

I do appreciate your time though...

"Sean Timmons" wrote:

I'm going to asume the 2006 data is in another workbook
so, in column A, you have this year's items
column b has this year's cost
in column c:
=if(VLOOKUP(A2,old wrokbook and sheetname A:B,1,FALSE) = FALSE,
"",VLOOKUP(A2,old wrokbook and sheetname A:B,1,FALSE))
in column D:
=if(VLOOKUP(A2,old wrokbook and sheetname A:B,2,FALSE) = FALSE,
"",VLOOKUP(A2,old wrokbook and sheetname A:B,2,FALSE))

How's that work?

"ConfusedNHouston" wrote:

That post-title took me some time to come up with...

Here's what a want to do. I have a price list from last year in a
worksheet. I created a new price list for this year in a separate Excel
document. I would like to take last years data, lay it next to current data
and calculate the percent change in pricing on a product-by-product basis.

The "hitch" that we've added about 20 to 30 new products (out of a total of
800). Right now I have the following: Column A holds the product name from
this year; Column B to holds this year's cost; Column C holds the product
names from last year and Column D holds last year's pricing. I'd like to
sort such that A1 always equals C1 and such that any Column A data that has
no match in Column C, causes columns C and D to skip the cell in the C column
during the sort.

That's confusing and I know what I want... so here's an example I'd start
with:

2006 Prod 2006 Cost 2007 Prod 2007 Cost
ABC100 0.20 ABC100 0.19
ABC150 0.45 ABC190 0.68
ABC190 0.71 ABC200 0.39
ABC200 0.39 ABC250 0.84


NOW, Here's what I'd like after the sort:

2006 Prod 2006 Cost 2007 Prod 2007 Cost
ABC100 0.20 ABC100 0.19
ABC150 0.45
ABC190 0.71 ABC190 0.68
ABC200 0.39 ABC200 0.39
ABC250 0.88 ABC200 0.84

Thanks