Learning how to fully use Excel

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Kanai22

Thread Starter
Joined
Mar 31, 2010
Messages
12
I am trying to figure out to automatically convert rows into columns. I know how to do it by transposing the information myself but is there anyway to set it up so that it converts automatically and still stays in the correct row? I have attached a copy of what I am working on. (all personal info blacked out) The section above the black line is what I need it to look like and below the black line is the info that I am starting with. I only have Excel 2003. I believe that I have Windows XP. It's a work computer and they have a lot of access blocked. Any help would be absolutly greatly appreciated!!:)
 

Attachments

Joined
Oct 20, 2004
Messages
7,837
Your file is really a mess, with things as varied as words being split into 2 cells (see M20 and M21 for an example.)
Also, to do a transposition properly, you need the same number of rows each time. Given that the most listings you have in this file is 17, someone will need to write some VBA to count down from one model name to the next and then insert enough lines between them to make a total of 25 (or whatever number makes sense to you.)
Then the data can be transposed to 25 columns.
Alas, I am not a VBA guy - I just kinda know what they need to do. But I will see if any are around.
 

Kanai22

Thread Starter
Joined
Mar 31, 2010
Messages
12
I am pulling the information from an internal company program. So I have no idea how to fix the words that are broken up. The Program that I'm pulling the data from is called ERALink32 Windows. Any Ideas?
 
Joined
Jul 1, 2005
Messages
8,546
Hi. :)

I did a quick check using this formula:

=LEN(M18)

in N18 & copied downwards. It appears that the "cropping" occurs when the text length reaches 55 characters. But you couldn't use code like "if text = 55 characters, add the text from the cell below" because in some cases the text length might be exactly 55 characters.

Do you have any ideas on that? :)
 

Kanai22

Thread Starter
Joined
Mar 31, 2010
Messages
12
You are very right!! The text length is 55 characters. I have no clue how to change it in the program that I am pulling the info from. These are the recommended services for vehicles that have recently been serviced and our advisors write these notes for each vehicle. I am so stumped on how to get this wooksheet the way my boss wants it. So your help is very much appreciated!! Oh and were do I enter the formulas? I'm new at using these formulas.
 
Joined
Jul 1, 2005
Messages
8,546
See the attached.

It would be possible to write code that bolts together the "cell groups" in column M except those where any of the cells (in a group) = 55 characters. You could probably even put in a switch -- have a prompt display like "cell xyz is 55 characters -- do you want to join it with the cell below, Yes/No?".

I'll have another look in the morning (UK). :)
 

Attachments

Joined
Jul 1, 2005
Messages
8,546
I made Sheet2 and Sheet3 into copies of Sheet1.

Sheet2 is just to show you the TRANSPOSE (array) formula (N18:R18).

Sheet3: I made a macro for Sheet3, the key combo to run it is CTRL+Shift+M.

Select K18 on Sheet3, then press CTRL+Shift+M. M18:M22 will be transpose to N18:R18 -- rows 19:22 will be deleted -- cell O18 will be coloured red, which means "flagged for inspection" because it has 55 characters -- the next cell down will be selected for the next "pass".

So in theory you can just keep pressing CTRL+Shift+M over and over till you get to the bottom, and then review the red cells. I have no idea how many of these you have to do. How about you?
 

Attachments

Kanai22

Thread Starter
Joined
Mar 31, 2010
Messages
12
As of right now I have 925 customers/vehicles. Thats just from 2/01/10-3/27/10. This is something that I will be doing frequently so the amount of customers/vehicles will differ each time.
 

Kanai22

Thread Starter
Joined
Mar 31, 2010
Messages
12
Thank you again for your help!! That macro is absolutely perfect. So now I just need help with transfering it over to my worksheet that has all of my raw data. Any ideas?
 
Joined
Oct 20, 2004
Messages
7,837
Would it be possible for you to import your data into Access rather than Excel? 55 characters is probably a limit from the other program, but possibly it is an artifact of the extraction process itself. In Access you could set the field length to be several hundred characters - or make the fields memo fields, which are nearly unlimited in size. Possibly that would eliminate the breakups.
 

Kanai22

Thread Starter
Joined
Mar 31, 2010
Messages
12
Bomb #21,
Can you help me so that in the vehicle make column it strickly just says the make of the vehicle? Example; right now it says; FORD TRUCK and I want it to just say; FORD. Same with the other vehicle makes.
 
Joined
Jul 1, 2005
Messages
8,546
"That macro is absolutely perfect."

Nope. Had to adjust it for row 62 (one action only, therefore no rows to delete).

You can have the transposed "cell groups" overwrite column M by changing:

ActiveCell.Offset(, 3).Resize(, Actions) = Application.Transpose(ActiveCell.Offset(, 2).Resize(Actions))

to:

ActiveCell.Offset(, 2).Resize(, Actions) = Application.Transpose(ActiveCell.Offset(, 2).Resize(Actions))

and:

For Each Cell In ActiveCell.Offset(, 3).Resize(, Actions)

to:

For Each Cell In ActiveCell.Offset(, 2).Resize(, Actions)

"help me so that in the vehicle make column it strickly just says the make of the vehicle?"

For that you can add:

If InStr(ActiveCell, " ") <> 0 Then
ActiveCell = Left(ActiveCell, InStr(ActiveCell, " ") - 1)
End If


immediately before:

ActiveCell.Offset(1).Select

Did you try slurpee's Access suggestion yet?
 

Attachments

Kanai22

Thread Starter
Joined
Mar 31, 2010
Messages
12
No I haven't. I have been working on something else and haven't had a chance yet. I will try it. Thank you so much for all of your help!! You have been a life savor!! :)
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

No members online now.
Top