Excel first timer Macro user

Z

ZaXaZ

New Member
Joined
Feb 16, 2015
Messages
24
Hi, this is my first time having the need of a macro and it´s a bit overwhelming.
i started a new work and lets just say that no on here is very sharp when it comes to pc or what you can do with it most of the times.

so i get a list of car Models and types of the model in a txt doc witch most of the time is very long 100+ sometimes 10+ or 1000+. but it´s given to me in a very bad form and takes quite some time to do manual ea time..

and any one here help me with making a macro?

here is a very short example, of what i get to work with, and what i need to end up with :)
Model: Type
405: 101 103 121 123 201 221
900: 911 912 913 917 918 919 922 934 936 937 938
list goes one feels like its endless..

i need it to be made to
type Model
405 101
405 102
405 121
405 123
405 201
405 221

900 911
900 912
900 913
900 917
900 918
900 919
900 922
900 934
900 936
900 937
900 938
 
Last edited:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
which data is in what column ???
Code: 
Model: Type
405: 101 103 121 123 201 221
900: 911 912 913 917 918 919 922 934 936 937 938
 
Upvote 0
ZaXaZ said:
...so i get a list of car Models and types of the model in a txt doc ...but it´s given to me in a very bad form and takes quite some time to do manual ea time..
Click to expand...


I do not use a storage account, but I am under the impression that Boxnet (or something close to that) is okay. Assuming that by 'txtx doc' you mean an actual text file (.txt file extension, can you obfuscate any sensitive data and upload the file?

Mark
 
Upvote 0
Michael M said:
which data is in what column ???
Click to expand...
Michael M said:
Rich (BB code):
Rich (BB code): 
Model: Type
405: 101 103 121 123 201 221
900: 911 912 913 917 918 919 922 934 936 937 938
Click to expand...


column a column b
Model Type
405 101 103 121 123 201 221
900 911 912 913 917 918 919 922 934 936 937 938


A B
Model Type
405 101
405 102
405 121
405 123
405 201
405 221

900 911
900 912
900 913
900 917
900 918
900 919
900 922
900 934
900 936
900 937
900 938
 
Upvote 0
GTO said:
I do not use a storage account, but I am under the impression that Boxnet (or something close to that) is okay. Assuming that by 'txtx doc' you mean an actual text file (.txt file extension, can you obfuscate any sensitive data and upload the file?

Mark
Click to expand...

i am unsure what you mean by a storage account. but yes its a .txt file i get..
 
Upvote 0
You're original thread shows a colon ":" after the first 3 numbers, your later post doesn't ...which is it ?
Also, will the model always be 3 numbers ??
 
Upvote 0
Michael M said:
You're original thread shows a colon ":" after the first 3 numbers, your later post doesn't ...which is it ?
Also, will the model always be 3 numbers ??
Click to expand...

(ohh forgot to mention i´m on office 2010 if that makes any difference in the macro :D)

the ":" was a try to indicate colons

yes all numbers will always be 3 numbers if its a 21 it will be show as a 021, it´s very rare that i get a 2 numbers models or types

This is what it looks like, this is one of the very short once. some times as in this one there are also duplicates but i know how to remove that easy tho :)


2ylmfck.jpg
 
Last edited:
Upvote 0
Would this be it?
Code: 
Sub Maybe()
    Dim jve, c As Range
    Columns("D:E").NumberFormat = "@"
    Range("D2:E2").Value = Range("B2:C2").Value
    For Each c In Range("C3:C" & Cells(Rows.Count, 3).End(xlUp).Row)
        jve = Split([c], " ")
        With Cells(Rows.Count, 4).End(xlUp).Offset(2).Resize(UBound(jve) + 1)
            .Value = c.Offset(, -1).Value
            .Offset(, 1) = WorksheetFunction.Transpose(jve)
        End With
        Erase jve
    Next c
End Sub
 
Upvote 0
I think this will fail if a blank row is encountered during the process, this should allow for any blanks...
Also, this seems to post every 2nd line, so I've adjusred that also.
Code: 
Sub Maybe()
    Dim jve, c As Range
    Columns("D:E").NumberFormat = "@"
    Range("D2:E2").Value = Range("B2:C2").Value
    For Each c In Range("C3:C" & Cells(Rows.Count, 3).End(xlUp).Row)
        jve = Split([c], " ")
        On Error Resume Next
        With Cells(Rows.Count, 4).End(xlUp).Offset(1).Resize(UBound(jve) + 1)
            .Value = c.Offset(, -1).Value
            .Offset(, 1) = WorksheetFunction.Transpose(jve)
        End With
    Next c
End Sub
 
Upvote 0
@ MichaelM
Thank you for the fix.
I was under the impression that the OP wanted an extra line.

Thanks again Michael
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
average based on time
Replies
1
Views
279
baqer
B
J
  • Question
Excel macro for Remove comma's and spaces from a cell and place the data in different cell
Replies
3
Views
916
JEC
JEC
J
  • Question
Need some help with permutation code, convert from one cell to a range
Replies
3
Views
254
johnnyL
J
A
  • Solved
transpose data int listbox on userform based on two dates or without dates
Replies
2
Views
133
Alaa mg
A
M
  • Question
first derivative
Replies
3
Views
685
JamesCanale
JamesCanale

Forum statistics

Threads
1,214,515
Messages
6,119,973
Members
448,933
Latest member
Bluedbw

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top