Changing the content of column E to C

**mozak** · 06-11-2011, 07:40 PM

HI TO all

Pls help me with This one,

im tired of dragging and copying, how can i make this one like this without pressing "ctrl+c" and dragging.

what formula or function must be use to it?

pls help me any help is deeply appreciated

LIB shoud be 22
SHW must be 25
ALB must be 28

now
site,,, no.
LIB,,, 22
LIB,,, 22
LIB,,, 22
SHW,,, 22
SHW,,, 22
SHW,,, 25
SHW,,, 25
LIB,,, 25
SHW,,, 25
SHW,,, 25
SHW,,, 25
LIB,,, 28
ALB,,, 28
LIB,,, 28
ALB,,, 28
SHW,,, 28
ALB,,, 28
ALB,,, 28

AFTER "CTRL+c" +dragging (so very time consuming)
site no.
LIB,,, 22
LIB,,, 22
LIB,,, 22
LIB,,, 22
LIB,,, 22
SHW,,, 25
SHW,,, 25
SHW,,, 25
SHW,,, 25
SHW,,, 25
SHW,,, 25
ALB,,, 28
ALB,,, 28
ALB,,, 28
ALB,,, 28
ALB,,, 28
ALB,,, 28
ALB,,, 28

how can i make it this way.

**Marcol** · 06-11-2011, 08:07 PM

Try in B3

Please Login or Register  to view this content.

Drag/Fill Down

Then sort as required,

Your sample is a bit unorthodox. Does it have to be sorted as you have posted?

It is more normal to sort alphabetically ascending/descending

**mozak** · 06-11-2011, 10:06 PM

Originally Posted by Marcol

Try in B3

Please Login or Register  to view this content.

Drag/Fill Down

Then sort as required,

Your sample is a bit unorthodox. Does it have to be sorted as you have posted?

It is more normal to sort alphabetically ascending/descending

Yeah thats the one im looking for thank.. btw i change the formula like this

=IF(d2=28,"ALB",IF(d2=22,"LIB",IF(d2=25,"SHW",IF(d2=35,"CLK",IF(d2=40,"CB1",IF(d2=48,"CB2",IF(d2=3,"MAK","")))))))

so every value that ccome out will turn to that,

how can i manage it to copy all in the cell without dragging sir?

its too long to drag my data is up to 10000+

**mozak** · 06-11-2011, 10:18 PM

Changing my sample.
ill give you my original data.

10.22.xx.xx CC3 - Llibis
10.25.xx.xx CC7 - Shaw
10.28.xx.xx CC5 - Alabang
10.35.xx.xx CC8 - Clark
10.40.xx.xx CC4 - Cebu1
10.48.xx.xx CC6 - Cebu2
10.3.xx.xx CC2 - Makati

see attached file.

how can i make the attache file

neglect the "group name" use text to column

"what if i dont use text to column? is their any possible way to change it to there corresponding area?

My Company\LIB\Servers\General---->>>>LIB

**Marcol** · 06-12-2011, 05:35 AM

Try this

1/. Create two Dynamic Named Ranges
AddressGroup
Refers To:

Please Login or Register  to view this content.

AddressTable
Refers To:

Please Login or Register  to view this content.

2/. In C2

Please Login or Register  to view this content.

Drag/Fill Down
Use the Fill Handle (the small square bottom right of the selected cell) to do this.
Double-click the Fill Handle, this will fill to the last row of your data provided there are no blanks in Column B.

For more information on the Fill Handle see this link
Ozgrid - Excel Fill Handle Explained

I have added a sheet to the attached workbook that does what you first asked, it should help to see how the method works.

Try changing something in the Returns/Value (the orange headers) columns of the lookup tables to see the results change.
You can add to these lists and the formula will adjust automatically to suit, just don't leave blank cells in the first column.

N.B. I have a thing about using INDEX() with MATCH() you could use other lookup functions

Hope this helps.

**royUK** · 06-12-2011, 05:45 AM

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Edited this time