Solved

macro to reformat data in excel

Posted on 2014-03-27
6
426 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

867 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

21 Experts available now in Live!

Get 1:1 Help Now