Data manipulation

Billy12excel · Feb 26, 2011

Received data file per first sheet1, looking to manipulate
results to = second sheet 1

Excel Workbook

A

B

C

D

1

LEVEL of Attainment

National Office Region

Name Upper and Lower

Ranking Position & Dollar Amount #1

2

Quadruple:

3

Top Level

North District, IL

Albert Einstein

#2 - Percent of Increase 6.0%

4

Top Level

North District, IL

Albert Einstein

#2 - Dollar Increase $2,745

5

Top Level

North District, IL

Albert Einstein

#2 New Members 10

6

Top Level

North District, IL

Albert Einstein

#1 Member Renewals 16

7

etc,etc,

8

9

Triple:

10

Mid Level

South Valley District, IL

Tony Tgier

#1 NM 105.56%

11

Mid Level

South Valley District, IL

Tony Tgier

#1 - Percent of Increase 15.6%

12

Mid Level

South Valley District, IL

Tony Tgier

#1 - Dollar Increase $9,737

13

etc,etc,

14

15

Double:

16

Lower Level

Central District, IL

Carter Mann

#2 New Members 6

17

Lower Level

Central District, IL

Carter Mann

#6 - APD $1,150

18

etc,etc,

19

20

Single:

21

Bottom Level

Central Distric, IL

Alexander Jones

#9 - APD $209

Sheet1

Excel Workbook

A

B

C

D

E

F

G

1

LEVEL of Attainment

National Office Region

Name Upper and Lower

Ranking Position & Dollar Amount #1

Ranking Position & Dollar Amount #2

Ranking Position & Dollar Amount #3

Ranking Position & Dollar Amount #4

2

Quadruple:

3

Top Level

North District, IL

Albert Einstein

#2 - Percent of Increase 6.0%

#2 - Dollar Increase $2,745

#2 New Members 10

#1 Member Renewals 16

4

etc,etc,

5

6

Triple:

7

Mid Level

South Valley District, IL

Tony Tgier

#1 NM 105.56%

#1 - Percent of Increase 15.6%

#1 - Dollar Increase $9,737

8

etc,etc,

9

10

Double:

11

Lower Level

Central District, IL

Carter Mann

#2 New Members 6

#6 - APD $1,150

12

etc,etc,

13

14

Single

15

Bottom Level

Central Distric, IL

Alexander Jones

#9 - APD $209

16

etc,etc,

17

18

Ideally all levels saved to separate csv files

Excel 2000 Sheet1

Excel 2000

Ideally all levels saved to separate csv files

Thank you for any help!

Billy12excel · Mar 1, 2011

Double worked, However last row, the Person's name was not there, see below, however the missing person's Ranking Position & Dollar Amount #1, & #2 was in row above but under Column Ranking Position & Dollar Amount #3, & #4 can Ranking Position & Dollar Amount #2, #3, and #4; be removed when not needed as well as that last column of client info not needed. CU445 and WA432 (last Column) from client's original list, might that be the source of the problem? Thank you kindly for your patience with the newer guy

Excel Workbook

A

B

C

D

E

F

G

H

1

LEVEL of Attainment

National Office Region

Name Upper and Lower

Ranking Position & Dollar Amount #1

Ranking Position & Dollar Amount #2

Ranking Position & Dollar Amount #3

Ranking Position & Dollar Amount #4

2

Beginner

North District, IL

Albert Einstein

#2 - Percent of Increase 6.0%

#2 - Dollar Increase $2,745

CU445

3

2nd intermediate

Central District

Rom Issac

#3 - Percent of Increase 5.4%

#3 - Dollar Increase $1,846

#4 - Percent of Increase 5.1%

#5 - Dollar Increase $2,247

WA432

...

Excel 2000

Domenic · Mar 1, 2011

