Working and sorting a CSV import

[jon_dj2000]

Hi guys,
in work we are having a competition between the sales team, to see who can sell the most amount catalogues. Our computers in work will load up various reports but none quite suited for what we want. What we can do is load up report that tells us what each individual has sold within five different time frames. Today, yesterday, we to date, period to date and finally year-to-date.
I can extract the information into a CSV file but I am unsure of how to sort it. The plan was that I can load up the catalogue sales report and save it to works desktop, created table that was linked - or sourced the data from the CSV. Leaving Excel to automatically format it, into a standard table where I can use functions such as the local to organise and sort the data as I wish.

I will attach a table of what the data looks like in Excel once imported from the CSV and am just looking for some guidance on how to sort this data automatically/dynamically using either macros or standard Excel functions.

Below I have used the HTML maker with Microsoft office 2010. Please see attached example of exactly how the CSV imports into Excel:

Excel Workbook
	A	B	C	D	E	F
1		Individual Sales for Catalogues			Qty
2		Yesterday
3		LEWIS			3
4		NICHOLAS			2
5				Total 5
6		Week to Date
7		DAVID			6
8		LEWIS			5
9		MIKE			3
10		NICHOLAS			3
11				Total 17
12
13		Period to Date
14		DAVID			6
15		LEWIS			5
16		MIKE			3
17		NICHOLAS			3
18				Total 17
19		Year to Date
20		ADAM STANDISH			6
21		ALAN MAGUIRE			2
22		DAVID			19
23		JAMES			8
24		JONATHAN			1
25		LEWIS			37
26		MAURICE			15
27		MIKE			27
28		NICHOLAS			36
29				Total 151
30
CAT Data

Excel 2010

Can anyone help me organise this data automatically.

All that I am trying to achieve is each time period going across the top of the table, with the staff members name going down the left-hand side column. I want this to populate automatically so that the spreadsheet looks at the CSV extract the information and then organises within a spreadsheet.

Hope somebody can help

Many many thanks in advance
Jon

 

[hiker95]

jon_dj2000,

Welcome to the MrExcel forum.


Can we have a screenshot of what the final report should look like?

You can upload your workbook to www.box.net and provide us with a link to your workbook.

 

[jon_dj2000]

Hi Hiker95,
Thanks for the fast responce !

I have uploaded an example of how I would like it to end up.

First tab is the way the report should look like.

Second tab is the CSV import.

The issue is that the report needs to be dynamic, because I will use this report daily and different people will sell on different days meaning the CSV import will just keep creating a list.

My problem is i cannot just reference a cell because the next time a load it up and someone else has sold a catalogue, the cell reference would have changed.

Sorry to be a little brief here. Just typing thing this up quickly before I go to work.

Regards

Jon


http://www.box.net/shared/klsmgjcn7s

 

[jon_dj2000]

Has anyone go any ideas on this ?

even if it is just a pointer on something that I can research.

Many thanks

Jon

 

[hiker95]

jon_dj2000,


Sample raw data:

Excel Workbook
	A	B	C	D
1	Individual Sales for CA32
2	Yesterday			Qty
3	Lewis			3
4	Nick			2
5			Total 5
6	Week to Date
7	Dave			6
8	Lewis			5
9	Mike			3
10	Nick			3
11			Total 17
12
13	Period to Date
14	Dave			6
15	Lewis			5
16	Mike			3
17	Nick			3
18			Total 17
19	Year to Date
20	Adam			6
21	Alan			2
22	Dave			19
23	James			8
24	Jon			1
25	Lewis			37
26	Maurice			15
27	Mike			27
28	Nick			36
29			Total 151
30
CAT Data

After the macro in a new worksheet Results (you can not see the titles in row 1 have AutoFilter set):

Excel Workbook
	A	B	C	D	E	F
1	Staff member	Today	Yesterday	WTD	PTD	YTD
2	Adam					6
3	Alan					2
4	Dave			6	6	19
5	James					8
6	Jon					1
7	Lewis		3	5	5	37
8	Maurice					15
9	Mike			3	3	27
10	Nick		2	3	3	36
11
Results

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Option Base 1
Sub CreateReport()
' hiker95, 03/05/2011
' http://www.mrexcel.com/forum/showthread.php?t=533711
Dim w1 As Worksheet, wR As Worksheet
Dim AArea As Range, SR As Long, ER As Long, NR As Long, LR As Long
Dim SM, a As Long, aa As Long, FR As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("CAT Data")
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
Set wR = Worksheets("Results")
wR.Range("A1") = "Staff member"
For Each AArea In w1.Range("A2", w1.Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  With AArea
    SR = .Row
    ER = SR + .Rows.Count - 1
    NR = wR.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
    wR.Range("A" & NR).Resize(ER - SR).Value = w1.Range("A" & SR + 1 & ":A" & ER).Value
  End With
Next AArea
wR.Columns(1).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=wR.Columns(2), Unique:=True
LR = wR.Cells(Rows.Count, 2).End(xlUp).Row
wR.Range("B2:B" & LR).Sort Key1:=wR.Range("B2"), Order1:=xlAscending, Header:=xlNo, _
  OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
wR.Columns(1).Delete
Range("B1:F1") = [{"Today","Yesterday","WTD","PTD","YTD"}]
LR = wR.Cells(Rows.Count, 1).End(xlUp).Row
SM = wR.Range("A2:A" & LR)
For Each AArea In w1.Range("A2", w1.Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  With AArea
    SR = .Row
    ER = SR + .Rows.Count - 1
    Select Case w1.Cells(SR, 1)
      Case "Yesterday"
        For a = SR + 1 To ER Step 1
          For aa = LBound(SM) To UBound(SM)
            If w1.Cells(a, 1) = SM(aa, 1) Then
              FR = 0
              On Error Resume Next
              FR = Application.Match(SM(aa, 1), wR.Columns(1), 0)
              On Error GoTo 0
              wR.Cells(FR, 3).Value = w1.Cells(a, 4).Value
              Exit For
            End If
          Next aa
        Next a
      Case "Week to Date"
        For a = SR + 1 To ER Step 1
          For aa = LBound(SM) To UBound(SM)
            If w1.Cells(a, 1) = SM(aa, 1) Then
              FR = 0
              On Error Resume Next
              FR = Application.Match(SM(aa, 1), wR.Columns(1), 0)
              On Error GoTo 0
              wR.Cells(FR, 4).Value = w1.Cells(a, 4).Value
              Exit For
            End If
          Next aa
        Next a
      Case "Period to Date"
        For a = SR + 1 To ER Step 1
          For aa = LBound(SM) To UBound(SM)
            If w1.Cells(a, 1) = SM(aa, 1) Then
              FR = 0
              On Error Resume Next
              FR = Application.Match(SM(aa, 1), wR.Columns(1), 0)
              On Error GoTo 0
              wR.Cells(FR, 5).Value = w1.Cells(a, 4).Value
              Exit For
            End If
          Next aa
        Next a
      Case "Year to Date"
        For a = SR + 1 To ER Step 1
          For aa = LBound(SM) To UBound(SM)
            If w1.Cells(a, 1) = SM(aa, 1) Then
              FR = 0
              On Error Resume Next
              FR = Application.Match(SM(aa, 1), wR.Columns(1), 0)
              On Error GoTo 0
              wR.Cells(FR, 6).Value = w1.Cells(a, 4).Value
              Exit For
            End If
          Next aa
        Next a
    End Select
  End With
Next AArea
Erase SM
With wR.UsedRange
  .AutoFilter
  .Columns.AutoFit
End With
wR.Activate
Application.ScreenUpdating = True
End Sub

Then run the CreateReport macro.

 

[hiker95]

jon_dj2000,

After you install the macro, make sure that you save the workbook (save as) a Macro Enabled workbook the the file extension of xlsm

 

[jon_dj2000]

Hi There Hiker95,
Just tried what you suggested. Worked exactly as as your screen shots depict.

The only issue is, I added a data entry emulating todays sales in the CSV data tab, which is what would happen in the real world test as we checked the results daily, (as this report keeps on going) and it skipped the "Today" sales figures and jumped to yesterday and worked its way down giving me the same result as if today was not there?

I haven't got a clue how you wrote that Macro.

Is there a way that you could deliver a similar result just using Excel Functions?

I totally appreciate the time and effort you have put into helping me !!! The only think is that I want to learn Excel, and the thing that you did with the Macro (which was very impressive) is a little bit beyond me at the moment.

If it was in Excel functions I could look at the Syntax formula and try and work out what you have done, but the Macro thiny has just blew me away. The only thing I have ever done with Macros is record a basic sorting fuction and that is it

When I get to work tomorrow I will export the latest catalogue sales as a CSV file again and send it to you like last time if you like ?

I have various projects on at the moment which all evolve around csv data and how to sort this information is crucial to my understanding of some more of excels advanced functions.

Many thanks again

kind regards

Jon

 

[hiker95]

jon_dj2000,

There was no area in the CAT Data worksheet that started with Today.

If you can send another linked workbook where the area Today is included in column A, I will update the macro code.