Excel Gurus Come Hither! - Chicagoland Sportbikes
Chicagoland Sportbikes
 
Geek Squad to the rescue! fucken computer crashing on you again? another porn site infected your computer? tierd of your wife seeing your anal fisting videos and want to lock her out of the external HD? get the answers you need from our local computer experts.

 
LinkBack Thread Tools Display Modes
post #1 of 17 (permalink) Old 08-26-2010, 05:08 PM Thread Starter
d0 work s0n!
 
paintedblue's Avatar
 
Join Date: May 2006
Location: West Chicago
Posts: 2,218
Location: West Chicago
Sportbike: '06 GSX-R
Years Riding: Moment I got off the street
How you found us: twinky in the donut?
           
Excel Gurus Come Hither!

Any excel gurus out there? I need some help. I have a massive excel sheet for a job we are working on and the problem is some of the cells in each row has multiple numbers in it, room numbers to be exact. I have tried the "text to columns" button but it just moves everything to a new column. I need the data to move down (new row) and also, if possible, retain the rest of the data in the string. so for instance, i have a string as follows:

ITEM, DRAW, DEPT, RM NAME, RM #

If the data then reads:
ITEM/ DRAW/ DEPT/ RM NAME/ 12420, 12421, 12422, 12423

If the RM # column has more then one number, then I need a new row to start with the same data to the left of the RM # column, however with only one number (RM #). So if there are 12 RM #'s I need the data repeated per room. For example,

I need the data to then read:
ITEM/ DRAW/ DEPT/ RM NAME/ 12420
ITEM/ DRAW/ DEPT/ RM NAME/ 12421
ITEM/ DRAW/ DEPT/ RM NAME/ 12422
ITEM/ DRAW/ DEPT/ RM NAME/ 12423

I hope that makes sense. I'm leaving work now but will try and check back later. If not, tomorrow.

Thanks for your help ahead of time.

#910

"if i close my mind in fear, please pry it open. if my face becomes sincere beware.
when i start to come undone, stitch me together.
when you see me stretched remind me of what left this outlaw torn."

Last edited by paintedblue; 08-27-2010 at 01:37 PM.
paintedblue is offline  
Sponsored Links
Advertisement
 
post #2 of 17 (permalink) Old 08-26-2010, 05:29 PM
Registered User
 
Aussie Grover's Avatar
 
Join Date: Sep 2006
Location: Chicago, IL
Posts: 948
Location: Chicago, IL
Sportbike: bikeless!!!
Years Riding: 19 Years minus 14 years
How you found us: Member
           
Are the room numbers seperated by a comma, tab, space etc? If so, you could do an import data and specify the delimiter to see how that works out for you.

Glenn
Aussie Grover is offline  
post #3 of 17 (permalink) Old 08-26-2010, 05:35 PM
Registered User
 
Gone In 3's Avatar
 
Join Date: Mar 2009
Location: Texas/Chicago
Posts: 5,771
Location: Texas/Chicago
Sportbike: CBR1000RR
Years Riding: Been around the block with training wheels
How you found us: Folks
           
If what centipede said above doesn't work, then upload the file along with nudies of wife/girlfriend for better results.
Gone In 3 is offline  
Sponsored Links
Advertisement
 
post #4 of 17 (permalink) Old 08-27-2010, 05:49 AM
Ron
Workin' up to the track
 
Join Date: Apr 2002
Location: Central WI
Posts: 801
Location: Central WI
Sportbike: In between bikes right now
Years Riding: 15
How you found us: don't remember
      
database guy by day...and there are a number of ways to do this, especially if the numbers are consistent in length. if that is the case use the excel LEFT or RIGHT formula to parse what you want from the string.

If it is more complicated than that drop an email to [email protected] with the file and some specifics and I can take a detailed look

We cannot defend freedom abroad by deserting it at home.

We must not confuse dissent with disloyalty. When the loyal opposition dies, I think the soul of America dies with it.