Billy12excel said:
Double worked, However last row, the Person's name was not there, see below, however the missing person's Ranking Position & Dollar Amount #1, & #2 was in row above but under Column Ranking Position & Dollar Amount #3, & #4 can Ranking Position & Dollar Amount #2, #3, and #4; be removed when not needed as well as that last column of client info not needed. CU445 and WA432 (last Column) from client's original list, might that be the source of the problem? Thank you kindly for your patience with the newer guy
Excel Workbook
A B C D E F G H
1 LEVEL of Attainment National Office Region Name Upper and Lower Ranking Position & Dollar Amount #1 Ranking Position & Dollar Amount #2 Ranking Position & Dollar Amount #3 Ranking Position & Dollar Amount #4
2 Beginner North District, IL Albert Einstein #2 - Percent of Increase 6.0% #2 - Dollar Increase $2,745 CU445
3 2nd intermediate Central District Rom Issac #3 - Percent of Increase 5.4% #3 - Dollar Increase $1,846 #4 - Percent of Increase 5.1% #5 - Dollar Increase $2,247 WA432
...
Excel 2000

Unclear... Can you post the sample data responsible for the above results?

Billy12excel · Mar 2, 2011

Domenic, I've reworked and have dbl. checked 1st data sheet, it is the way it was received. (Have Copied only first 30 rows to comply with posting rule) Second sheet would be the hoped for results in xls, then to csv. I Hope this clears everything up. I may have transposed a few things in last few previous posts, I'm sorry, I should have created a complete dummy sheet from the get go.

Excel Workbook

A

B

C

D

F

1

Quadruple:

2

Beginner

North District, IL

Person 1

#2 - Percent of Increase 6.0%

AB561

3

Beginner

North District, IL

Person 1

#2 - Dollar Increase $2,745

AB561

4

Beginner

North District, IL

Person 1

#2 New Members 16

AB561

5

Beginner

North District, IL

Person 1

#1 Member Renewals 33

AB561

6

Triple:

7

Beginner

Central District, IL

Person 2

#1 ASES 105.56%

AB103

8

Beginner

Central District, IL

Person 2

#1 - Percent of Increase 11.0%

AB103

9

Beginner

Central District, IL

Person 2

#1 - Dollar Increase $2,737

AB103

10

2nd intermediate

West, IL

Person 3

#1 New Members 10

CW677

11

2nd intermediate

West, IL

Person 3

#1 Member Renewals 4

CW678

12

2nd intermediate

West, IL

Person 3

#3 XYZ $1,572

CW678

13

LowerTop

So Central, IL

Person 4

#3 - 1,501

MS234

14

LowerTop

So Central, IL

Person 4

#2 - ASES 202.58%

MS234

15

LowerTop

So Central, IL

Person 4

#1 XYZ $3,604

MS234

16

2nd intermediate

So Central, IL

Person 5

#1 - VPA $1,502

ZW102

17

2nd intermediate

So Central, IL

Person 5

#2 Member Renewals 30

ZW102

18

2nd intermediate

So Central, IL

Person 5

#1 XYZ $2,468

ZW102

19

LowerTop

West Valley, IL

Person 6

#1 - VPA $4,250

QJ286

20

LowerTop

West Valley, IL

Person 6

#2 Member Renewals 17

QJ286

21

LowerTop

West Valley, IL

Person 6

#3 XYZ $3,494

QJ286

22

LowerTop

North District, IL

Person 7

#2 - VPA $2,239

RE345

23

LowerTop

North District, IL

Person 7

#1 - ASES 317.38%

RE345

24

LowerTop

North District, IL

Person 7

#4 - XYZ $1,607

RE345

25

2nd intermediate

West Valley, IL

Person 8

#2 - VPA $1,982

XZ123

26

2nd intermediate

West Valley, IL

Person 8

#2 ASES 124.24%

XZ123

27

2nd intermediate

West Valley, IL

Person 8

#4 - XYZ $1,589

XZ123

28

Beginner

North District, IL

Person 9

#1 New Members 15

BT293

29

Beginner

North District, IL

Person 9

#2 Member Renewals 10

BT293

30

Beginner

North District, IL

Person 9

#3 XYZ $1,930

