converting question; function, formula, or coding?

[gtrask]

I am importing a spreadsheet with two columns; ID and Code number.
I want to convert these into readable text. Each combination (about 90
potential combinations) of ID and Code renders unique text. What is the best
way to convert the data? Should I use a function, read another table, etc.

For example:

ID Code Company Part Combined
10 100 ACME widget ACME widget
10 200 ACME cog ACME cog
10 300 ACME sprocket ACME sprocket
20 100 Bell Mfg wrench Bell Mfg wrench
20 400 Bell Mfg cog Bell Mfg cog
30 600 Zebra Dist bucket Zebra Dist bucket

 

[Per Erik Midtrød]

I am importing a spreadsheet with two columns; ID and Code number.
I want to convert these into readable text. Each combination (about 90
potential combinations) of ID and Code renders unique text. What is the best
way to convert the data? Should I use a function, read another table, etc..

For example:

ID      Code    Company Part    Combined
10      100     ACME    widget  ACME widget
10      200     ACME    cog     ACME cog
10      300     ACME    sprocket        ACME sprocket
20      100     Bell Mfg        wrench  Bell Mfg wrench
20      400     Bell Mfg        cog     Bell Mfg cog
30      600     Zebra Dist      bucket  Zebra Dist bucket

I don't think your list is quite clear, it appears that ID10 = ACME,
ID20 = Bell Mfg and ID30 = Zebra.
But what about the Codes?
Code 100= widget and wrench
200 and 400 = cog

Or maybe I am completely misunderstanding your setup.

Per Erik

 

[Pete_UK]

If this table is in the same worksheet, say starting in cell J1, then
insert a new column L and put this formula in L2:

=J2&K2

and copy this down.

Assuming your imported ID and Code are in columns A and B, then put
this formula in C2 to return your Combined column (now in column O):

=VLOOKUP(A2&B2,L$2:O$90,4,0)

Then copy this down to cover the data in columns A and B.

Hope this helps.

Pete

 

[gtrask]

Great answer. worked well. one last question. Can I make the table reference
a different static worksheet? That way I can have a master list and grab the
description from that.

 

[Pete_UK]

Glad to hear it - thanks for the feedback.

The answer is yes. I have a dynamic lookup table which contains rates, but
those rates change depending on some other choices in the workbook - the
formula for working out the pay is the same (duration times rate, derived
from the lookup table), but the rates change for each job/client.

The exact details of how you will do this will depend on how your data is
set up and what you want to achieve.

Hope this helps.

Pete