Solved

macro to reformat data in excel

Posted on 2014-03-27
6
428 Views
Last Modified: 2014-04-16
Hi Experts,

I have csv export from server view around 80000 lines. the data extracted is like username and list of mutiple mailgroups user is part of.

Current extract
Username
   #Groups1
    !Group 2

Desired format is in new excel tab
username #Group1
username !group2

Please could you help
Kind Regards
Macentrap
Test.xlsx
0
Comment
Question by:macentrap
6 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39960571
an easier method may be to run a simple macro that deletes all rows where column B is empty?

SORRY, I LOOKED AT 'DESIRED' NOT 'CURRENT' -
0
 
LVL 7

Accepted Solution

by:
COACHMAN99 earned 250 total points
ID: 39960607
1. you shouldn't use 'current' as a sheet name.

2. try this:
Sub FixData()
  Dim x As Long, lastRow As Long, thisUser As String
  lastRow = ActiveWorkbook.Worksheets("XXX").Range("B65000").End(xlUp).Row + 1
  x = 2
  While x < lastRow
    If Cells(x, 1) <> "" Then
      x = x + 1
      While Cells(x, 1) = "" And x < lastRow
        Cells(x, 1) = Cells(x - 1, 1)
        x = x + 1
      Wend
    End If
  Wend
End Sub
0
 
LVL 22

Expert Comment

by:Flyster
ID: 39960632
Here's one way to do that:

Sub CopyData()
Dim i, r As Integer
Application.ScreenUpdating = False
i = Range("B" & Rows.Count).End(xlUp).Row
For r = 2 To i
  Range("A" & r).Select
    If Len(Range("A" & r).Value) > 0 Then
      Selection.Copy
    Else
      Range("A" & r).Select
      ActiveSheet.Paste
    End If
  Next r
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Flyster
0
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39960730
Without a macro

Select the column A range
press F5
Click on special
Select blanks
Click OK
type   =
from the keyboard move the cursor one cell up
press ctrl-enter
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39960732
With a macro

Sub fillwithuppercells()
ActiveSheet.UsedRange.Columns(1).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=r[-1]c"
End Sub
0
 
LVL 7

Author Closing Comment

by:macentrap
ID: 40003623
Thank you, Apologies for late reply
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now