BT293

Sheet1

Excel Workbook

A

B

C

D

E

F

G

1

Name Upper and Lower

National Office Region

LEVEL of Attainment

Ranking Position & Dollar Amount #1

Ranking Position & Dollar Amount #2

Ranking Position & Dollar Amount #3

Ranking Position & Dollar Amount #4

2

Quadruple:

3

Person 1

North District, IL

Beginner

#2 - Percent of Increase 6.0%

#2 - Dollar Increase $2,745

#2 New Members 16

#1 Member Renewals 33

4

Triple:

Export to Triple.csv

5

Person 2

Central District, IL

Beginner

#1 ASES 105.56%

#1 - Percent of Increase 11.0%

#1 - Dollar Increase $2,737

6

Person 3

West, IL

2nd intermediate

#1 New Members 10

#1 Member Renewals 4

#3 XYZ $1,572

7

Person 4

So Central, IL

LowerTop

#3 - 1,501

#2 - ASES 202.58%

#1 XYZ $3,604

8

Person 5

So Central, IL

2nd intermediate

#1 - VPA $1,502

#2 Member Renewals 30

#1 XYZ $2,468

9

Person 6

West Valley, IL

LowerTop

#1 - VPA $4,250

#2 Member Renewals 17

#3 XYZ $3,494

10

Person 7

North District, IL

LowerTop

#2 - VPA $2,239

#1 - ASES 317.38%

#4 - XYZ $1,607

11

Person 8

West Valley, IL

2nd intermediate

#2 - VPA $1,982

#2 ASES 124.24%

#4 - XYZ $1,589

12

Person 9

North District, IL

Beginner

#1 New Members 15

#2 Member Renewals 10

#3 XYZ $1,930

13

Double

Export to Doiuble.csv

14

Person 10

Central District

Associate Level

#2 Member Renewals 36

#6 - XYZ $1,004

15

Person 12

North District, IL

2nd intermediate

#3 New Members 21

#7 - XYZ $1,377

16

Person 13

North District, IL

2nd intermediate

#3 - VPA $1,736

#2 XYZ $2,196

17

Person 14

West, IL

2nd intermediate

#3 ASES 123.26%

#2 New Members 12

Excel 2000 Results Expected Sheet1

Excel 2000 Thank you, Thank you, for your patience with the newbie.

Domenic · Mar 2, 2011

Try the following instead...

