Macro to look at certain parts of a cell and compare?

[kuat00]

Hi Everyone,

I'm not sure if a macro will help with this problem but I'll explain the situation and hopefully some one can help.

I have a list of Expense Codes in Column A. They look like this
0010-0020-8200-70.

Where
0010: Represents a Location
0020: Represnts the Business Type
8200-70: Represents Expense Type

I also have 3 tables on the spread sheet, which explains what these codes mean.

I need a macro that will look at the numbers in colum A and return the three types of expenses so they dont have to be looked up manually.

I hope this makes sense.
Can anybody help.

Thanks a bunch,
Nick

[Paul]

Hi Nick,

You shouldn't need a macro for this. You should be able to just use VLOOKUP if you already have tables with lookup values and their descriptions.

For example, if A1:A10 have full Expense Codes, and your Expense Type lookup table is in H1:I50, then in B1 use:

=VLOOKUP(RIGHT(A1,7),$H$1:$I$50,2,0)

Fill that down column B for as many rows as you have in column A. This formula looks at the last 7 characters of A1 (8200-07 is 7 chars including the "-") and if it finds a match in H1:H50, it will return the value (expense type) from column I.

Hope that helps!

[Paul]

Just noticed you want to return all 3 descriptions... read it wrong. You could still use VLOOKUP, just three times in the formula.

Assume Locations are in E1:F40
Assume Business Types are in H1:I40
Assume Expense Types are in K1:L40

=VLOOKUP(VALUE(LEFT(A1,4)),$E$1:$F$40,2,0)&" "&VLOOKUP(VALUE(MID(A1,6,4)),$H$1:$I$40,2,0)&" "&VLOOKUP(RIGHT(A1,7),$K$1:$L$40,2,0)

I added the VALUE function to the first two in case your lookup table has those numbers stored as number and not text. Using the LEFT, MID and RIGHT functions forces the VLOOKUP to search for the text, not the numeric value. If your lookup tables are stored as text, remove the VALUE() wrapper around the LEFT and MID functions.

This formula also assumes that all of your codes are formatted the same:
4 digits, dash, 4 digits, dash, 4 digits, dash, 2 digits.

[kuat00]

Hi,

Thanks for your help, but in this case I'm not sure that will work.
If I upload the sheet could you possibly have a look?

Thanks
Nick

[Paul]

I don't see how uploading a sheet could hurt. (Famous last words!)

If you do upload a sheet, be sure to show us what you have, and what you would expect as the results.

Thanks!

[kuat00]

Awesome...Thanks for your help.
I've colored the columns and tables that correspond.
Just to recap I need to compare the data in column 1
with three tables. I need to have the resulting data go into three seperate columns whihc are labeled on the sheet.

I have done an example in row one to show you how the data should go.

Thanks again for the help.

[kuat00]

Never Mind. I adapted your previous formula and it works perfect.
Thanks very much for the help.
Nick

[Paul]

Nick, see the attached updated copy of your spreadsheet.

I altered your lookup columns slightly to remove unnecessary columns and merged cells. Try using "center across selection" in cell formatting rather than merged cells, trust me on this.

For your Expense Type description table, I made it all vertical rather than having some in columns MNO and others in columns PQR. This makes using VLOOKUP and INDEX/MATCH functions possible. Once I had the lookup tables organized properly, I then named the range of J2:K24 "Business". I see you already named the range G2:H55 as "Location". I didn't create a named range for the expense types, since it would be awkward to use it an INDEX/MATCH function.

For the functions I used, see cells A2, B2 and C2. The first two are simple VLOOKUP functions based on the values of the first and second set of four digits. The third is a bit more complex, using INDEX/MATCH so that I can concatenate the third set of four digits with the last two digits, and match that up with the concatenation of columns M and N, to return the values in column O.

Whew.. I hope that all made sense. In your test sheet I modified A3 to match A2 so I could confirm it was pulling the correct data.

Let me know if you need further assistance!