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!

Domenic · Feb 26, 2011

Unclear... How many .csv files do you expect, based on the sample source data (the first Sheet1)? What do you mean by "etc,etc," in the second Sheet1? Can you post the expected data for one of the .csv files?

Billy12excel · Feb 28, 2011

Hope this helps, this would be the final CSV result of the Quadruple Group, Triple would be similar, with one less Ranking Position & Dollar Amount#3 being the end. Double would one less ranking, Single = 1 ranking only. All Levels have several names with Double (20+) and single levels (50+) names and related data. Hence my hope for a way to massage this data. Thank You!

Excel Workbook

B

C

D

E

F

G

1

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

3

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

Central District

Rom Issac

#3 - Percent of Increase 5.4%

#3 - Dollar Increase $1,846

#2 New Members 10

#2 Member Renewals 15

5

West, IL

Jan Jones

#4 - Percent of Increase 5.1%

#5 - Dollar Increase $2,247

#3 New Members 8

#3 Member Renewals 11

Sheet1

Excel 2000

Domenic · Feb 28, 2011

On a copy of your workbook, and with the sheet containing the data being the active sheet, try the following macro...

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 Application.ScreenUpdating = False LastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = LastRow To 3 Step -1 If Cells(i, "B").Value <> "" Then If Cells(i, "B").Value = Cells(i - 1, "B").Value Then Set SourceRng = Range(Cells(i, "D"), Cells(i, Columns.Count).End(xlToLeft)) Cells(i - 1, "E").Resize(, SourceRng.Columns.Count).Value = SourceRng.Value Rows(i).Delete End If End If Next i '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, "National Office Region"; Write #1, "Name Upper and Lower"; 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" Set Cell = FoundCell.Offset(1, 0) Do While Cell <> "" LastColumn = Cells(Cell.Row, Columns.Count).End(xlToLeft).Column For i = 2 To LastColumn If i <> LastColumn Then Write #1, Cells(Cell.Row, i).Value; Else Write #1, Cells(Cell.Row, i).Value End If Next i Set Cell = Cell.Offset(1, 0) Loop Close #1 End If End With Next Category Application.ScreenUpdating = True MsgBox "Completed...", vbInformation End Sub

Billy12excel · Mar 1, 2011

Domenic,

That is Awesome! thank you!! That will save a lot of copy and paste time!!
Have a followup question. In my haste to remove private date I forgot to mention that under the categories ie: Quad, Triple, Double, and Single there is a subset to the Level column, that could be 6 possibilities, ie; Beginner, intermediate, 2nd intermediate, mid intermediate, Lower Top, High Top, is it possible to have the 6 subcategories popped into a new Separate column, while still maintaining referential integrity? Lastly the file sent always has one extra column that they (client) use, is it possible to have the macro auto delete that last column?

Thank You!

Domenic · Mar 1, 2011

Billy12excel said:
Domenic,

That is Awesome! thank you!! That will save a lot of copy and paste time!!

You're very welcome!

Have a followup question. In my haste to remove private date I forgot to mention that under the categories ie: Quad, Triple, Double, and Single there is a subset to the Level column, that could be 6 possibilities, ie; Beginner, intermediate, 2nd intermediate, mid intermediate, Lower Top, High Top, is it possible to have the 6 subcategories popped into a new Separate column, while still maintaining referential integrity? Lastly the file sent always has one extra column that they (client) use, is it possible to have the macro auto delete that last column?

Thank You!

For clarity, can you post a small sample of the data, along with the expected results?

Billy12excel · Mar 1, 2011

This is the revised sheet as received

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

Beginner

North District, IL

Albert Einstein

#2 - Percent of Increase 6.0%

4

Beginner

North District, IL

Albert Einstein

#2 - Dollar Increase $2,745

5

Beginner

North District, IL

Albert Einstein

#2 New Members 10

6

Beginner

North District, IL

Albert Einstein

#1 Member Renewals 16

7

2nd intermediate

South Valley District, IL

Tony Tgier

#1 NM 105.56%

8

2nd intermediate

South Valley District, IL

Tony Tgier

#1 - Percent of Increase 15.6%

9

2nd intermediate

South Valley District, IL

Tony Tgier

#1 - Dollar Increase $9,737

10

2nd intermediate

South Valley District, IL

Tony Tgier

#2 Member Renewals 14

11

LowerTop

Central District, IL

Carter Mann

#2 - Percent of Increase 4.9%

12

LowerTop

Central District, IL

Carter Mann

#6 - Dollar Increase $1,045

13

LowerTop

Central District, IL

Carter Mann

#1 New Members 2

14

LowerTop

Central District, IL

Carter Mann

#2 Member Renewals 5

15

Additional People and data continues Down

Sheet1

#VALUE!

Excel 2000 Thank you!

Domenic · Mar 1, 2011

Your results don't seem to jive with the source data. In any case, it looks like you probably want to replace...

Open strPath & Category & ".csv" For Output As #1 Write #1, "National Office Region"; Write #1, "Name Upper and Lower"; 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" Set Cell = FoundCell.Offset(1, 0) Do While Cell <> "" LastColumn = Cells(Cell.Row, Columns.Count).End(xlToLeft).Column For i = 2 To LastColumn If i <> LastColumn Then Write #1, Cells(Cell.Row, i).Value; Else Write #1, Cells(Cell.Row, i).Value End If Next i Set Cell = Cell.Offset(1, 0) Loop Close #1 End If

with

Open strPath & Category & ".csv" For Output As #1 Write #1, "LEVEL of Attainment"; Write #1, "National Office Region"; Write #1, "Name Upper and Lower"; 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" Set Cell = FoundCell.Offset(1, 0) Do While Cell <> "" LastColumn = Cells(Cell.Row, Columns.Count).End(xlToLeft).Column For i = 1 To LastColumn If i <> LastColumn Then Write #1, Cells(Cell.Row, i).Value; Else Write #1, Cells(Cell.Row, i).Value End If Next i Set Cell = Cell.Offset(1, 0) Loop Close #1 End If

Which column do you wish to delete? Is it the last column from the source file? In your example, you refer to the last column of the .csv file, so I'm a little confused.

Billy12excel · Mar 1, 2011

Yes, the last column from the .xls file, should be deleted. I'm sorry.

Domenic · Mar 1, 2011

Try adding the following line of code after "Application.ScreenUpdating = False"...

Code:

Columns("E").ClearContents

Data manipulation

Billy12excel

New Member

Excel Facts

Domenic

MrExcel MVP

Billy12excel

New Member

Domenic

MrExcel MVP

Billy12excel

New Member

Domenic

MrExcel MVP

Billy12excel

New Member

Domenic

MrExcel MVP

Billy12excel

New Member

Domenic

MrExcel MVP

Forum statistics

Share this page

Data manipulation

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

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