Option Explicit Sub CreateCSVFiles() Dim Categories As Variant Dim Category As Variant Dim strPath As String Dim SourceRng As Range Dim FoundCell As Range Dim Cell As Range Dim LastRow As Long Dim LastColumn As Long Dim i As Long Dim Rw As Long Application.ScreenUpdating = False Range(Cells(1, "E"), Cells(1, Columns.Count)).EntireColumn.ClearContents Columns("D").Insert LastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = 2 To LastRow If Cells(i, "B").Value <> "" Then Cells(i, "D").Value = Cells(i, "A").Value & "#" & Cells(i, "B").Value End If Next i For i = LastRow To 3 Step -1 If Cells(i, "D").Value <> "" Then If Cells(i, "D").Value = Cells(i - 1, "D").Value Then Set SourceRng = Range(Cells(i, "E"), Cells(i, Columns.Count).End(xlToLeft)) Cells(i - 1, "F").Resize(, SourceRng.Columns.Count).Value = SourceRng.Value Rows(i).Delete End If End If Next i Columns("D").Delete 'Change the path to the folder in which you want to save the .csv files strPath = "C:\Users\Domenic\Desktop\Test\" If Right(strPath, 1) <> "\" Then strPath = strPath & "\" Categories = Array("Quadruple", "Triple", "Double", "Single") For Each Category In Categories With Columns("A") Set FoundCell = .Find(Category, LookIn:=xlValues, lookat:=xlPart, MatchCase:=False) If Not FoundCell Is Nothing Then Open strPath & Category & ".csv" For Output As #1 Write #1, "Name Upper and Lower"; Write #1, "National Office Region"; Write #1, "LEVEL of Attainment"; Select Case Category Case "Quadruple" Write #1, "Ranking Position & Dollar Amount #1"; Write #1, "Ranking Position & Dollar Amount #2"; Write #1, "Ranking Position & Dollar Amount #3"; Write #1, "Ranking Position & Dollar Amount #4" Case "Triple" Write #1, "Ranking Position & Dollar Amount #1"; Write #1, "Ranking Position & Dollar Amount #2"; Write #1, "Ranking Position & Dollar Amount #3" Case "Double" Write #1, "Ranking Position & Dollar Amount #1"; Write #1, "Ranking Position & Dollar Amount #2" Case "Single" Write #1, "Ranking Position & Dollar Amount #1" End Select Set Cell = FoundCell.Offset(1, 1) Do While Cell <> "" Rw = Cell.Row Write #1, Cells(Rw, "C").Value; Write #1, Cells(Rw, "B").Value; Write #1, Cells(Rw, "A").Value; Select Case Category Case "Quadruple" Write #1, Cells(Rw, "D").Value; Write #1, Cells(Rw, "E").Value; Write #1, Cells(Rw, "F").Value; Write #1, Cells(Rw, "G").Value Case "Triple" Write #1, Cells(Rw, "D").Value; Write #1, Cells(Rw, "E").Value; Write #1, Cells(Rw, "F").Value Case "Double" Write #1, Cells(Rw, "D").Value; Write #1, Cells(Rw, "E").Value Case "Single" Write #1, Cells(Rw, "D").Value End Select Set Cell = Cell.Offset(1, 0) Loop Close #1 End If End With Next Category Columns.AutoFit Application.ScreenUpdating = True MsgBox "Completed...", vbInformation End Sub

Billy12excel · Mar 5, 2011

I wish I knew what I've messed up, I just can't get it to work with the actual client data. I'm Lost!! I've keeping going over and over it. Is it read only then coped to new book? no idea Thank you.

Billy12excel · Mar 5, 2011

I've condensed The "Data as Received" sheet from client, with all it's formatting. In case, this is where I'm inserting or changing something that is effecting the macro and how it serves up the final output. I'm showing the "Triple" CSV as it would be after the macro has run. There are Multiple rows of data for Quadruple, Triple, Double, and Single. All exported to separate .csv files.

Excel Workbook

A

B

C

D

E

G

1

4th Quarter 2010

2

Quarter

Name

State

Level

Ranking

3

Quadruple Award:

4

4th Quarter 2010

Person 1

North District, IL

Beginner

#2 - Percent of Increase 6.0%

5

4th Quarter 2010

Person 1

North District, IL

Beginner

#2 - Dollar Increase $2,745

6

4th Quarter 2010

Person 1

North District, IL

Beginner

#2 New Members 16

7

4th Quarter 2010

Person 1

North District, IL

Beginner

#1 Member Renewals 33

8

9

10

Triple Awards:

11

4th Quarter 2010

Person 2

West, IL

2nd intermediate

#1 ASES 105.56%

12

4th Quarter 2010

Person 2

West, IL

2nd intermediate

#1 - Percent of Increase 11.0%

13

4th Quarter 2010

Person 2

West, IL

2nd intermediate

#1 - Dollar Increase $2,737

14

4th Quarter 2010

Person 3

So Central, IL

LowerTop

#3 - 1,501

15

4th Quarter 2010

Person 3

So Central, IL

LowerTop

#2 - ASES 202.58%

16

4th Quarter 2010

Person 3

So Central, IL

LowerTop

#1 XYZ $3,604

Data as Received

Excel Workbook

A

B

C

D

E

F

1

Name Upper and Lower

National Office Region

LEVEL of Attainment

Ranking Position & Dollar Amount #1

Ranking Position & Dollar Amount #2

Ranking Position & Dollar Amount #3

2

Person 1

West, IL

2nd intermediate

#1 ASES 105.56%

#1 - Percent of Increase 11.0%

