macro to reformat data in excel

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
LVL 7
macentrapAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
COACHMAN99Connect With a Mentor Commented:
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
 
COACHMAN99Commented:
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
 
FlysterCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Saqib Husain, SyedEngineerCommented:
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
 
Saqib Husain, SyedEngineerCommented:
With a macro

Sub fillwithuppercells()
ActiveSheet.UsedRange.Columns(1).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=r[-1]c"
End Sub
0
 
macentrapAuthor Commented:
Thank you, Apologies for late reply
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.