need function to compare and return a value in Excel

[jazminecat]

Hi again all!

I have two worksheets that my boss needs a comparison done on. the information was downloaded from a database into excel.

Worksheet A has a list of parts and description, in columnC as Part and Description. In the format 123456789 TEXT. I cannot change this. It also has a columnG of numerical values.

Worksheet B has a columnA part # in format 123456789, Column B description as TEXT, and column F as numerical values.

I'm sure you can see where I"m going.

I need to look at WorksheetB column A, and compare that part number to the part number in Worksheet Column A, and then return the value from Worksheet B into Column G of Worksheet A.

I don't know how to look for a numerical value in a combined field...and I can't split off the part # and description, even though I should.

Example data:

Worksheet A

Part&Description Cost
12345Widget .05
88904Tire 1.24
99886Pants .55


Worksheet B

Item# Cost
12345 .06
88904 1.24
99886 1.00

So, I need to put a function in the Cost column of Worksheet A that will look at Part&Description and compare it to the part # on worksheet b, and update the cost using the value from WorksheetB.

I've played with Vlookup and match, but can't figure out how to get the value of WOrksheet B to compare to any value in worksheet A. I can't find a CONTAINS function.

Thanks in advance!

 

[SkipVought]

Hi,

I'd recommend using Data/Text to columns to parse the part/description into part & description columns. then your lookup would work.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[jazminecat]

I'd love to but I cannot change the data layout. people with far more control over my wage earning capabilities refuse to flex.

 

[SkipVought]

I can't believe it!

You could not make a copy of the data in question and rip it apart -- is it THAT sacred???

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[jazminecat]

well I could. But then when they want it back the function wouldn't be in the one they want to look at. I can do anything on a copy. They want this function on the sheet they use....

 

[SkipVought]

Then you're gonna have to write a function in VBA, I'm afraid.

I was going to also recommend a ms query compare solution. But if your management is dictating PROCESS rather than RESULTS requirements, you're up the creek.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[KenWright]

They're nuts, but if you really have to go there

Assuming you are on SheetB and looking up the value in cell A2 from a 2 column table on Sheet A in A1:B100
=VLOOKUP("*"&A2&"*",WShtA!$A$1:$B$100,2,0)

If more than one match then you're sunk

Personally I'd parse it and then pivot it. Use an Index field on your data to mark it old vs new, throw the old and the new set of data one on top of the other, pivot it and then have INDEX as your column field and then ID as your row fields.

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

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

 

[jazminecat]

Y'all, I can't change the sheet layout. I can't make a pivot table. And there are columns of data. I know my superiors aren't...

Isn't there a way to use concatenate in a function, to make it look at the concatenated information from the teo columns on the second sheet, compare that to the first sheet, and if a match, give me the number form the second sheet?

I guess I'm either stubborn or stupid, but it just doesn't seem like it's impossible, and i'm awfully surprised to be told that here...

 

[SkipVought]

[tt]
=INDEX(Sheet2!Cost,MATCH(VALUE(LEFT(Part_Description,5)),Item,0),1)
[/tt]
but as Ken stated, it will only find ONE occurrenct of the item.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[KenWright]

Did you actually try the formula I gave you first?

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

 

[jazminecat]

Assuming you are on SheetB and looking up the value in cell A2 from a 2 column table on Sheet A in A1:B100
=VLOOKUP("*"&A2&"*",WShtA!$A$1:$B$100,2,0)

No Ken, I didn't try this formula because the assumption was incorrect. I am starting from sheet a, looking up the values from sheet b. In addition, the tables are not two column tables, and do not start in cell A1 on either sheet.

 

[SkipVought]

But did you TRY to ADAPT it to your situation???

Ken is sitting at his desk at a major aerospace company, doing his job, and occasionally checking several Forums here at Tek-Tips. It's not uncommon for someone like that to read a question quickly and miss a nuance/assumption.

So put some of your own blood, sweat and tears into it, jaz!

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[jazminecat]

Skip - did I say something to annoy you in my original post? Because you've spent half of this thread berating me or my situation. i was just looking for some help here. I resent the implication that i haven't put any of my own time into it, as well. Do you know where I work, or how late I was here trying to make this thing work? I'm a database programmer, not an Excel programmer, and I don't use Excel very often, and now find myself in a place that is very attached to it's million and one spreadsheets, most of which should be in a database themselves. When I'm given something that's obviously clunky and inefficient, and often clearly a misuse of excel and set up simply wrong, I often don't have the option to change it. Even more often, I don't have the liberty to explain to the person who's written it or using it that it's ridiculous and should be fixed. I'd love to have that kind of freedom, or to be able to just pick up and move on, but neither of those are an option for me. Ken asked if I tried it and I answered honestly why I didn't try it. Perhaps I too missed a nuance, that it would work with a table that contained more than two columns, for starters.

 

[KenWright]

It's kind of hard to do anything more than make assumptions if you don't clarify the question fully when posting it. You haven't given us any ranges to work with, so offered solutions cannot be fully tailored to your particular issue. As Skip said, many times we will take a look at the question and knock out a quick solution with an assumed range, so that you can then see the correct syntax for the function in question, and then adapt the formula by using your own ranges. If it still doesn't work you can then post back citing the formula you tried, and an explanation of what does or doesn't work, and what you are actually seeing vs what you expected to see.

VLOOKUP will work with up to 256 columns, as that is all you can have. The FAQs have one specifically for VLOOKUP that may help, so try here

How does VLOOKUP work?? faq68-4743

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

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

 

[jazminecat]

SHEET A
C5 through C19 contains the list of concatenated part numbers and descriptions. (example: "22336698 Widget" all in one cell, without the quotes)

G5 through G19 needs to contain the price of that Widget.

Sheet B contains:

Column1 = Part number (example 22336698)
Column2 = Description (example Widget)
Column4 = Price (example $4.20)

I need to get the price from column4 of sheet b into column G of sheet A.

I have tried the following formulae in column G of Sheet A:

=VLOOKUP("*"&C5&"*",SHEETB!A1:E35,4,0) returns #N/A
=VLOOKUP("*"&C5&"*",SHEETB!A1:E35,4,1) returns #N/A

Evaluating the function shows it is getting the value of column C correctly, but after that it just says #N/A

If I create a concatenated column in sheet B with Columns 1 and 2, and include this new combined column in the criteria works. But, since I"m not 'allowed' to do that, it doesn't work without it.

 

[SkipVought]

The lookup range needs to have ABSOLUTE references.

It would be a WHOLE lot easier if you were to post columnar examples in columnar format to facilitate copy 'n' paste ir
[tt]
Part Number Description Price
22336698 Widget 4.20
[/tt]

What's the specification on the formats of the concatenated column? Is there ALWAYS a space between part number & description? Is part number a constant width? Need some help here.

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[SkipVought]

Once we have a good specifaction, it would probably be alot simpler to write a VBA function to attack this specific problem.

Will they let you write a user function?

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[UniqueFD]

Jaz,

It looks to me like you need a few steps:

1) Insert a column to the left of the Part number column on SHEET B
2) In this spare column (assuming it is column A and the part numbers start at row [red]1[/red]):
=VLOOKUP("*"&B[red]1[/red]&"*",SHEETA!C$5:C$19,1,FALSE)
[blue]This will bring the concatenated string into SHEET B, Column A, for the next step[/blue].
3) In column G on SHEET A:
=VLOOKUP(C5,SHEETB!A1:E1000,5,FALSE)

I think this will give you what you want. Let us know.

Tony
___________________________________________________
Reckless words pierce like a sword,
but the tongue of the wise brings healing (Solomon)