Solved

macro to reformat data in excel

Posted on 2014-03-27
6
422 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
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…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

747 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

12 Experts available now in Live!

Get 1:1 Help Now