replacing adjacent cells

[Dylan]

Hi all,
I have a list of item descriptions with corresponding codes, and a set
of lists with item descriptions with quantities. What I need to do is add
the code to the description in another column in the second list (if that
makes sense). The data is on a dozen different worksheets.

e.g. what i have is a code list:

1111 foo
2222 bar
3333 baz

and quantity lists like:

foo 121
bar 203
baz 46


.... whereas what we need is:

1111 foo 121
2222 bar 203
3333 baz 46

I've tried going through it with macros (even staring at the code, to no
avail unsurprisingly) - find and replace doesn't seem to have what i'm after
(if I could just find all instances of x in column b, and replace them with
something in column a as well...).
If this was just a one-off thing it would be fine, as it only takes about an
hour to do by hand. However this is a monthly thing and I'd love to be able
to avoid wasting time and effort if possible.


I'm running Excel 2003 if that makes a difference. Can anyone help?



Thanks in advance

Dylan

 

[Frank Kabel]

Hi
if your target list currently looks like
A B C
1111 foo
2222 bar
3333 baz

And you want to get the values for column C enter the following in C1
(assumption: the quatity list is on a separate worksheet called
'sheet2'):
=VLOOKUP(B1,'sheet2'!$A$1:$B$999,2,0)
copy this formula down

If you like you can replace the formulas with the following procedure:
- copy column C
- goto 'Edit - Paste Special'
- Insert as 'Values'

 

[Earl Kiosterud]

Dylan,

Having your lists in separate sheets complicates things. It may be that you
should combine them into one table with an additional column for whatever
list they were originally in. That will open up tons of Excel
functionality, including this task.

Give us a description of your lists, and maybe we can take that a little
further.

 

[RagDyeR]

If
1111 foo
and
2222 bar
are in the *same* column,
AND
"foo" and "bar" are *not* always 3 character descriptions,
then you must first extract the description from the code list before you
can find it in the inventory (quantities) list.
Also, is there *always* a space between the code and the description?

Is the quantities list a single column also?
Is there *always* a space between the description and the quantity?

<<"The data is on a dozen different worksheets">>
Does this amount of data pertain to the "CodeList" or the "QuantityList", or
both?

If you could post some exact replicas of your data, it would be easier to
suggest a solution.

The use of "TextToColumns" *prior* to a Lookup might be useful if the data
configuration is appropriate.

OR, maybe code might be called for.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Hi all,
I have a list of item descriptions with corresponding codes, and a set
of lists with item descriptions with quantities. What I need to do is add
the code to the description in another column in the second list (if that
makes sense). The data is on a dozen different worksheets.

e.g. what i have is a code list:

1111 foo
2222 bar
3333 baz

and quantity lists like:

foo 121
bar 203
baz 46


.... whereas what we need is:

1111 foo 121
2222 bar 203
3333 baz 46

I've tried going through it with macros (even staring at the code, to no
avail unsurprisingly) - find and replace doesn't seem to have what i'm after
(if I could just find all instances of x in column b, and replace them with
something in column a as well...).
If this was just a one-off thing it would be fine, as it only takes about an
hour to do by hand. However this is a monthly thing and I'd love to be able
to avoid wasting time and effort if possible.


I'm running Excel 2003 if that makes a difference. Can anyone help?



Thanks in advance

Dylan

 

[Otto Moehrbach]

Dylan
You say "the data is on a dozen different worksheets". That muddies the
waters somewhat for a formula solution. And you say this is a monthly task.
With both of these conditions being present, I would go with code (macros)
to do this.
If you wish, send me, direct via email, a small file with a sample of
your data (all the sheets). Or send me the whole file if it isn't too big
and I'll setup something for you. Remove "cobia97" from email address. HTH
Otto