#1 - Dollar Increase $2,737

3

Person 2

So Central, IL

2nd intermediate

#3 - 1,501

#2 - ASES 202.58%

#1 XYZ $3,604

Excel 2000 Triple

Excel 2000 Thank you! Thank you,

Domenic · Mar 5, 2011

That's because the format of this last set of data differs from that of the previous one.

Billy12excel · Mar 6, 2011

Yes, it is different. I think, my deleting Row1 and Col A data, and possible other data changes, I made, to protect client info, are at the route of the problem. Not understanding all of the correct terms and nuances has also been to my disadvantage. On the up side it is forcing me look up the meaning of what I believe are the key terms in this macro and hopefully better understanding said terms. That last post of mine is exactly the way the client provided it, nothing deleted, nothing moved, only in-cell data changed for client privacy. Thank you! Thank you for your patience of a Saint.

Domenic · Mar 6, 2011

Try...

Code:

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] CreateCSVFiles()

    [color=darkblue]Dim[/color] Categories [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] Category [color=darkblue]As[/color] [color=darkblue]Variant[/color]
    [color=darkblue]Dim[/color] strPath [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] SourceRng [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] FoundCell [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] Cell [color=darkblue]As[/color] Range
    [color=darkblue]Dim[/color] LastRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] LastColumn [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Long[/color]
    [color=darkblue]Dim[/color] Rw [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    Application.ScreenUpdating = [color=darkblue]False[/color]
    
    Range(Cells(1, "F"), Cells(1, Columns.Count)).EntireColumn.ClearContents
    
    Columns("E").Insert

    LastRow = Cells(Rows.Count, "A").End(xlUp).Row
    
    [color=darkblue]For[/color] i = 4 [color=darkblue]To[/color] LastRow
        [color=darkblue]If[/color] Cells(i, "C").Value <> "" [color=darkblue]Then[/color]
            Cells(i, "E").Value = Cells(i, "A").Value & "#" & Cells(i, "B").Value & "#" & Cells(i, "C").Value & "#" & Cells(i, "D").Value
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] i
    
    [color=darkblue]For[/color] i = LastRow [color=darkblue]To[/color] 4 [color=darkblue]Step[/color] -1
        [color=darkblue]If[/color] Cells(i, "E").Value <> "" [color=darkblue]Then[/color]
            [color=darkblue]If[/color] Cells(i, "E").Value = Cells(i - 1, "E").Value [color=darkblue]Then[/color]
                [color=darkblue]Set[/color] SourceRng = Range(Cells(i, "F"), Cells(i, Columns.Count).End(xlToLeft))
                Cells(i - 1, "G").Resize(, SourceRng.Columns.Count).Value = SourceRng.Value
                Rows(i).Delete
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] i
    
    Columns("E").Delete
    
    [color=green]'Change the path accordingly[/color]
    strPath = "C:\Users\Domenic\Desktop\Test\"
    
    [color=darkblue]If[/color] Right(strPath, 1) <> "\" [color=darkblue]Then[/color] strPath = strPath & "\"

    Categories = Array("Quadruple", "Triple", "Double", "Single")
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] Category [color=darkblue]In[/color] Categories
        [color=darkblue]With[/color] Columns("A")
            [color=darkblue]Set[/color] FoundCell = .Find(Category, LookIn:=xlValues, lookat:=xlPart, MatchCase:=False)
            [color=darkblue]If[/color] [color=darkblue]Not[/color] FoundCell [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
                [color=darkblue]Open[/color] strPath & Category & ".csv" [color=darkblue]For[/color] [color=darkblue]Output[/color] [color=darkblue]As[/color] #1
                    [color=darkblue]Write[/color] #1, "Name Upper and Lower";
                    [color=darkblue]Write[/color] #1, "National Office Region";
                    [color=darkblue]Write[/color] #1, "LEVEL of Attainment";
                    [color=darkblue]Select[/color] [color=darkblue]Case[/color] Category
                        [color=darkblue]Case[/color] "Quadruple"
                            [color=darkblue]Write[/color] #1, "Ranking Position & Dollar Amount #1";
                            [color=darkblue]Write[/color] #1, "Ranking Position & Dollar Amount #2";
                            [color=darkblue]Write[/color] #1, "Ranking Position & Dollar Amount #3";
                            [color=darkblue]Write[/color] #1, "Ranking Position & Dollar Amount #4"
                        [color=darkblue]Case[/color] "Triple"
                            [color=darkblue]Write[/color] #1, "Ranking Position & Dollar Amount #1";
                            [color=darkblue]Write[/color] #1, "Ranking Position & Dollar Amount #2";
                            [color=darkblue]Write[/color] #1, "Ranking Position & Dollar Amount #3"
                        [color=darkblue]Case[/color] "Double"
                            [color=darkblue]Write[/color] #1, "Ranking Position & Dollar Amount #1";
                            [color=darkblue]Write[/color] #1, "Ranking Position & Dollar Amount #2"
                        [color=darkblue]Case[/color] "Single"
                            [color=darkblue]Write[/color] #1, "Ranking Position & Dollar Amount #1"
                    [color=darkblue]End[/color] [color=darkblue]Select[/color]
                    [color=darkblue]Set[/color] Cell = FoundCell.Offset(1, 1)
                    [color=darkblue]Do[/color] [color=darkblue]While[/color] Cell <> ""
                        Rw = Cell.Row
                        [color=darkblue]Write[/color] #1, Cells(Rw, "B").Value;
                        [color=darkblue]Write[/color] #1, Cells(Rw, "C").Value;
                        [color=darkblue]Write[/color] #1, Cells(Rw, "D").Value;
                        [color=darkblue]Select[/color] [color=darkblue]Case[/color] Category
                            [color=darkblue]Case[/color] "Quadruple"
                                [color=darkblue]Write[/color] #1, Cells(Rw, "E").Value;
                                [color=darkblue]Write[/color] #1, Cells(Rw, "F").Value;
                                [color=darkblue]Write[/color] #1, Cells(Rw, "G").Value;
                                [color=darkblue]Write[/color] #1, Cells(Rw, "H").Value
                            [color=darkblue]Case[/color] "Triple"
                                [color=darkblue]Write[/color] #1, Cells(Rw, "E").Value;
                                [color=darkblue]Write[/color] #1, Cells(Rw, "F").Value;
                                [color=darkblue]Write[/color] #1, Cells(Rw, "G").Value
                            [color=darkblue]Case[/color] "Double"
                                [color=darkblue]Write[/color] #1, Cells(Rw, "E").Value;
                                [color=darkblue]Write[/color] #1, Cells(Rw, "F").Value
                            [color=darkblue]Case[/color] "Single"
                                [color=darkblue]Write[/color] #1, Cells(Rw, "E").Value
                        [color=darkblue]End[/color] [color=darkblue]Select[/color]
                        [color=darkblue]Set[/color] Cell = Cell.Offset(1, 0)
                    [color=darkblue]Loop[/color]
                [color=darkblue]Close[/color] #1
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]End[/color] [color=darkblue]With[/color]
    [color=darkblue]Next[/color] Category
    
    Columns.AutoFit
    
    Application.ScreenUpdating = [color=darkblue]True[/color]
    
    MsgBox "Completed...", vbInformation
                    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

Billy12excel · Mar 7, 2011

Microsoft Visual Basic window, with Red X and 400 error. I keep trying, Does a read only file ( it is ), then I save as new name effect it? Thank you,

Data manipulation

Billy12excel

New Member

Billy12excel

New Member

Excel Facts

Domenic

MrExcel MVP

Billy12excel

New Member

Domenic

MrExcel MVP

Billy12excel

New Member

Billy12excel

New Member

Domenic

MrExcel MVP

Billy12excel

New Member

Domenic

MrExcel MVP

Billy12excel

New Member

Forum statistics

Share this page

Data manipulation

New Member

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock