Modifying Raw Data

[ksiemens]

Hi Everyone, I'm working with a an excel report that is literally thousands of rows long (exported from accounting software). In order to effectively use the information, I need to sort the data. However, a very crucial piece of information (the unit number) will be lost if the report is sorted. Therefore, I need to modify the report prior to sorting it, and this is where the challenge begins.... Each row of the exported report contains sales data, such as invoice number, type of revenue, province, and amount (as shown below). This sales data always relates to a specific unit, which is shown as a group header above the respective sales data rows. In my simplified example (below), row 3-6 relate to sales information for unit1, while rows 9-12 relate to sales information for unit2 etc. As you've probably already noticed, using the sort function will disconnect the unit numbers from the respective sales data. Therefore, I need to add the proper unit number to each related row (e.g. by adding the respective unit number into the next column, column E). However, due to the large amount of sales data as well as the extremely long list of unit numbers, performing this task manually is basically impossible. Does anybody have any ideas on how I could automatically add the correct unit numbers into the respective cells under column E? Any ideas would be very much appreciated. Thanks, Katharina A B C D E 1 2 Unit 1 3 Invoice 200 Parts Revenue AB 100.00 4 Invoice 202 Parts Revenue AB 150.00 5 Invoice 303 Service Revenue BC 300.00 6 Invoice 207 Warranty Revenue BC 100.00 7 8 Unit 2 9 Inocie 55 Service Revenue ON 500.00 10 Invoice 302 Service Revenue ON 600.00 11 Invoice 210 Warranty Revenue BC 460.00 12 Invoice 220 Warranty Revenue BC 300.00 13 14 Unit 3 15 Invoice 404 Parts Revenue NS 300.00 16 Invoice 60 Parts Revenue ON 800.00 17 Invoice 88 Warranty Revenue ON 400.00 <colgroup><col><col><col><col><col><col></colgroup><tbody> </tbody> <colgroup><col><col><col><col><col><col></colgroup><tbody> </tbody>

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[apo]

Does the whole follow that same format..

1. Is there always a blank row between Units?
2. Is the group header always in the same syntax... "Group x"..


If so.. I will give it a go..

 

[ksiemens]

Hi Apo,

Thanks for your reply. To answer your questions:

1. The data is quite messy and inconsistent, but it appears that there are always 4 blank rows between units (unless the sales volume was 0 for a unit, in which case the blank rows are increased to 5 or 6).
2. The group header is always the same syntax (with a changing unit number)

This is by far one of the worst data tables that I've ever worked with. If you have any ideas, I'd love to her them.

Cheers,
Katharina

 

[apo]

Hi..
This should do what you want... I added a CommandButton on the Sheet and ran the code from it..
I added heaps of comments within the code so you can see what is happening..

Private Sub CommandButton1_Click()
Dim LastRow As Long
Dim i As Long
Dim LastCell As Long


'Find the Last Row in Column A
LastRow = Sheets("Sheet1").Range("A65536").End(xlUp).Row


'Loop through the dataset
For i = 2 To LastRow


'Detect if cell in Column A is a Group Header
If InStr(1, Cells(i, 1).Value, "Unit") Then


'If so..  find the last cell in that Unit
LastCell = Sheets("Sheet1").Cells(i, 1).End(xlDown).Row


'Insert Group Header text into Column E for each row
Range(Cells(i, 1).Offset(1, 4), Cells(LastCell, 1).Offset(0, 4)) = Cells(i, 1).Value


'Cells(i, 1).ClearContents
End If
Next i
End Sub

If you want to remove the Group Header (Unit x) as it goes.. then Uncomment the following line..

'Cells(i, 1).ClearContents

Here's what it looks like after running the code.. don't worry about the asterix that are showing.. they just indicate a empty cell in Excel Jeanie .
(I tested it on 1000 rows.. took less than second to run)
Sheet1

*	A	B	C	D	E
2	Unit 1	*	*	*	*
3	Invoice 200	Parts Revenue	AB	$100.00	Unit 1
4	Invoice 201	Parts Revenue	AB	$150.00	Unit 1
5	Invoice 202	Service Revenue	BC	$200.00	Unit 1
6	Invoice 203	Warranty Revenue	BC	$250.00	Unit 1
7	*	*	*	*	*
8	Unit 2	*	*	*	*
9	Invoice 204	Parts Revenue	ON	$400.00	Unit 2
10	Invoice 205	Parts Revenue	ON	$450.00	Unit 2
11	Invoice 206	Service Revenue	BC	$500.00	Unit 2
12	Invoice 207	Warranty Revenue	BC	$550.00	Unit 2
13	Invoice 999	Warranty Revenue	BC	$551.00	Unit 2
14	*	*	*	*	*
15	Unit 3	*	*	*	*
16	Invoice 208	Parts Revenue	ON	$700.00	Unit 3
17	Invoice 209	Parts Revenue	ON	$750.00	Unit 3
18	Invoice 210	Service Revenue	NS	$800.00	Unit 3
19	Invoice 211	Warranty Revenue	BC	$850.00	Unit 3

<tbody>
</tbody>

Excel tables to the web - Excel Jeanie Html 4