Need advice on data processing formulas in Excel 2010

excel forum example.xlsI am just beginning to work with VBA and macros. I do not yet know the language. I have so far been able to bring in data from an email and save as a text file. From the
text file I bring in the data into column E (starting at E5) and downward (never a fixed length). The info in columns G & I is the end result that I want. Is this really a "transpose"? Or what sort of formula/command do I need to make the entire E column (always a different amount of data) go to the G & I columns? I would think it needs to loop until it reaches the last cell in column E that contains data. Thank you for your suggestions and help!



A B C D E F G H I


77.50 NAME
77.50 0.31 NAME
NAME 88.25 NAME
0.31 84.59 NAME
NAME 62.20 NAME
88.25 100.00 NAME
NAME 103.40 NAME
84.59 140.00 NAME
NAME 66.69 NAME
62.20
NAME
100.00
NAME
103.40
NAME
140.00
NAME
66.69
NAME

robinc1969,

Welcome to the Excel Forum.

I am just beginning to work with VBA and macros. I do not yet know the language.

See if something below will assist you:

Training / Books / Sites as of 12/30/2012
http://www.excelforum.com/excel-prog...terials-3.html


Let me look are your request.

Have a great day,
Stan

robinc1969,

In reference to your workbook:


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, by highlighting the code and pressing the keys CTRL + C
2. Open your 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 by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Option Base 1
Sub ReorgData()
' stanleydgromjr, 01/14/2013
' http://www.excelforum.com/newreply.php?do=postreply&t=890900
Dim i As Variant, o As Variant, r As Long, s As Long
i = Range("E5").CurrentRegion
ReDim o(1 To UBound(i, 1) / 2, 1 To 3)
For r = 1 To UBound(i, 1) Step 2
  s = s + 1
  o(s, 1) = i(r, 1)
  o(s, 3) = i(r + 1, 1)
Next r
Range("G4").Resize(UBound(o, 1), UBound(o, 2)) = o
Range("G4").Resize(UBound(o, 1)).NumberFormat = "0.00"
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


Then run the ReorgData macro.


I will be back in a little while with another slightly modified macro.


Have a great day,
Stan

robinc1969,

The following macro creates two separate arrays for columns G and I, instead of one array for columns GHI. I was not sure what column H is being used for.


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).

Option Explicit
Sub ReorgDataV2()
' stanleydgromjr, 01/14/2013
' http://www.excelforum.com/newreply.php?do=postreply&t=890900
Dim e As Variant, g As Variant, i As Variant, lr As Long, r As Long, s As Long
e = Range("E5").CurrentRegion
lr = Cells(Rows.Count, "G").End(xlUp).Row
If lr > 3 Then Range("G4:G" & lr).ClearContents
lr = Cells(Rows.Count, "I").End(xlUp).Row
If lr > 3 Then Range("I4:I" & lr).ClearContents
ReDim g(1 To UBound(e, 1) / 2, 1 To 1)
ReDim i(1 To UBound(e, 1) / 2, 1 To 1)
For r = 1 To UBound(e, 1) Step 2
  s = s + 1
  g(s, 1) = e(r, 1)
  i(s, 1) = e(r + 1, 1)
Next r
Range("G4").Resize(UBound(i, 1)) = g
Range("G4").Resize(UBound(i, 1)).NumberFormat = "0.00"
Range("I4").Resize(UBound(i, 1)) = i
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


Then run the ReorgDataV2 macro.


Have a great day,
Stan

robinc1969,

What exactly is the difference in the two?

The last macro creates two separate arrays, so that when the data is written to columns G and I, nothing will happen to column H is you had something there.

You've saved me a LOT of time in my job.

For my own personal information: How much time in hours do you think my macro will save you per day/week/month?



Thanks for the feedback.

You are very welcome. Glad I could help.

Come back anytime.