- Edward R. Murrow
Ron is offline  
post #5 of 17 (permalink) Old 08-27-2010, 06:38 AM
Irony helps us play!
 
Arch's Avatar
 
Join Date: Jan 2006
Location: N'ville
Posts: 29,508
Location: N'ville
Sportbike: 2000 F4
Years Riding: Long enough to know better
How you found us: some hot MILF whispered it in my ear
           
Post a two lines of data, so people can give you an exact answer.


1 line with 1 room #, and another line with multiple room numbers...

ALso, do you have the raw data (text file, csv, etc) or just the excel sheet you currently have?

Everyone Exaggerates

We're being taken for a ride... agaaaaaaain.....


Best Auto/Moto Insurance | Motorcycle Protection Today | FREE Trade-In Quote

Last edited by Arch; 08-27-2010 at 06:41 AM.
Arch is offline  
post #6 of 17 (permalink) Old 08-27-2010, 07:08 AM
King Nothing


 
Kegger's Avatar
 
Join Date: Nov 2004
Location: Ur Moms House
Posts: 17,944
Location: Ur Moms House
Sportbike: I ride Ur Mom
Years Riding: As long as Ive known Ur Mom
How you found us: u found me
           
Is this for scheduling the Craigslist adult services ad you have running?

"When in doubt, use full throttle. It may not improve your situation, but it will end the suspense."
Kegger is offline  
post #7 of 17 (permalink) Old 08-27-2010, 07:20 AM
Registered User
 
defiant's Avatar
 
Join Date: Jun 2009
Location: Vernon Hills, Illinois
Posts: 139
Location: Vernon Hills, Illinois
Sportbike: Not worth stealing, really.
Years Riding: 6 years
How you found us: NA
 
You can use a vba or simple vbscript to do what you want. A nested loop can do it. If you need help let me know, if you want me to write it I expect some enumeration, a 1.75L bottle of Jack Daniels will do. Not just the bottle! The JD has to be in it and still sealed! Some cheap ass jokers out there.
defiant is offline  
post #8 of 17 (permalink) Old 08-27-2010, 01:26 PM Thread Starter
d0 work s0n!
 
paintedblue's Avatar
 
Join Date: May 2006
Location: West Chicago
Posts: 2,218
Location: West Chicago
Sportbike: '06 GSX-R
Years Riding: Moment I got off the street
How you found us: twinky in the donut?
           
Thanks for all of the responses and sorry for my delay. Alright lots of answers and most over my head. I've attached a screen shot of an example since I can't attach an excel doc.

As you can see the first line (Column G) has one room number and the second has a ton. I need the second line to be broken up with the exact same info but with each room separate. So rows 3 through whatever should have all the same info as the second row but only one room number (one row - 12421, next row 12422, etc). Btw, the solution must be able to be used on an entire doc too, not just this one example.

Basically, I need to make the document filterable. I need to be able to filter by room number so I can see what equipment goes where. However, with some of the rows having multiple rooms I can't do this.
Attached Images
File Type: jpg Rm Number Split.jpg (16.4 KB, 16 views)

#910

"if i close my mind in fear, please pry it open. if my face becomes sincere beware.
when i start to come undone, stitch me together.
when you see me stretched remind me of what left this outlaw torn."

Last edited by paintedblue; 08-27-2010 at 01:29 PM.
paintedblue is offline  
post #9 of 17 (permalink) Old 08-27-2010, 01:37 PM Thread Starter
d0 work s0n!
 
paintedblue's Avatar
 
Join Date: May 2006
Location: West Chicago
Posts: 2,218
Location: West Chicago
Sportbike: '06 GSX-R
Years Riding: Moment I got off the street
How you found us: twinky in the donut?
           
I reread my original post and I must have been in a hurry bc it didn't make all that much sense. I changed it and made some clarifications. Thanks!

#910

"if i close my mind in fear, please pry it open. if my face becomes sincere beware.
when i start to come undone, stitch me together.
when you see me stretched remind me of what left this outlaw torn."
paintedblue is offline  
post #10 of 17 (permalink) Old 08-27-2010, 02:12 PM
Irony helps us play!
 
Arch's Avatar
 
Join Date: Jan 2006
Location: N'ville
Posts: 29,508
Location: N'ville
Sportbike: 2000 F4
Years Riding: Long enough to know better
How you found us: some hot MILF whispered it in my ear
           
I can do this with a quick shell script, but not Excel... if I had a text file with the raw data in it, this would be very simple to do.

Everyone Exaggerates

We're being taken for a ride... agaaaaaaain.....


Best Auto/Moto Insurance | Motorcycle Protection Today | FREE Trade-In Quote
Arch is offline  
post #11 of 17 (permalink) Old 08-27-2010, 02:17 PM Thread Starter
d0 work s0n!
 
paintedblue's Avatar
 
Join Date: May 2006
Location: West Chicago
Posts: 2,218
Location: West Chicago
Sportbike: '06 GSX-R
Years Riding: Moment I got off the street
How you found us: twinky in the donut?
           
Quote:
Originally Posted by Arch View Post
I can do this with a quick shell script, but not Excel... if I had a text file with the raw data in it, this would be very simple to do.
thanks arch but unfortunately i can't give out the data.

#910

"if i close my mind in fear, please pry it open. if my face becomes sincere beware.
when i start to come undone, stitch me together.
when you see me stretched remind me of what left this outlaw torn."
paintedblue is offline  
post #12 of 17 (permalink) Old 08-27-2010, 02:22 PM
Irony helps us play!
 
Arch's Avatar
 
Join Date: Jan 2006
Location: N'ville
Posts: 29,508
Location: N'ville
Sportbike: 2000 F4
Years Riding: Long enough to know better
How you found us: some hot MILF whispered it in my ear
           
Quote:
Originally Posted by paintedblue View Post
thanks arch but unfortunately i can't give out the data.
I'll write you a script that will work on any unix box, mac or windows box (with cygwin) anyway... and if you can use it, knock yourself out...

Will post it in a bit...

Everyone Exaggerates

We're being taken for a ride... agaaaaaaain.....


Best Auto/Moto Insurance | Motorcycle Protection Today | FREE Trade-In Quote
Arch is offline  
post #13 of 17 (permalink) Old 08-27-2010, 02:24 PM
BSB > WSBK > MotoGP
 
shadrach's Avatar
 
Join Date: May 2004
Location: South Loop & Cary
Posts: 11,948
Location: South Loop & Cary
Sportbike: '02 GSXR 600 / '98 CBRF3
Years Riding: Since '03
How you found us: Google
           
Arch you friggen hippy liberal, you know the script won't work on a treadmill

Jeff
NESBA #311
'02 GSXR600
'98
Smokin' Joe's F3
shadrach is offline  
post #14 of 17 (permalink) Old 08-27-2010, 02:35 PM
Registered User
 
defiant's Avatar
 
Join Date: Jun 2009
Location: Vernon Hills, Illinois
Posts: 139
Location: Vernon Hills, Illinois
Sportbike: Not worth stealing, really.
Years Riding: 6 years
How you found us: NA
 
Let me know which way you go. Outside of an macro, vbscript is prob easiest way to go since it's an excel spreadsheet.
defiant is offline  
post #15 of 17 (permalink) Old 08-27-2010, 02:38 PM
Registered User
 
Join Date: Jun 2004
Location: Evanston
Posts: 175
Location: Evanston
Sportbike: HD V-ROD/Honda CBR1000RR
Years Riding: 1
How you found us: Friend
 
Depending on the format of the data in the cell, you might need to write a parse function. Then do text to column, and lastly you can do a pivot table to produce a row for each value in that column.

2004 Silver Honda CBR1000RR
2003 H.D. V-ROD
roman is offline  
post #16 of 17 (permalink) Old 08-27-2010, 02:56 PM
Irony helps us play!
 
Arch's Avatar
 
Join Date: Jan 2006
Location: N'ville
Posts: 29,508
Location: N'ville
Sportbike: 2000 F4
Years Riding: Long enough to know better
How you found us: some hot MILF whispered it in my ear
           
If you can export the data into the format listed in the first post, I have a script that will do it for you... (fields delimited by / rooms delimited by , )

save the data to a text file 'pipe' the file through it, it will change it...

in.in = original data text file...

$ cat in.in

ITEM/ DRAW/ DEPT/ RM NAME/ 12333
ITEM/ DRAW/ DEPT/ RM NAME/ 12333, 1234, 1235, 1236


# breakit.sh is the script. pipe it to the script, and catch it into another file.

$cat in.in | sh ./breakit.sh >out.out


#Let's see what it did.

$cat out.out

ITEM/ DRAW/ DEPT/ RM NAME/ 12333
ITEM/ DRAW/ DEPT/ RM NAME/ 12333
ITEM/ DRAW/ DEPT/ RM NAME/ 1234
ITEM/ DRAW/ DEPT/ RM NAME/ 1235
ITEM/ DRAW/ DEPT/ RM NAME/ 1236


# Re-Import your new data...
# get drink, be happy.. it's 2:55 on a friday.


PM me for script location... I have it on a website that you can grab and save.

Everyone Exaggerates

We're being taken for a ride... agaaaaaaain.....


Best Auto/Moto Insurance | Motorcycle Protection Today | FREE Trade-In Quote
Arch is offline  
post #17 of 17 (permalink) Old 08-27-2010, 03:33 PM Thread Starter
d0 work s0n!
 
paintedblue's Avatar
 
Join Date: May 2006
Location: West Chicago
Posts: 2,218
Location: West Chicago
Sportbike: '06 GSX-R
Years Riding: Moment I got off the street
How you found us: twinky in the donut?
           
Quote:
Originally Posted by Arch View Post
If you can export the data into the format listed in the first post, I have a script that will do it for you... (fields delimited by / rooms delimited by , )

save the data to a text file 'pipe' the file through it, it will change it...

in.in = original data text file...

$ cat in.in

ITEM/ DRAW/ DEPT/ RM NAME/ 12333
ITEM/ DRAW/ DEPT/ RM NAME/ 12333, 1234, 1235, 1236


# breakit.sh is the script. pipe it to the script, and catch it into another file.

$cat in.in | sh ./breakit.sh >out.out

#Let's see what it did.

$cat out.out

ITEM/ DRAW/ DEPT/ RM NAME/ 12333
ITEM/ DRAW/ DEPT/ RM NAME/ 12333
ITEM/ DRAW/ DEPT/ RM NAME/ 1234
ITEM/ DRAW/ DEPT/ RM NAME/ 1235
ITEM/ DRAW/ DEPT/ RM NAME/ 1236


# Re-Import your new data...
# get drink, be happy.. it's 2:55 on a friday.


PM me for script location... I have it on a website that you can grab and save.
I see lots of pretty symbols but only understand the part about me drinking. I'm f'd.

#910

"if i close my mind in fear, please pry it open. if my face becomes sincere beware.
when i start to come undone, stitch me together.
when you see me stretched remind me of what left this outlaw torn."
paintedblue is offline  
Reply

Quick Reply
Message:
Options

Register Now



In order to be able to post messages on the Chicagoland Sportbikes forums, you must first register.
Please enter your desired user name, your email address and other required details in the form below.

User Name:
Password
Please enter a password for your user account. Note that passwords are case-sensitive.

Password:


Confirm Password:
Email Address
Please enter a valid email address for yourself.

Email Address:
OR

Log-in










Thread Tools
Show Printable Version Show Printable Version
Email this Page Email this Page
Display Modes
Linear Mode Linear Mode



Posting Rules  
You may post new threads
You may post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On
Trackbacks are On
Pingbacks are On
Refbacks are On

 
For the best viewing experience please update your browser to Google Chrome