Excel first timer Macro user

[ZaXaZ]

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

 

[Michael M]

@jolivanes
You could be correct about the extra line.....but with imported data you just never know ....

 

[hiker95]

ZaXaZ,

1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


In your reply #4 you display the raw data in columns A, and, B.

In your reply #7 you display the raw data in columns B, and C.


So that we can get it right on the first try:

3. Can we see your workbook/worksheet after you have brought in the text file?

4. What columns do you want the results to be in?


The following is a free site. Click on the BLUE link:

You can upload your workbook to Box Net,

sensitive data changed

mark the workbook for sharing

and provide us with a link to your workbook.

 

[hiker95]

ZaXaZ,

Sample raw data, and, the results, in the active worksheet:

Excel 2007
	A	B	C	D	E
1
2		Model	Type	Model	Type
3		906	995	906	995
4		211	084 087 089 090 092
5		211	284 287 289 290 292	211	084
6		405	200 220	211	087
7		715	050 051 060	211	089
8		715	050 051 060	211	090
9		936	910 912 916	211	092
10		936	910 912 916
11		405	100 102 120 122 200 220	211	284
12				211	287
13				211	289
14				211	290
15				211	292
16
17				405	200
18				405	220
19
20				715	050
21				715	051
22				715	060
23
24				715	050
25				715	051
26				715	060
27
28				936	910
29				936	912
30				936	916
31
32				936	910
33				936	912
34				936	916
35
36				405	100
37				405	102
38				405	120
39				405	122
40				405	200
41				405	220
42
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub ReorgData()
' hiker95, 02/23/2015, ME837837
Dim lr As Long, c As Range, s, i As Long, nr As Long, n As Long
Application.ScreenUpdating = False
With ActiveSheet
  lr = .Cells(Rows.Count, 2).End(xlUp).Row
  With .Range("B2:C" & lr)
    .Value = Evaluate("IF(ISTEXT(" & .Address & "),TRIM(" & .Address & "),REPT(" & .Address & ",1))")
  End With
  .Columns("D:E").ClearContents
  .Cells(2, 4).Resize(, 2).Value = Array("Model", "Type")
  n = 1
  For Each c In .Range("C3:C" & lr)
    If n = 1 Then
      nr = 3
      n = 0
    Else
      nr = .Cells(.Rows.Count, "D").End(xlUp).Row + 2
    End If
    If InStr(c, " ") Then
      s = Split(c, " ")
      .Cells(nr, 4).Resize(UBound(s) + 1, 2).NumberFormat = "@"
      .Cells(nr, 5).Resize(UBound(s) + 1) = Application.Transpose(s)
      .Cells(nr, 4).Resize(UBound(s) + 1) = c.Offset(, -1).Value
    Else
      c.Offset(, -1).Resize(, 2).Copy .Cells(nr, 4)     '.Resize(, 2)
    End If
    Application.CutCopyMode = False
  Next c
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the ReorgData macro.

 

[ZaXaZ]

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.

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

how do i adjust for bigger or smaller range?..
also perhaps you can explain a bit of how this works so i can adjust it myself?

 

[ZaXaZ]

Reply to Michale M and Hiker95

The position of A+B or B+C is irrelevant for me, i had ½ af food on my way to a meeting so just made a quick pic of how i had the data.

here is my doc
https://docs.google.com/a/autoimport.dk/file/d/0B3rjVFHrzvCSb0dETS01dmFELXM/edit?pli=1
Here is one of the long list in RAW DATA (.txt)
https://docs.google.com/a/autoimport.dk/file/d/0B3rjVFHrzvCSVUN3REE1N1R3dFE/edit
why do i get this wired spaced empty cells from Hiker95 macro?

also is there a way to make it more print friendly?
and perhaps make a empty cell when the model nr change or another way to easy make a separation from 2 different model numbers ?

i know i ask a lot here. sorry

 

[jolivanes]

Re: How do I adjust for bigger or smaller range?

Have you tried the code?

 

[Michael M]

The code does all size ranges

 

[ZaXaZ]

The code does all size ranges

tried to read the macro seems i failed big at this then

any chance for the stuff in post #15 can be done?

 

[Michael M]

Did you try the code in posts #8 and #9 ??

Also, the links you provided require a login and password......most of the users here, won't bother OR risk doing that !!!

also is there a way to make it more print friendly

You could adjust your page setup to suit your requirements.

 

[ZaXaZ]

Did you try the code in posts #8 and #9 ??

Also, the links you provided require a login and password......most of the users here, won't bother OR risk doing that !!!


You could adjust your page setup to suit your requirements.

Yes the codes are tested.

My excel doc (public link) 3 arks in this one one original. and 2 with macros. Hiker95 and Yours/jolivanes
https://www.dropbox.com/s/gsvgq1r07lcxqxb/My macro.xlsx?dl=0
Input data from .txt (public link)
https://www.dropbox.com/s/56mcptu16f0rxch/One of the big once..txt?dl=0