Help writing a macro

[impulse24]

Hi,

I can't seem to figure this one out.

I have a spreadsheet with the data as below(there are a large number o
rows and columns, so I can't figure out how to set the cell references
and I don't think I can hard code them)


102004 100 test.jpg
010202 101 test2.jpg
010204 102 test3.jpg
010305 103 test4.jpg
321321 104 test5.jpg
213213 105 101
544544 106 100


Anytime there is a number in the third column, I need the value t
replaced with the name referenced by the number in the second column.
Below is how I need the new data to look

102004 100 test.jpg
010202 101 test2.jpg
010204 102 test3.jpg
010305 103 test4.jpg
321321 104 test5.jpg
213213 105 test2.jpg
544544 106 test.jpg

In the original data since the last two rows, had a numeric value i
the third column, I need to look back in the 2nd column for that value
and grab it's value in the third column. Sounds confusing, and sinc
the data varies I am not sure how to do it. Any help would be greatl
appreciated

 

[mudraker]

What you are after is not that hard to do.

A couple of thing first

1st. What happens when column 2 goes past 109 do you want just the las
digit or does 110 become test10.jpg

2nd. How do you get 105 = test2.jpg & 106 = test.jpg

3rd. Should 100 be just test.jpg or test0.jpg

4th. Is this to be a macro or can a formula do


One way :- paste this formula into c1 & copy down

="Test"&VALUE(RIGHT(C1,2))&".jpg

 

[mark]

102004 100 test.jpg

010202 101 test2.jpg
010204 102 test3.jpg
010305 103 test4.jpg
321321 104 test5.jpg
213213 105 101
544544 106 100

hey.

name your range from the 100 in column 2 to the last cell
in column 3 something like rgData

then, in a fourth column, use this formula:


=IF(ISNUMBER(C1),VLOOKUP(C1,rgData,2,0),C1)

Then, copy that formula down to the entire relevant range,
and then copy and pastespecial the values to your original
column 3.


If you need other help, post again.

 

[impulse24]

Mark,

Thanks so much for the help. This worked perfectly and was exactl
what I was looking for