Solved

macro to Modify Excel Spreadsheet

Posted on 2016-09-22
4
46 Views
Last Modified: 2016-09-22
Hello,

I would like to modify a Spreadsheet that is preconfigured to export from the software in the below way:

Excel 1 non formattte

then I would like it to format in the following way:

Excel 2 Formatted

The person may have more than one Badge ID so it could be 2 or 3 or 4 like the Cray, Acie has, it just depends.

don't know if there is a way to produce this?  Maybe by a macro or VBA? something?  I have perhaps, thousands.
0
Comment
Question by:Ernest Grogg
4 Comments
 
LVL 46

Expert Comment

by:Martin Liss
ID: 41811604
Please post a sample workbook.
0
 
LVL 8

Expert Comment

by:Koen
ID: 41811637
try this:

Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("Sheetname").Select
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "Name"
    Range("B2").Select
    Do While ActiveCell.Value <> "" And ActiveCell.Offset(1, 0).Value <> ""
        Selection.Cut
        ActiveCell.Offset(0, -1).Range("A1").Select
        ActiveSheet.Paste
        ActiveCell.Offset(0, 1).Range("A1:F1").Select
        Selection.Delete Shift:=xlUp
        Do While ActiveCell.Offset(1, 0) <> ""
            ActiveCell.Offset(0, -1).Range("A1").Select
            Selection.Copy
            ActiveCell.Offset(1, 0).Range("A1").Select
            ActiveSheet.Paste
            ActiveCell.Offset(0, 1).Range("A1").Select
        Loop
        ActiveCell.Offset(1, 0).Rows("1:1").EntireRow.Select
        Selection.Delete Shift:=xlUp
        ActiveCell.Offset(0, 1).Range("A1").Select
    Loop
    
    End Sub

Open in new window

0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 41811639
This code will parse your data.  It creates a new sheet with a name column, names for each Badge ID, and no blank rows.
Sub Parse_Badges()
    Dim lngLastRow As Long
        
    'Test that the active sheet is not already processed - or not expected layout
    If Range("A1").Value <> "Badge ID" Then
        MsgBox "Data set does not appear to be correct; Halting process.", vbCritical + vbOKOnly, "Error"
        Exit Sub
    End If
    
    'Test if there is already an existing parsed sheet in the workbook
    For sh = 1 To ActiveWorkbook.Sheets.Count
        If Sheets(sh).Name = "Badge List" Then
            MsgBox "There is already a processed sheet.  Please delete it then re-run.", vbCritical + vbOKOnly, "Badge List Already Exists"
            Exit Sub
        End If
    Next sh
    'Make a copy of the current sheet and process
    ActiveSheet.Copy before:=Sheets(1)
    Sheets(1).Name = "Badge List"
    
    lngLastRow = Cells.SpecialCells(xlCellTypeLastCell).Row
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A1").Value = "Name"
    Range("A1").Font.Bold = True
    Range("A2").Select
    Do Until ActiveCell.Row = lngLastRow
        If InStr(1, ActiveCell.Offset(0, 1).Value, ",", vbTextCompare) > 0 Then
            strName = ActiveCell.Offset(0, 1).Value
            ActiveCell.EntireRow.Delete
            lngLastRow = lngLastRow - 1
            ActiveCell.Value = strName
            ActiveCell.Offset(1, 0).Select
        ElseIf ActiveCell.Offset(0, 1) = "" Then
            ActiveCell.EntireRow.Delete
            lngLastRow = lngLastRow - 1
        Else
            ActiveCell.Value = strName
            ActiveCell.Offset(1, 0).Select
        End If
    Loop
    Range("A1").Select
    Selection.AutoFilter
    Columns("A:G").EntireColumn.AutoFit
    Range("A2").Select
    MsgBox "Done."
End Sub

Open in new window


I've attached an example file.  To test it, run on the "Original" sheet; it will create a new one called "Badge List".
EE-CreateBadgeList.xlsm
0
 

Author Closing Comment

by:Ernest Grogg
ID: 41811761
Works good.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

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 tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

775 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