Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

macro to reformat data in excel

Posted on 2014-03-27
6
Medium Priority
?
454 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 1000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

569 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